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

siera_gld gravatar image

siera_gld
1k 78 84 85

(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

dvroman gravatar image

dvroman
1.1k 2 2

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x150
x10
x1

asked: Mar 02, 2012 at 06:23 PM

Seen: 3174 times

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