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'