Nicely Formatted HTML Email of SQL Table
Published: Oct 10, 2014
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'
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron