This is an Archived topic. The solution is available in the Thinkstore inside the Software Factory.
Goal
Sending emails from within your application is a common feature and can be implemented in many ways. However the traceability of sending emails from your application is often difficult. Therefor it is very usefully to gain some insights into which emails have been sent to whom and whether the email has exactly been sent. This topic demonstrates a basic setup for gaining insight into your email traffic.
Solution
First of you will have to set up some tables. This example will also contain tables used by the Thinkwise Reporting Service. This topic will allow you to email attachments created by the Thinkwise Reporting Service. This topic will not explain how the configure sending emails with a template. If you would to know how to do that, look at this topic: Example email module.
Create the following tables (if you do not use the Thinkwise Reporting Service, ignore the reporting_service_request tables and columns):
Make sure that you use the following domain elements for the column email_service_status:
- New (1)
- Ready for processing (2)
- Sent (3)
- Error (4)
The column email status does not require any elements (this is an additional column which can be used in the future)
Now that the tables are in place, we can start with the code templates. When and how you will fill these tables is determined by your application and will not be discussed in this topic.
I do advice that you set up separated stored procedures for creating an attachment and an email request and call these procedures from within the business rule that required to send an email.
Next we need to set up the code templates for processing the email request.These code templates are setup to support execution for a single row or the entire data set.
1. Check attachment status
The first step in processing the email request is to check the attachment status. For doing so you can use the following template:
----------------------------------------------
-- email does not contain attachment which is not created
----------------------------------------------
if exists(
select
1
from email_service_request esr
where esr.email_service_request_id = ISNULL(@email_service_request_id, esr.email_service_request_id)
and esr.email_service_status = 1 -- status new
and not exists(
select
1
from email_service_request_attachment att
join reporting_service_request rsr
on rsr.reporting_service_request_id = att.reporting_service_request_id
where att.email_service_request_id = esr.email_service_request_id
and rsr.reporting_service_status <> 2 -- not succesfull
)
)
begin
----------------------------------------------
-- set email status ready for processing
----------------------------------------------
update esr
set esr.email_service_status = 2 -- ready for processing
from email_service_request esr
where esr.email_service_request_id = ISNULL(@email_service_request_id, esr.email_service_request_id)
and esr.email_service_status = 1 -- status new
and not exists(
select
1
from email_service_request_attachment att
join reporting_service_request rsr
on rsr.reporting_service_request_id = att.reporting_service_request_id
where att.email_service_request_id = esr.email_service_request_id
and rsr.reporting_service_status <> 2 -- not succesfull
)
;
end;2. Send email
Next up is sending the emails which are ready for processing.
This procedure I have separated in 2 sections:
- Checks
- Send email
Checks:
----------------------------------------------------------------------
-- check if field recipient is empty
----------------------------------------------------------------------
if exists(
select
1
from email_service_request esr
where esr.email_service_request_id = ISNULL(@email_service_request_id, esr.email_service_request_id)
and esr.email_service_status = 2 -- ready for processing
and esr.recipient is null -- no recipient
)
begin
----------------------------------------------------------------------
-- set status to error
----------------------------------------------------------------------
update esr
set esr.email_service_status = 4 -- error
from email_service_request esr
where esr.email_service_request_id = ISNULL(@email_service_request_id, esr.email_service_request_id)
and esr.email_service_status = 2 -- ready for processing
and esr.recipient is null -- no recipient
;
end;Send email:
This template is set up for using the MSDB service. Other email services are supported but require a different template. Nevertheless the concept used remains the same.
----------------------------------------------------------------------
-- send emails
----------------------------------------------------------------------
-------------------------------------
-- declare required parameters
-------------------------------------
declare
@email_id email_service_request_id
,@profile_name name
,@recipient email_address
,@copy_recipient email_address
,@blind_copy_recipient email_address
,@from_address email_address
,@reply_to email_address
,@subject subject
,@body email_body
,@attachment nvarchar(max)
,@email_item_id email_item_id
;
-------------------------------------
-- select emails which are ready for processing
-------------------------------------
declare email_loop cursor local static read_only forward_only for
select
esr.email_service_request_id -- email_service_request_id
,ep.name -- profile_name
,esr.recipient -- recipient
,esr.copy_recipient -- copy_recipient
,esr.blind_copy_recipient -- blind_copy_recipient
,epa.sent_email_address -- from_address
,ISNULL(epa.response_email_address, epa.sent_email_address) -- replay_to
,esr.email_subject -- subject
,esr.email_body -- body
,REPLACE(att.attachment, 'amp;', '') -- attachment
from email_service_request esr
join email_profile ep
on ep.email_profile_id = esr.email_profile_id
join email_profile_account epa
on epa.email_profile_id = esr.email_profile_id
and epa.email_account_id = esr.email_account_id
outer apply(
select
SUBSTRING(
( -- attachment
select
';'
+ att.document_file
from email_service_request_attachment att
where att.email_service_request_id = esr.email_service_request_id
for xml path('')
)
,2
,9999
) as attachment
) att
where ( esr.email_service_request_id = @email_service_request_id -- specific row
or ( @email_service_request_id is null
and esr.email_service_status = 2 -- ready for processing
)
)
;
-------------------------------------
-- open cursor
-------------------------------------
open email_loop;
fetch next from email_loop into
@email_id
,@profile_name
,@recipient
,@copy_recipient
,@blind_copy_recipient
,@from_address
,@reply_to
,@subject
,@body
,@attachment
;
----------------------------------------------------------------------
-- execute loop
----------------------------------------------------------------------
while @@FETCH_STATUS = 0
begin
----------------------------------------------------------------------
-- reset return value email item id
----------------------------------------------------------------------
set @email_item_id = null;
----------------------------------------------------------------------
-- send email using MSDB service
----------------------------------------------------------------------
exec msdb.dbo.sp_send_dbmail
@profile_name = @profile_name
,@recipients = @recipient
,@copy_recipients = @copy_recipient
,@blind_copy_recipients = @blind_copy_recipient
,@from_address = @from_address
,@reply_to = @reply_to
,@subject = @subject
,@body = @body
,@file_attachments = @attachment
,@body_format = 'HTML'
,@exclude_query_output = 1
,@mailitem_id = @email_item_id output
;
----------------------------------------------------------------------
-- update status
----------------------------------------------------------------------
update esr
set esr.email_service_status = case
when @email_item_id is not null -- has a return value
then 3 -- sent
else 4 -- error
end
,esr.email_item_id = @email_item_id -- return value
from email_service_request esr
where esr.email_service_request_id = @email_id
;
----------------------------------------------------------------------
-- get next record and close cursor
----------------------------------------------------------------------
fetch next from email_loop into
@email_id
,@profile_name
,@recipient
,@copy_recipient
,@blind_copy_recipient
,@from_address
,@reply_to
,@subject
,@body
,@attachment
;
end;
close email_loop;
deallocate email_loop;
As you can see the attachments are being select using XML. This is way we have to implement the REPLACE function upon selecting the full attachment string.
Notice, MSDB does not support certain symbols and characters. Be sure avoid these in the attachment name, otherwise the email will not be send.
These procedure can be put within 2 separated tasks. Both can be implemented within the GUI and can be used within Jobs to be executed on a specific schedule.
This is a basic concept which can be extended with additional features. To support these some of the features the field email_status has been implemented. This will allow you insert the status given to you the by the email service. For example if you use MSDB you can include the error message in this field.
