SQL Database Mail With Table Format
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:6px;padding-right:6px;padding-top:4px;padding-bottom:4px;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>Special Offer ID</b></td>'
+ '<td bgcolor=#E6E6FA><b>Description</b></td>'
+ '<td bgcolor=#E6E6FA><b>Type</b></td>'
+ '<td bgcolor=#E6E6FA><b>Category</b></td>'
+ '<td bgcolor=#E6E6FA><b>Start Date</b></td>'
+ '<td bgcolor=#E6E6FA><b>Today Date</b></td>'
+ '<td bgcolor=#E6E6FA><b>EndDate</b></td></tr>' ;
SET @Body = (
SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',
td = [Description],'',
td = [Type],'',
td = [Category] ,'',
td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
td = CONVERT(VARCHAR(30),GETDATE(),120) ,'',
td = CONVERT(VARCHAR(30),[EndDate],120)
FROM [AdventureWorks2012].[Sales].[SpecialOffer]
ORDER BY [SpecialOfferID]
FOR XML PATH('tr'),ELEMENTS )
SELECT @Body = @TableHead + ISNULL(@Body, '') + @TableTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database2012Mail',
@recipients='vagalla.suresh@gmail.com',
@subject = 'Work Order List',
@body = @Body,
@body_format = 'HTML' ;
Result:
