Create HTML/XML Emails Using SQL Server

Step 1: Configure Your Database Mail
Step 2: Use the following stored procedure

    exec  msdb.dbo.sp_send_dbmail
    @profile_name =@profile,
    @recipients = @To,
    @copy_recipients = @Cc,
    @blind_copy_recipients = @Bcc,
    @subject =@Subject,
    @body =@Body,
    @body_format=@Format,
    @file_attachments=@Attachment,
    @exclude_query_output=1

Step 3: Create your SQL Query 

declare @BodyContent varchar(max),
      @TableHeader varchar(max),
      @TableFooter varchar(max)

Set @TableFooter = ‘</table></body></html>’;
Set @TableHeader = ‘<html><head>’ +
                  ‘<style>’ +
                  ‘td {padding-left:5px;padding-right:5px;padding-top:5px;padding-bottom:5px;font-family: Arial, Helvetica, sans-serif; font-size: 10pt;} ‘ +
                  ‘</style>’ +
                  ‘</head>’ +
                  ‘<body><table cellpadding="5" cellspacing="0" border="1" bordercolor="#C0C0C0" style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; border-collapse:collapse;border-bottom-style:solid;">’ +
                  ‘<tr bgcolor=#FFEFD8><td align=center style="background-color: #359AFF; color: white"><b>Server Name</b></td>’ +
                  ‘<td align=center style="background-color: #359AFF; color: white"><b>Product</b></td>’ +
                  ‘<td align=center style="background-color: #359AFF; color: white"><b>Data Source</b></td>’;
Select @BodyContent = (Select Row_Number() Over(Order By name) % 2 As [ALTCOLOR],
            name As [TD],
            product As [TD],
            data_source As [TD align=center]
      From sys.servers
      Order By name
      For XML raw(‘tr’), Elements)

Set @BodyContent = Replace(@BodyContent, ‘_x0020_’, space(1))
Set @BodyContent = Replace(@BodyContent, ‘_x003D_’, ‘=’)
Set @BodyContent = Replace(@BodyContent, ‘<tr><ALTCOLOR>1</ALTCOLOR>’, ‘<tr bgcolor=#DFFFDF>’)
Set @BodyContent = Replace(@BodyContent, ‘<ALTCOLOR>0</ALTCOLOR>’, ”)

Select @BodyContent = @TableHeader + @BodyContent + @TableFooter

Select @BodyContent

Step 4: Use msdb.dbo.sp_send_dbmail to send email @body=@BodyContent

@BodyContent Output:
image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s