Here how can you create HTML body part of your mail.
Step 1:
DECLARE @Body NVARCHAR(MAX),
@TableHead VARCHAR(1000),
@TableTail VARCHAR(1000)
SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
+ 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
+ '</style>' + '</head>' + '<body>' + 'Report generated on : '
+ CONVERT(VARCHAR(50), GETDATE(), 106)
+ ' <br> <table cellpadding=0 cellspacing=0 border=0>'
+ '<tr> <td bgcolor=#E6E6FA><b>PO Date</b></td>'
+ '<td bgcolor=#E6E6FA><b>PO ID</b></td>'
+ '<td bgcolor=#E6E6FA><b>SAP Ref</b></td>'
+ '<td bgcolor=#E6E6FA><b>GRN</b></td>'
+ '<td bgcolor=#E6E6FA><b>DealerID</b></td>'
+ '<td bgcolor=#E6E6FA><b>Dealer Name</b></td>'
+ '<td bgcolor=#E6E6FA><b>Status</b></td></tr>' ;
SET @Body = ( SELECT td = CONVERT(VARCHAR(10), P.Date, 120), '',
td = P.ID, '',
td = P.ID2, '',
td = G.ID, '',
td = D.ID,'',
td = D.Name,'',
td = CASE WHEN G.SubmittedDate IS NULL THEN 'New'
ELSE 'Dealer Submitted'
END, ''
FROM I_CancelledGRN I
INNER JOIN TxnGRN G ON G.ID = I.ID
INNER JOIN Distributor D ON D.UID = G.DistributorUID
INNER JOIN POTxn P ON P.SiteUID = G.POTxn_SiteUID
AND P.UID = G.POTxnUID
WHERE IsCancelled IS NULL
FOR XML RAW('tr'),
ELEMENTS
)
SELECT @Body = @TableHead + ISNULL(@Body, '') + @TableTail
Step 2: create database email profile
Step 3: Send Email
EXEC sp_send_dbmail
@profile_name='DatabaseEmailProfile',
@copy_recipients ='aasc@stackexchange.com',
@recipients='aa.sc@outlook.com',
@subject='Query Result',
@body=@Body ,
@body_format = 'HTML' ;
No comments:
Post a Comment