Formatting issue in email when using sp_send_dbmail

Question

I can get the email to send and the query results are fine, but the format in which the email arrives is driving me crazy. Here is the SQL:

DECLARE @Body varchar(max)
declare @TableHead varchar(max)
declare @TableTail varchar(max)
declare @mailitem_id as int
declare @statusMsg as varchar(max)
declare @Error as varchar(max) 
declare @Note as varchar(max)

Set NoCount On;
set @mailitem_id = null
set @statusMsg = null
set @Error = null
set @Note = null
Set @TableTail = '</table></body></html>';

--HTML layout--
Set @TableHead = '<html><head>' +
'<H1 style="color: #000000">New Contact</H1>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#F6AC5D>'+
'<td align=center><b>FirstName</b></td>' +
'<td align=center><b>lastname</b></td>' +
'<td align=center><b>ContactValue</b></td>' +
'<td align=center><b>ClientMessage</b></td>' + 
'<td align=center><b>timestamp</b></td></tr>';

--Select information for the Report-- 
Select @Body= (SELECT 
FirstName 
,lastname 
,CASE 
    WHEN MethodOfContact = 'emailChecked'
        THEN ContactEmail
    WHEN MethodOfContact = 'phoneChecked'
        THEN PhoneNumber
        ELSE ContactEmail
    END AS ContactValue
,ClientMessage
,Addts as timestamp
FROM TABLE1
WHERE Addts > (
    SELECT MAX(ADDTS)
    FROM TABLE2)

For XML path('tr'), Elements) 

-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')


Set @Body = @TableHead + @Body + @TableTail

-- return output--
Select @Body

--Email
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'GPG Infrastructure Team',
@mailitem_id = @mailitem_id out,
@recipients='myemail@email.com',
@subject = 'NewContactRecord',
@body = @Body,
@body_format = 'HTML';

The resulting email contains all 5 columns, but the data points are all crammed into the first column. How do I get each value to appear in each appropriate column? I am not very familiar with HTML at all, so this has been pieced together with what I've found online. Much thanks! email screenshot

Here is a sample of the HTML output:

<html>
   <head>
      <H1 style="color: #000000">New Contact</H1>
      <style>td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} </style>
   </head>
   <body>
      <table cellpadding=0 cellspacing=0 border=0>
         <tr bgcolor=#F6AC5D>
            <td align=center><b>FirstName</b></td>
            <td align=center><b>lastname</b></td>
            <td align=center><b>ContactValue</b></td>
            <td align=center><b>ClientMessage</b></td>
            <td align=center><b>timestamp</b></td>
         </tr>
         <tr>
            <FirstName>Shannon</FirstName>
            <lastname>Smith</lastname>
            <ContactValue>email.value@email.com</ContactValue>
            <ClientMessage>Test</ClientMessage>
            <timestamp>2019-03-13T12:57:17.723</timestamp>
         </tr>
         <tr>
            <FirstName>Gwen</FirstName>
            <lastname>Smith</lastname>
            <ContactValue>email.value@email.com</ContactValue>
            <ClientMessage>testing 3/13/2019 4:15 pm</ClientMessage>
            <timestamp>2019-03-13T16:18:30.600</timestamp>
         </tr>
         <tr>
            <FirstName>Gwen</FirstName>
            <lastname>Smith</lastname>
            <ContactValue>email.value@email.com</ContactValue>
            <ClientMessage>another one 4:19 pm</ClientMessage>
            <timestamp>2019-03-13T16:19:36.020</timestamp>
         </tr>

Possible duplicate of [Create HTML Table with SQL FOR XML](https://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml) — saritonin, Mar 14 '19 at 20:23
Not a duplicate of https://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml My HTML output is correct, it's just not displaying correctly in the email. — jw11432, Mar 14 '19 at 21:01
How do you know that your HTML output is correct? It sounds like you're having the exact same problem that was described in the other question linked. You need to make sure each data element is in its own instead of just the that defines the table row. — saritonin, Mar 14 '19 at 21:14
While each data value is correctly labeled (firstname, last name, etc.) I just realized that each one is under a separate . As stated, I don't really know HTML, so I wasn't sure where I went wrong. But I did manage to figure it out and your recommendation was correct, it just wasn't immediately obvious. Thank you for your time! — jw11432, Mar 14 '19 at 21:43

score 0 · Answer 1 · answered Mar 15 '19 at 14:03

The issue was with my select statement. What ended up making it work was the following:

Select @Body= (SELECT 
(select a.FirstName as 'td'  for xml path(''), type),
(select a.LastName as 'td'  for xml path(''), type),
(select CASE 
    WHEN MethodOfContact = 'emailChecked'
        THEN ContactEmail
    WHEN MethodOfContact = 'phoneChecked'
        THEN PhoneNumber
        ELSE ContactEmail
    END as 'td'  for xml path(''), type) AS ContactValue ,
(select a.ClientMessage as 'td'  for xml path(''), type), 
(select cast(a.AddTs as smalldatetime) as 'td'  for xml path(''), type) as Timestamp
FROM Table1 a

Formatting issue in email when using sp_send_dbmail

1 Answers1