DECLARE @ProfileName VARCHAR(64),
@EmailsRecipients VARCHAR(640),
@EmailSubject VARCHAR(640);
SET @ProfileName = 'Default Profile 01';
SET @EmailsRecipients = 'me@test.com';
SET @EmailSubject = 'Email Subject';
CREATE TABLE ##Results
(
Col01 VARCHAR(12),
Col02 VARCHAR(8)
);
INSERT INTO ##Results
SELECT
Col01, Col02
FROM tbTable01
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<html><body><h3>Title 01</h3>' +
N'<table border="1" cellspacing=0 cellpadding=5 width="55%">' +
N'<tr bgcolor="#3BB9FF">
<td nowrap><b>Col01</b></td>
<td nowrap><b>Col02 </b></td>
</tr>' +
CAST((
SELECT
'td/@bgcolor' = CASE WHEN Col01 <> 'Success' THEN 'yellow' ELSE '' END,
td = FileDate, '',
'td/@bgcolor' = CASE WHEN LEN(Col02) = 0 THEN 'yellow' ELSE '' END,
td = FileTime, ''
FROM ##Results
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table>
</body></html>';
EXEC msdb.dbo.sp_send_dbmail
@recipients= @EmailsRecipients,
@profile_name = @ProfileName,
@subject = @EmailSubject,
@body=@tableHTML,
@body_format = 'HTML';
DROP TABLE ##Results;
Comments