使用SP_SEND_DBMAIL时，电子邮件中格式化问题

我可以发送电子邮件，查询结果很好，但是电子邮件到达的格式使我发疯。这是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';

生成的电子邮件包含所有5列，但是数据点全部都塞入了第一列。如何使每个值出现在每个适当的列中？我根本不太熟悉HTML，因此与我在网上发现的内容一起拼凑了一下。非常感谢！电子邮件屏幕截图

这是HTML输出的样本：

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

问题是我的选择语句。最终使它起作用的是：

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

相关内容

最新更新

热门标签：