Send HTML Formatted Email Using Database Mail

When using database mail, it may be required to send email that is well formatted. Sometimes, we might want to send formatted data with proper header in the email. Below example describes as how we can send such email with table data in email body. For information on how to set up database mail refer to my earlier post here.

Say we have a scenario where we have audit information in two tables Product_Info and Order Info and we are sending the report for past 10 days. We first create our tables and populate some data in it.

--create your Product_info Table

create table #Product_Info(ID int identity(1,1) not null

,Product_old_val varchar(20)

,Product_new_val varchar(20)

,updatedate datetime

,updatedBy varchar(30))

 

--Populate data in Product_info table

insert #Product_Info values('old-2','old-3',(select dateadd(DAY,-10,getdate())),'Meera')

insert #Product_Info values('old-2','Name-3',(select dateadd(DAY,-10,getdate())),'Meera')

insert #Product_Info values('N/A-3','Name-3Dummy',(select dateadd(DAY,-10,getdate())),'Meera')

insert #Product_Info values('N/A-3','Name-3',(select dateadd(DAY,-10,getdate())),'John')

insert #Product_Info values('N/A-3-old','Name-3',(select dateadd(DAY,-5,getdate())),'John')

insert #Product_Info values('N/A-2','Name-2',(select dateadd(DAY,-5,getdate())),'John')

insert #Product_Info values('N/A-2-tobechanged','Name-2',(select dateadd(DAY,-10,getdate())),'John')

insert #Product_Info values('N/A-1','Name-1',(select dateadd(DAY,-10,getdate())),'John')

insert #Product_Info values('old-1','new',(select dateadd(DAY,-10,getdate())),'John')

insert #Product_Info values('aa','bb',(select dateadd(DAY,-1,getdate())),'John')

 

 

--Create your Order_info table

create table #Order_Info(ID int identity(1,1) not null

,Order_old_val varchar(20)

,Order_new_val varchar(20)

,updatedate datetime

,updatedBy varchar(30))

--Populate data in Order_info table

insert #Order_Info values('order-1','order-1',(select dateadd(DAY,-5,getdate())),'John')

insert #Order_Info values('order-1_old','order-1_new',(select dateadd(DAY,-10,getdate())),'John')

insert #Order_Info values('order-1_dummy','order-1_newdummy',(select dateadd(DAY,-10,getdate())),'Meera')

insert #Order_Info values('order-1_change','order-3_changed',(select dateadd(DAY,-10,getdate())),'Meera')

insert #Order_Info values('order-2','order-3',(select dateadd(DAY,-10,getdate())),'Meera')

insert #Order_Info values('order-2','old-3',(select dateadd(DAY,-10,getdate())),'Meera')

insert #Order_Info values('old-2','old-3',(select dateadd(DAY,-10,getdate())),'Meera')

 

--coding for formatting and sending the email in html format

 

DECLARE @yourHTMLEmail NVARCHAR(MAX) ;

 

SET @yourHTMLEmail =

N'<H1>Product/Order Detail Report</H1>' +

N'<style type="text/css">table {border-collapse:collapse;font-family:Arial;font-size:small;}'+

N' table td,th{border:solid 1px Gray;padding:10px;text-align:left;color:Gray;} '+

N' table th{background-color:Gray;color:White;font-weight:bold;}</style>'+

N'<table border="1">' +

N'<tr><th>ID</th>'+

N'<th>Old_Value</th><th>New_Value</th>'+

N'<th>UpdateDate</th><th>updatedBy</th></tr>' +

 

CAST( (select td=id,' ',

td=Product_old_val ,'',

td=Product_new_val ,'',

td=updatedate ,'',

td=updatedBy

from (

select id,Product_old_val,Product_new_val,updatedate,updatedBy

from #Product_Info where cast(updatedate as date) in(cast(dateadd(DAY,-10,getdate()) as date))

union all

select id,Order_old_val,Order_new_val,updatedate,updatedBy

from #Order_Info where cast(updatedate as date) in(cast(dateadd(DAY,-10,getdate()) as date))

) A FOR XML PATH('tr'), TYPE )

AS NVARCHAR(MAX)

) +

N'</table>' ;

 

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Profile1',

@recipients='yoursenderemail@email.com',

@subject = 'Report-From Past 10 days',

@body = @yourHTMLEmail,

@body_format = 'HTML'

 

 

 

Tags: , , , , ,

SQL Server | SQL Tricks

Comments (5) -

Kelly United States, on 6/13/2011 11:37:19 AM Said:

Kelly

Hi Nehaa Vishwakarma,

This was an excellent post.  It help me to get started in how to create a formated HTML email.

I need to create a procedure that will loop through a cursor and send an automated email response based off of the criteria in the where clause. The email needs to be a formatted template (has company logo, some static text, values stored in a table, and more static text, more values stored in a table, with a thick boarder around the entire email). Could you please give me an example in how to do this?  I appreciate any help you can provide.

Kellylee

Nehaa United States, on 6/14/2011 11:31:54 AM Said:

Nehaa

Hi Kelly,

I will soon provide an article that you requested. Keep checking...

Kelly United States, on 6/14/2011 11:38:46 AM Said:

Kelly

Thank you Nehaa.  This will be very helpful since this is a high priority request I need to complete.

Kellylee

Kelly United States, on 6/16/2011 9:55:00 AM Said:

Kelly

Hi Nehaa,

I am confused.  Your tutorial worked perfectly 2 days ago.  Now, the email generates a blank email instead of the report.  Any ideas?

Thanks,

Kellylee

Kelly United States, on 6/16/2011 11:07:47 AM Said:

Kelly

This is all set.  For some reason I had to stop and restart the sql server.

Kellylee

Pingbacks and trackbacks (1)+