It is a compilation of information found online, with some modifications made by me.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST(( SELECT
Col1 AS 'td','',
Col2 AS 'td','',
Col3 AS 'td','',
Col4 AS 'td','',
Col5 AS 'td','',
Col6 AS 'td','',
Col7 AS 'td','',
Col8 AS 'td','',
Col9 AS 'td','',
Col10 AS 'td','',
Col11 AS 'td','',
Col12 AS 'td','',
Col13 AS 'td','',
Col14 AS 'td','',
Col15 AS 'td','',
Col16 AS 'td'
From DBName.dbo.Table1
Where New_Status = (Select Col1 From DBName.dbo.Table2 Where DBName.dbo.Table2.PONbr = DBName.dbo.Table1 .PONbr)
ORDER BY [PONbr] Desc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Table
Name</H3>
<table border = 1>
<tr>
<th>
[Col1] </th>
<th>
[Col2] </th>
<th>
[Col3] </th>
<th>
[Col4] </th>
<th>
[Col5] </th>
<th>
[Col6]] </th>
<th>
[Col7] </th>
<th>
[Col8] </th>
<th>
[Col9] </th>
<th>
[Col10]] </th>
<th>
[Col11] </th>
<th>
[Col12] </th>
<th>
[Col13] </th>
<th>
[Col14] </th>
<th>
[Col15] </th>
<th>
[Col16] </th>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='Profile_Name',
@body = @body,
@body_format ='HTML',
@recipients='Recipient@Domain.com',
@subject='Email Subject';
No comments:
Post a Comment