Send HTML Mail Using T-SQL

• dudik

  SSC Enthusiast

  Points: 196

  More actions

  February 4, 2018 at 8:55 am

  #410206

  Hello,
  I'm trying to create a SP that will take a Temp Table and will send it as an HTML mail.
  The following code send the tabke but it does not fill the content of the table.
  If I will change the line ... "td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName1, '')) ,'',"
  to td = CONVERT(NVARCHAR(200), ISNULL(C1, '')) ,'',

  Then the send mail will work.

  
--USE IDEMaintenance
--GO

  --ALTER PROCEDURE [dbo].[SendEMail_HTML_6Columns_Table]
--(
--@subject_Input     nvarchar(max),
--@TableHeader_Input     nvarchar(max)
--)
--AS
--BEGIN
--DROP TABLE ##MailTable_6Columns
CREATE TABLE ##MailTable_6Columns
(
C1     NVARCHAR(50),
C2     NVARCHAR(50),
C3     BIGINT,
C4     INT,
C5     NVARCHAR(50),
C6     NVARCHAR(250)
)

  INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')
INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')
INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')
INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')

  --GetSettings
DECLARE     @subject_Input     NVARCHAR(250)
DECLARE     @TableHeader_Input NVARCHAR(250)

  DECLARE     @AdminMail NVARCHAR(250)
SET         @AdminMail = (Select SettingsValue From IDEMaintenance.dbo.Settings WHERE SettingsKey = 'AdminMail')

  --DECLARE     @bodyMsg nvarchar(max)
DECLARE     @subject nvarchar(max)
DECLARE     @TableHeader nvarchar(max)
DECLARE     @tableHTML nvarchar(max)

  DECLARE     @ColumnName1 nvarchar(100)
DECLARE     @ColumnName2 nvarchar(100)
DECLARE     @ColumnName3 nvarchar(100)
DECLARE     @ColumnName4 nvarchar(100)
DECLARE     @ColumnName5 nvarchar(100)
DECLARE     @ColumnName6 nvarchar(100)

  SET @ColumnName1 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 1)
SET @ColumnName2 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 2)
SET @ColumnName3 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 3)
SET @ColumnName4 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 4)
SET @ColumnName5 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 5)
SET @ColumnName6 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 6)

  IF @subject_Input IS NOT NULL
  SET @subject = @subject_Input
ELSE
  SET @subject = 'Mail Subject'

  IF @TableHeader_Input IS NOT NULL
  SET @TableHeader = @TableHeader_Input
ELSE
  SET @TableHeader = ''

  SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 14px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-top: 2px solid #9baff1;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-top: 1px solid #aabcfe;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">'+

  @TableHeader +

  N'<table id="box-table" >' +
N'<tr><font color="Green">' +
N'<th>' + @ColumnName1 + N'</th>' +
N'<th>' + @ColumnName2 + N'</th>' +
N'<th>' + @ColumnName3 + N'</th>' +
N'<th>' + @ColumnName4 + N'</th>' +
N'<th>' + @ColumnName5 + N'</th>' +
N'<th>' + @ColumnName6 + N'</th>' +
N'</tr>' +

  CAST     
  (
     (
     SELECT
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName1, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName2, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName3, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName4, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName5, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName6, '')) ,''
     FROM [##MailTable_6Columns]
     FOR XML PATH('tr'), TYPE
     )
     AS NVARCHAR(MAX)
  ) +
N'</table>'

  EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfile',
@recipients=     @AdminMail,
@subject =     @subject,
@body =         @tableHTML,
@body_format =     'HTML' ;

  --END


  Thansks in advance,
  David

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  February 4, 2018 at 11:59 am

  #1978470

  dudik - Sunday, February 4, 2018 8:55 AM

  Hello,
  I'm trying to create a SP that will take a Temp Table and will send it as an HTML mail.
  The following code send the tabke but it does not fill the content of the table.
  If I will change the line ... "td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName1, '')) ,'',"
  to td = CONVERT(NVARCHAR(200), ISNULL(C1, '')) ,'',

  Then the send mail will work.

  
--USE IDEMaintenance
--GO

  --ALTER PROCEDURE [dbo].[SendEMail_HTML_6Columns_Table]
--(
--@subject_Input     nvarchar(max),
--@TableHeader_Input     nvarchar(max)
--)
--AS
--BEGIN
--DROP TABLE ##MailTable_6Columns
CREATE TABLE ##MailTable_6Columns
(
C1     NVARCHAR(50),
C2     NVARCHAR(50),
C3     BIGINT,
C4     INT,
C5     NVARCHAR(50),
C6     NVARCHAR(250)
)

  INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')
INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')
INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')
INSERT INTO ##MailTable_6Columns (C1, C2, C3, C4, C5, C6) VALUES ('A', 'B', '1', '1', 'C', 'D')

  --GetSettings
DECLARE     @subject_Input     NVARCHAR(250)
DECLARE     @TableHeader_Input NVARCHAR(250)

  DECLARE     @AdminMail NVARCHAR(250)
SET         @AdminMail = (Select SettingsValue From IDEMaintenance.dbo.Settings WHERE SettingsKey = 'AdminMail')

  --DECLARE     @bodyMsg nvarchar(max)
DECLARE     @subject nvarchar(max)
DECLARE     @TableHeader nvarchar(max)
DECLARE     @tableHTML nvarchar(max)

  DECLARE     @ColumnName1 nvarchar(100)
DECLARE     @ColumnName2 nvarchar(100)
DECLARE     @ColumnName3 nvarchar(100)
DECLARE     @ColumnName4 nvarchar(100)
DECLARE     @ColumnName5 nvarchar(100)
DECLARE     @ColumnName6 nvarchar(100)

  SET @ColumnName1 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 1)
SET @ColumnName2 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 2)
SET @ColumnName3 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 3)
SET @ColumnName4 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 4)
SET @ColumnName5 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 5)
SET @ColumnName6 = (SELECT name FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..##MailTable_6Columns') AND column_id = 6)

  IF @subject_Input IS NOT NULL
  SET @subject = @subject_Input
ELSE
  SET @subject = 'Mail Subject'

  IF @TableHeader_Input IS NOT NULL
  SET @TableHeader = @TableHeader_Input
ELSE
  SET @TableHeader = ''

  SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 14px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-top: 2px solid #9baff1;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-top: 1px solid #aabcfe;
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">'+

  @TableHeader +

  N'<table id="box-table" >' +
N'<tr><font color="Green">' +
N'<th>' + @ColumnName1 + N'</th>' +
N'<th>' + @ColumnName2 + N'</th>' +
N'<th>' + @ColumnName3 + N'</th>' +
N'<th>' + @ColumnName4 + N'</th>' +
N'<th>' + @ColumnName5 + N'</th>' +
N'<th>' + @ColumnName6 + N'</th>' +
N'</tr>' +

  CAST     
  (
     (
     SELECT
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName1, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName2, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName3, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName4, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName5, '')) ,'',
             td = CONVERT(NVARCHAR(200), ISNULL(@ColumnName6, '')) ,''
     FROM [##MailTable_6Columns]
     FOR XML PATH('tr'), TYPE
     )
     AS NVARCHAR(MAX)
  ) +
N'</table>'

  EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailProfile',
@recipients=     @AdminMail,
@subject =     @subject,
@body =         @tableHTML,
@body_format =     'HTML' ;

  --END


  Thansks in advance,
  David

  Anything concatenated with NULL will result in a NULL.  That means that you did the right thing by using ISNULL in the bit of information you added in your post above.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)