Example email module

  • 26 May 2020
  • 2 replies
  • 118 views

Userlevel 3
Badge +10

Goal

Most application constructed will include some sort of email module. In most cases those email modules share a lot of similarities. Therefor it is better to use a proven concept instead of designing one yourself. This topic will help you set up the basics regarding most email modules.

 

Solution

This example is based on setting up an email module while using the Databasemail option available within MS SQL Server. 

The first step is setting the email template configuration. Note that my example contains 2 views:

  1. email_profile
  2. email_profile_account

These views allow a user to select the email configuration available within the MS SQL Server database mail.

First create these tables and views:

The email profile view requires the follow SQL code:

----------------------------------------------------
-- select all email profils
----------------------------------------------------
select sp.profile_id
,sp.name
,sp.description
from msdb..sysmail_profile sp

The email profile account view requires the following SQL code:

----------------------------------------------------
-- select all email profile accounts
----------------------------------------------------
select spa.profile_id
,sa.account_id
,sa.name
,sa.description
,sa.email_address
,sa.replyto_address
,sa.display_name
from msdb..sysmail_profileaccount spa
join msdb..sysmail_account sa
on sa.account_id = spa.account_id

The email template table contains a column email_template. This column has been given a specific domain with elements. Each element represents a specific email the system can process. The element database value can be used in your SQL Code to program data transaction required for a specific email.

I would recommend using a procedure (Subroutine) for sending and email (based on these email templates).

In most case the SQL code for this procedure is divided in 3 sections:

  1. Select the settings based on the email_template
  2. Replace parameters within the email body
  3. Call the MS SQL Server database mail procedure, which will send the email.

The code used for step 1:

----------------------------------------------------------------
-- create the email template and then sent it to the recipient
----------------------------------------------------------------
declare @profile_name name
,@from_address email_address
,@reply_to email_address
,@subject name
,@body html_field
;
------------------------------------
-- select email template parameters
------------------------------------

select @profile_name = ep.name
,@from_address = epa.sent_email_address
,@reply_to = isnull(epa.response_email_address, epa.sent_email_address) -- when reponse doesn't exists use sent email address
,@subject = etl.subject
,@body = etl.body
,@recipient = isnull(@recipient, et.default_recipient)
,@copy_recipient = isnull(@copy_recipient, et.default_copy_recipient)
,@blind_copy_recipient = isnull(@blind_copy_recipient, et.default_blind_copy_recipient)
from email_template et
join email_profile ep
on ep.email_profile_id = et.email_profile_id
join email_profile_account epa
on epa.email_profile_id = et.email_profile_id
and epa.email_account_id = et.email_account_id
join email_template_translation etl
on etl.email_template_id = et.email_template_id
where et.email_template_id = @email_template_id
and etl.language_id = @language_id
and et.inactive = 0 -- active template
;

The code used for step 2:

---------------------------------------------------
-- Exmaple code : fill body and subject parameters
---------------------------------------------------
--If in your email_template_translation there is a subject or a body,
--you can add parameters with brackets, e.g. [customer]
--In this template you should replace all parameters by real values.

--Example code:

select @body = replace(@body, '[customer]', 'Mr. Johnson')

select @subject = replace(@subject, '[customer]', 'Mr. Johnson')

The code used for step 3:

-------------------------------------------------
-- check if al required parameters are not null
-------------------------------------------------
if @recipient is null
begin
exec dbo.tsf_send_message 'email_no_recipients', 1, 1
return
end

if @subject is null
begin
exec dbo.tsf_send_message 'email_no_subject', 1, 1
return
end

if @body is null
begin
exec dbo.tsf_send_message 'email_no_body', 1, 1
return
end

-----------------------------------------------
-- email can be sent, because body is not null
-----------------------------------------------
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
,@body_format = 'HTML'
,@exclude_query_output = 1

Next to setting up this email module there is also an option to use an email queue. This has the benefit of being able to log all emails sent by your application. In set of using step 3 in the email procedure, you will insert a record into your email queue table and set up an additional procedure for step 3 (which can be scheduled within a SQL Server agent Job).

This is an example of an email queue table:

As you can see, this example also linked the attachments of an email. And also has a link with the queue of my Thinkwise Reporting service queue (this is a Thinkwise feature used to automatically generated reports).

The column email_item_id is used to store the ID that MS SQL Server will assign to my call. In order to select this ID you have to add the line “@mailitem_id = @email_item_id OUTPUT” to the call of the send email procedure of MS SQL Server database mail:

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
;

 


2 replies

Badge +4

When running the first statement, I get the following message:

How do I get the above to work for me?

PS My SQL Server (12.0.2000.8 with compatibility lever 150 ~ SQL Server 2019) is running on Azure

Userlevel 3
Badge +10

Roy,

Database mail is not available on MS Azure. 

There are other options available for MS Azure, the following topic might give you some insights regarding Send Email on MS Azure.

 

Reply