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:

1. Checks
2. 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.