HTML Formatted Emails from SQL Server

This blog covers the step by step process on sending an SQL query output to HTML Formatted Emails from SQL Server.

Please find the output of the query.

Please follow the below steps to send an SQL query output to HTML Formatted Emails from SQL Server

Create a query based on the user requirement and save the query has view.

      Then go to SQL server Agent and create new jobs.

• In General tab choose Name,Description,owner

• Then in steps tab choose New and enter the Step name and choose the database and in command type please design the HTML table structure as required. Syntax is given below

Command Syntax:

DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @sub NVARCHAR(200)=’Package Details’;

Declare @Text NVARCHAR(300) =’Package processing Report Details’;

SET @tableHTML =

   N’

  <P><H3><B>’+@Text+'</B></H3>

   </P>

   <table border=”1.5″>

     <tr>

  <td bgcolor=”#99CC33″>docnum</td>

  <td bgcolor=”#99CC33″>Docdate</td>

  <td bgcolor=”#99CC33″>doctotal</td>

  <td bgcolor=”#99CC33″>DocStatus</td>

 </tr> ‘+

    CAST ( ( SELECT distinct td=a.Start_Time,”,

      td=a.docnum,”,

      td=a.docdate,”,

td=a.doctotal,”,

          td=a.Docstatus,”

              from oinv a where a.docstatus = ‘O’

            FOR XML PATH(‘tr’), TYPE

    ) AS NVARCHAR(MAX) ) +      N’

 </table>’+

 N'<footer>

 <p><h3>Thanks & Regards</h3></p>

 <p><h3><B></b></h3></p>

 </footer>’

 EXEC msdb.dbo.sp_send_dbmail

 @profile_name=’SupportMail’,

 @recipients=’xxx@avaniko.com‘,

 @copy_recipients=’XXX@avaniko.com‘,

     @subject =@sub,

    @body = @tableHTML,

    @body_format = ‘HTML’ ;

 Select @tableHTML

Arguments :

[ @profile_name = ] ‘profile_name’ Is the name of the profile to send the message from.The profile_name must be the name of an existing Database Mail profile. @profile_name must be specified.

[ @recipients = ] ‘recipients’ Is a semicolon-delimited list of e-mail addresses to send the message to.

[ @copy_recipients = ] ‘copy_recipients’ Is a semicolon-delimited list of e-mail addresses to carbon copy the message to.

[ @subject = ] ‘subject’ Is the subject of the e-mail message.

 [ @body = ] ‘body’ Is the body of the e-mail message.

[ @body_format = ] ‘body_format’ Is the format of the message body. The headers of the outgoing message are set to indicate that the message body has the specified format. The parameter may contain one of the following values:

TEXT

HTML

• Next, choose schedules and pick the time for the mail and then click OK.