Nicely Formatted HTML Email of SQL Table
Published: 4th August 2013
Okay, in my last post I showed you how easy it is to include a table of results in an email, but by all accounts it was a little bit bland when produced. So in this post I’ll deviate a little from SQL Server to give you some HTML which will turn this:



into this:



Okay, if you have a good HTML knowledge then you’ll probably see what this code is doing, but if not then you might struggle. It’s also slightly different from some of the more common HTML as Outlook (which this is designed for) doesn’t yet understand every nuance of the language and therefore I’ve had to fudge accordingly.

If you’ve read my previous post then you’ll have seen a lot of this code before, so I’ll not cover it again (read here).

Where this differs is with the main email body which is generated in a much more complex manner. To be honest the easiest approach is simply to post the code and let you try it for yourself and tweak as you see fit. As before, make sure to change the Mail Server and recipient email address.

if object_id('myEmailTable') is not null drop table myEmailTable
go

create table myEmailTable
(
  
id tinyint identity(1, 1),
  
firstName varchar(15),
  
lastName varchar(20),
  
isAdult bit
)
go

insert into myEmailTable
values('Homer', 'Simpson', 1),
       (
'Marge', 'Simpson', 1),
       (
'Bart', 'Simpson', 0),
       (
'Lisa', 'Simpson', 0),
       (
'Maggie', 'Simpson', 0)
go

declare @emailSubject varchar(100),
      
@textTitle varchar(100),
      
@tableHTML nvarchar(max)

select @emailSubject = 'My Test Email',
      
@textTitle = 'All adults in #myEmailTable'

set @tableHTML = '<html><head><style>' +
  
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
  
'</style></head><body>' +
  
'<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +
  
@textTitle + '</div>' +
  
'<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=0 cellspacing=0 border=0>' +
  
'<tr bgcolor=#4b6c9e>' +
  
'<td align=center><font face="calibri" color=White><b>First Name</b></font></td>' +    -- Manually type headers
  
'<td align=center><font face="calibri" color=White><b>Last Name</b></font></td>' +     -- Manually type headers
  
'<td align=center><font face="calibri" color=White><b>Is Adult</b></font></td></tr>'   -- Manually type headers

/*
-----------------------------------------------------------------------------------------------------------------------
-- NOTE:  Headers could still be automated to use column names by using the following alternate @tableHTML above
-----------------------------------------------------------------------------------------------------------------------

declare @columnHeaders varchar(max)
select @columnHeaders = isnull(@columnHeaders + '<td align=center><font face="calibri" color=White><b>', '') + c.name +
       '</b></font></td>'
from sys.objects o
join sys.columns c
on o.object_id = c.object_id
where o.name = 'myEmailTable'
and c.name != 'id' -- Because I don't want this column in the output
order by c.column_id

select @columnHeaders = '<td align=center><font face="calibri" color=White><b>' + @columnHeaders

set @tableHTML = '<html><head><style>' +
   'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
   '</style></head><body>' +
   '<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +
   @textTitle + '</div>' +
   '<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=0 cellspacing=0 border=0>' +
   '<tr bgcolor=#4b6c9e>' + @columnHeaders + '</tr>'

*/

declare @body varchar(max)
select @body =
(
  
select ROW_NUMBER() over(order by id) % 2 as TRRow,
          
td = firstName,     -- Here we put the column names
          
td = lastName,      -- Here we put the column names
          
td = isAdult        -- Here we put the column names
  
from myEmailTable
  
where isAdult = 1           -- Normal WHERE clause
  
order by id
  
for XML raw('tr'), elements
)

set @body = REPLACE(@body, '<td>', '<td align=center><font face="calibri">')
set @body = REPLACE(@body, '</td>', '</font></td>')
set @body = REPLACE(@body, '_x0020_', space(1))
set @body = Replace(@body, '_x003D_', '=')
set @body = Replace(@body, '<tr><TRRow>0</TRRow>', '<tr bgcolor=#F8F8FD>')
set @body = Replace(@body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#EEEEF4>')
set @body = Replace(@body, '<TRRow>0</TRRow>', '')

set @tableHTML = @tableHTML + @body + '</table></div></body></html>'

set @tableHTML = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;">' + @tableHTML + '</div>'
          
exec msdb.dbo.sp_send_dbmail
  
@profile_name = 'My SQL Mail Server',
  
@recipients = 'theBoredDBA@theBoredDBA.com',
  
@body = @tableHTML,
  
@subject = @emailSubject,
  
@body_format = 'HTML'
Comments:
Myo Min Lin
13/11/2014 08:45:00
Thanks a lot for this post. it helps me.
But there is one thing i got problem is data size that will be in email Body is exceed over varchar(max). So doesn't send email. How can i do it?
Kevin (theBoredDBA)
13/11/2014 08:59:00
Hi,

By your comment I'm guessing that your body is larger than 2GB in size (as that's the limit of a MAX datatype)?

Sorry... I'm honestly not sure what you can do in that circumstance as I've never tested it to that extent.

It might be worth posting your question on the MSDN forums in this case and seeing if someone has come across this before.

Thanks,
Kevin
Gowri Kuchangi
11/09/2015 18:45:00
Thank you so much for this code.It really helped me.

Thanks Again:)
Sobhan Dutta
24/04/2016 17:42:00
Hi Myo,

First try to compress your table row or page wise and then try to send the email. If table size get reduce, your problem must be solved.


Thanks,
Sobhan
NB: Comments will only appear once they have been moderated.