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.

undefined

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.

mm

Aravind

Aravinth AKA Parattai is amazingly fun filled consultant with timely solutions. His timing of jokes are also simply superb. He joined Avaniko as a fresher and has strong experience in handling international customers and expertise in application integrations.

Previous Post
Next Post

Leave a Reply

Your email address will not be published. Required fields are marked *

error

Enjoy this blog? Please spread the word :)

RSS
Follow by Email
LinkedIn
Share