x

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 = '<font face="Arial"><H3>PEASE VERIFY REPORTS USED for '+@NAME+'</H3></font>
 <br />
 <font face="Arial">Email would have gone to '+@EMAIL_ID+'</Font><br />'
 
 Set @EmailMsgSecTion2 = '<font face="Arial">Below is a list of subscriptions found that are currently being sent to you</Font></Br>'
 
 
 Set @TableTail1 = '</table></body></html>';
 Set @TableHead1 = '<html><head>'  +
                   '<style>' +
                   'td {font-family: Arial;border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
                   '</style>' +
                   '</head>' +
                   '<body><table cellpadding=0 cellspacing=0 border=0>' +
                   '<tr bgcolor= #357EC7><td align=center><b>Report Name</b></td>' + 
                   '<td align=center><b>Report Link</b></td>' + 
                   '<td align=center><b>Last Used</b></td></tr>';
 Set @TableTail2 = '</table></body></html>';
 Set @TableHead2 = '<html><head>' +
                   '<style>' +
                   'td {font-family: Arial;border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
                   '</style>' +
                   '</head>' +
                   '<body><table cellpadding=0 cellspacing=0 border=0>' +
                   '<tr bgcolor= #357EC7><td align=center><b>Report Name</b></td>' + 
                   '<td align=center><b>Report Link</b></td>' + 
                   '<td align=center><b>Unsubscribe Link</b></td></tr>';
 
 
 
     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', '',        
                         '<a href="'+URL+'">'+[URL]+'</a>' 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', '',        
                         '<a href="'+r.URL+'">'+r.[URL]+'</a>' AS 'td','',                        
                         '<a href="'+REMOVE_SUB+'">'+REMOVE_SUB+'</a>'  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 = '<DIV>'+@EmailMsgSecTion1+'<br/>'+  
             @TableHead1 + 
             REPLACE(REPLACE(@Body1,'&LT;','<'),'&GT;','>') + 
             @TableTail1 +
             '<br /><br />'+ 
             @EmailMsgSecTion2+'</Br>'+@TableHead2 + 
             REPLACE(REPLACE(@Body2,'&LT;','<'),'&GT;','>') + 
             @TableTail2+'</DIV>'
more ▼

asked Mar 02, 2012 at 06:23 PM in Default

avatar image

siera_gld
1k 82 88 93

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 02, 2012 at 08:27 PM

avatar image

dvroman
1.1k 1 4 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x186
x16
x1

asked: Mar 02, 2012 at 06:23 PM

Seen: 3474 times

Last Updated: Mar 02, 2012 at 08:29 PM

Copyright 2016 Redgate Software. Privacy Policy