Hey Jeff, I like your simplified T-SQL only solution a lot, I just used it for a small project. Here are a couple small tips for others trying to use the code Jeff posted, you need to specify body_format='HTML' for the send mail call, and you also need to add blanks to the end of all data that could be blank, so that grid lines are rendered in all versions of outlook/internet explorer (it will work in some versions without them). The thing is, you can't use a regular " " space, and you can't use " ", you have to use the alternate space generated by holding down the alt key, typing 255, then releasing the alt key. It looks the same as a regular space but is treated differently by outlook/internet explorer. The column width issues can drive you crazy because people running different machines with different versions of windows/outlook/internet explorer could potentially all see different formatting of the HTML table. If there are good solutions to this issues, please post them. I used OP's SSIS solution a while ago in another project and I can't remember now if that solution suffered from the same formatting problem?? Maybe that is the value added in the (albeit very complicated) SSIS version.
At any rate, here is my working version of the t-sql only solution previously posted (obviously you would have to specify your own table name and query to test:
DECLARE @Body NVARCHAR(MAX),
@Email NVARCHAR(300),
@ccNVARCHAR(300)
-- @FirstName NVARCHAR(15) --If you want to use the recipients name in the email
;
--===== You could get the email address and name from a table or hard code it below
--can use multiple email addresses separated with a ";"
select @EMail = 'myEmail@domain.com'
select @cc = 'someotheremail@domain.com'
--SELECT @FirstName = 'x',
-- FROM #MyEmailTable
-- WHERE ID = @SomeParameter
;
--===== Create the body of the message including a formatted HTML table of query results data
SELECT @Body =
------- Create the table and the table header. (Easily readable HTML)
--You could use a greeting here: <p>Dear ' + @FirstName + ',</p>
--Note: Your table column headings are defined below, you can add spaces ( ) to make the columns wider, but this will only work on certain versions of internet explorer / outlook, some renderers will ignore the spaces.
'
<html>
<body>
<p>The following table shows blah blah blah....</p>
<table border="1" cellspacing="0" style="text-align:center">
<caption>Coverage Policy Exceptions</caption>
<tr style="background-color:AliceBlue"><th>Exception Order</th><th> Exception_Name </th><th>Plan_Name</th><th>Internal_Policy_Nm</th><th>Policy_Nm </th><th> Policy_Nbr </th><th> Status </th><th> Drug_Nm </th><th> Indication </th><th> SubIndication </th><th> Prev_Internal_Policy_Nm </th><th> Prev_Policy_Nm </th><th> Prev_Policy_Nbr </th><th> Prev_Status </th><th> Change_Date_Time </th><th> Changed_By_ID </th><th> Changed_By_Name </th>
</tr>
'
------- Create the rest of the table. Filled in from data in the table.
+ SPACE(8)
+ REPLACE(--This just indents each row to make rows in the HTML apparent and easy to read
CAST((SELECT td = Exception_Order, N'', --<td></td> = "data" element in a row
--You might want to format dates, or convert things to text as in the examples below:
--td = CONVERT(NCHAR(11), OpenToEnrollDate, 106), N'',
--td = CAST(XXX AS NVARCHAR(10)), N''
--You need to add something to the end of any column that could be blank if you want grid lines to appear in all versions of windows/internet explorer/outlook (some versions work fine, others remove grid lines around blank cells)
--Please note, this is NOT a regular space - hold down alt key, type 255 then release alt key, this creates something like a space. A regular space does NOT resolve the grid line problem for SOME versions of windows/internet explorer/outlook.
td = Exception_Name + ' ', N'',
td = [Plan_Name] + ' ', N'',
td = [Internal_Policy_Nm] + ' ', N'',
td = [Policy_Nm] + ' ', N'',
td = [Policy_Nbr] + ' ', N'',
td = [Status] + ' ', N'',
td = [Drug_Nm] + ' ', N'',
td = [Indication] + ' ', N'',
td = [SubIndication] + ' ', N'',
td = [Prev_Internal_Policy_Nm] + ' ', N'',
td = [Prev_Policy_Nm] + ' ', N'',
td = [Prev_Policy_Nbr] + ' ', N'',
td = [Prev_Status] + ' ', N'',
--A date with time can be formated as below
td = replace(convert(varchar(8), [Change_Dt], 10), '-', '/') + ' ' + substring(convert(varchar(20), [Change_Dt], 9), 13, 5) + ' ' + substring(convert(varchar(30), [Change_Dt], 9), 25, 2) + ' ', N'',
td = cast([Change_By] as varchar(6)) + ' ', N'',
td = [Change_By_Name] + ' ', N''
FROM SomeTableName --Add where clause and/or order by clause as needed
FOR XML PATH('tr'),TYPE --<tr></tr> = row encapsulation
)AS NVARCHAR(MAX))
,'</tr>','</tr>'+CHAR(10)+SPACE(8))
------- Finalize the HTML
+ '
</table>
</body>
</html>'
;
-- All set. Send the email. (google sp_send_dbmail if you need help setting it up)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SpecifyYourProfileNameHere',
@recipients = @EMail,
@copy_recipients = @cc,
@subject = 'Specify Email Subject here',
@body = @Body,
@body_format = 'HTML' --If you don't include this, you will see all HTML tags in the email and it will not render correctly
;
GO