Html Table in Email

Question

question

siera_gld asked Mar 2, '12

Html Table in Email

I am trying to use html / xml in building an email body... This code works and successfully builds two tables/ results and sends an email using the send_db_mail. But when thte eamil gets forwarded from the client(outlook), the second table gets stripped... DECLARE @NAME VARCHAR(100) DECLARE @TableHead1 VARCHAR(MAX), @TableTail1 VARCHAR(MAX), @TableHead2 VARCHAR(MAX), @TableTail2 VARCHAR(MAX), @EmailMsgSecTion1 VARCHAR(MAX), @EmailMsgSecTion2 VARCHAR(MAX) DECLARE @BODY VARCHAR(MAX) SET @EMAIL_ID = (SELECT TOP 1(Email) FROM #REPORTS WHERE EMAIL IS NOT NULL) SET @NAME = (SELECT TOP 1(DisplayName) FROM #Reports WHERE Email = @EMAIL_ID) Set @EmailMsgSecTion1 = '

PEASE VERIFY REPORTS USED for '+@NAME+'

Email would have gone to '+@EMAIL_ID+'
' Set @EmailMsgSecTion2 = 'Below is a list of subscriptions found that are currently being sent to you' Set @TableTail1 = ''; Set @TableHead1 = '' + '' + '' + '' + ' Report Name' + ' Report Link' + ' Last Used'; Set @TableTail2 = ''; Set @TableHead2 = '' + '' + '' + '' + ' Report Name' + ' Report Link' + ' Unsubscribe Link'; WHILE EXISTS(SELECT TOP 1 Email FROM #Reports where email is not null) BEGIN DECLARE @Body1 NVARCHAR(MAX) SET @Body1 = CAST((SELECT ReportName AS 'td', '', ' '+[URL]+'' AS 'td','', CONVERT(CHAR(10),LastUsed,110) AS 'td','' FROM #Reports WHERE Email = @EMAIL_ID FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX) ) DECLARE @Body2 NVARCHAR(MAX) SET @Body2 = CAST((SELECT DISTINCT x.REPORT_NAME AS 'td', '', ' '+r.[URL]+'' AS 'td','', ' '+REMOVE_SUB+'' AS 'td','' FROM #ITEM_EMAIL_XREF x join #Reports r on r.ReportName COLLATE SQL_Latin1_General_CP1_CI_AS = x.REPORT_NAME WHERE x.Email = @EMAIL_ID FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX) ) Set @body = ''+@EmailMsgSecTion1+'
'+ @TableHead1 + REPLACE(REPLACE(@Body1,'<','') + @TableTail1 + '

'+ @EmailMsgSecTion2+''+@TableHead2 + REPLACE(REPLACE(@Body2,'<','') + @TableTail2+''

xml html send_db_mail

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-03-02T20:27:00Z

dvroman answered Mar 2, '12

I'm guessing, but I believe it is because you have two <html><body>...</body></html> tags in the script. It should only have one. The structure should be:

<html><head>
<style>
...
</style>
</head>
<body>
<table>
...
</table>
<table>
...
</table>
</body></html>

I believe the email client is more restrictive than your browser. If this is correct, your email should look correct in a web based browser, but not in outlook.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question