sp_send_dbmail sending HTML instead of HTML formatted email

Question

To begin, I have reviewed the following question and incorporated it into my problem: HTML Format in sp_send_dbmail

I am trying to dynamically build an email that would send to separate people if needed. It appears that my string builds just find and sends the email. The issue is that the email just appears as text that looks like HTML. Here is my SQL:

        HTML_CTE ( EmailRecipients, EmailSubject, EmailBody )
          AS ( SELECT   Strings.Email AS EmailRecipients ,
                        'NOTIFICATION: You have exceeded the alloted meal amount' AS EmailSubject ,
                        N'<HTML>
                        <HEAD>
                            <STYLE>
                                TD {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}
                            </STYLE>
                        </HEAD>
                        <BODY>
                            <H3>' + Strings.PersonName + N',</H3>
                            <br/>
                            <p> On the following dates you spent the indicated amounts:</p>
                            <br/>
                            <table cellpadding=0 cellspacing=0 border=0>
                                <TR>
                                    <TD>Date</TD>
                                    <TD></TD>
                                    <TD>Amount</TD>
                                </TR>'
                                + Strings.Exceptions + 
                            N'</table>
                            <p>You will see $' + CAST(Strings.TotalOver AS VARCHAR(MAX)) + N' deducted from your paycheck. </p>
                        </BODY>
                        </HTML>' AS EmailBody
               FROM     AllDataStrings_CTE Strings
             )
    SELECT  @ExecSQL = ( SELECT N'EXEC msdb.dbo.sp_send_dbmail'
                                + N' @profile_name = ''GrillExceptions'''
                                + N' ,@recipients = '''
                                + HTML_CTE.EmailRecipients + N''''
                                + N' ,@subject = ''' + HTML_CTE.EmailSubject
                                + N'''' + N' ,@body = ''' + HTML_CTE.EmailBody
                                + N'''' + N' ,@body_format = ''HTML''; '
                         FROM   HTML_CTE
                       FOR
                         XML PATH('')
                       );
SELECT  @ExecSQL;
EXEC sys.sp_executesql @ExecSQL;

There are a couple CTE's above that build up the table and get the correct data. The string that is output from the final SELECT is:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'GrillExceptions',
    @recipients = '*******@******.com',
    @subject = 'NOTIFICATION: You have exceeded the alloted meal amount',
    @body = '&lt;HTML&gt;&#x0D;       &lt;HEAD&gt;&#x0D;        &lt;STYLE&gt;&#x0D;         TD {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}&#x0D;        &lt;/STYLE&gt;&#x0D;       &lt;/HEAD&gt;&#x0D;       &lt;BODY&gt;&#x0D;        &lt;H3&gt;James Shinevar,&lt;/H3&gt;&#x0D;        &lt;br/&gt;&#x0D;        &lt;p&gt; On the following dates you spent the indicated amounts:&lt;/p&gt;&#x0D;        &lt;br/&gt;&#x0D;        &lt;table cellpadding=0 cellspacing=0 border=0&gt;&#x0D;         &lt;TR&gt;&#x0D;          &lt;TD&gt;Date&lt;/TD&gt;&#x0D;          &lt;TD&gt;&lt;/TD&gt;&#x0D;          &lt;TD&gt;Amount&lt;/TD&gt;&#x0D;         &lt;/TR&gt;&lt;TR&gt;&lt;TD&gt;2016-11-22&lt;/TD&gt;&lt;TD&gt;&amp;#x20;&lt;/TD&gt;&lt;TD&gt;8.50&lt;/TD&gt;&lt;/TR&gt;&lt;/table&gt;&#x0D;        &lt;p&gt;You will see $0.50 deducted from your paycheck. &lt;/p&gt;&#x0D;       &lt;/BODY&gt;&#x0D;                         &lt;/HTML&gt;',
    @body_format = 'HTML';

And this results in the following being sent in the body of an email:

<HTML> <HEAD> <STYLE> TD {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} </STYLE> </HEAD> <BODY> <H3>***** *****,</H3> <br/> <p> On the following dates you spent the indicated amounts:</p> <br/> <table cellpadding=0 cellspacing=0 border=0> <TR> <TD>Date</TD> <TD></TD> <TD>Amount</TD> </TR><TR><TD>2016-11-22</TD><TD>&#x20;</TD><TD>8.50</TD></TR></table> <p>You will see $0.50 deducted from your paycheck. </p> </BODY> </HTML>

The above code looks like this in Outlook:

I have checked and ensured that I am receiving other HTML formatted email. This issue is not that my email client is set for plain text. Any ideas or assistance would be appreciated.

Xavier J Xavier J · Accepted Answer · 2016-11-29T15:07:15

In your example, the @body argument is already HTML-encoded, so that's why you're getting HTML in the e-mail. Your use of FOR XML PATH is the culprit, as it is doing this encoding you don't need. Find a better approach to building @ExecSQL - I think you're overcomplicating it.

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'GrillExceptions',
    @recipients = '*******@******.com',
    @subject = 'NOTIFICATION: You have exceeded the alloted meal amount',
    @body = '&lt;HTML&gt;&#x0D;       &lt;HEAD&gt;&#x0D;        &lt;STYLE&gt;&#x0D;         TD {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}&#x0D;        &lt;/STYLE&gt;&#x0D;       &lt;/HEAD&gt;&#x0D;       &lt;BODY&gt;&#x0D;        &lt;H3&gt;James Shinevar,&lt;/H3&gt;&#x0D;        &lt;br/&gt;&#x0D;        &lt;p&gt; On the following dates you spent the indicated amounts:&lt;/p&gt;&#x0D;        &lt;br/&gt;&#x0D;        &lt;table cellpadding=0 cellspacing=0 border=0&gt;&#x0D;         &lt;TR&gt;&#x0D;          &lt;TD&gt;Date&lt;/TD&gt;&#x0D;          &lt;TD&gt;&lt;/TD&gt;&#x0D;          &lt;TD&gt;Amount&lt;/TD&gt;&#x0D;         &lt;/TR&gt;&lt;TR&gt;&lt;TD&gt;2016-11-22&lt;/TD&gt;&lt;TD&gt;&amp;#x20;&lt;/TD&gt;&lt;TD&gt;8.50&lt;/TD&gt;&lt;/TR&gt;&lt;/table&gt;&#x0D;        &lt;p&gt;You will see $0.50 deducted from your paycheck. &lt;/p&gt;&#x0D;       &lt;/BODY&gt;&#x0D;                         &lt;/HTML&gt;',
    @body_format = 'HTML';

sp_send_dbmail sending HTML instead of HTML formatted email

2 Answers