HTML Mail with more than one line in Resultset

Question

Post reply

HTML Mail with more than one line in Resultset

tutter

SSC Enthusiast

Points: 116
More actions
May 25, 2020 at 2:19 pm
Go to Answer
#3757225

Hello all,
i would like to send a html Mail with sql server. But i can´t solve it by more than one line.
Resultset: Select car,model,engine from cars
Honda, Civic, 1.6
VW, Golf, 1.5
Dodge, Viper, 8.4
In my Mail i would receive a html table with:
<table>
<tr>
<th>Car</th>
<th>Model</th>
<th>Engine</th>
</tr>
<tr>
<td>Honda</td>
<td>Civic</td>
<td>1.6</td>
</tr>
<tr>
<td>VW</td>
<td>Golf</td>
<td>1.5</td>
</tr>
<tr>
<td>Dodge</td>
<td>Viper</td>
<td>8.4</td>
</tr>
</table>

One line in HTML Table is no problem but how can i create a table from more than one line in the resultset?
Thanks for helping.

Jeffrey Williams

SSC Guru

Points: 90303

May 25, 2020 at 4:19 pm

Answer

#3757258

To send an HTML email - you have to provide the html code in the body of the message. Whether you build out that code manually - or within your SQL all depends on how you want that email configured.

To create a table - you need to define the HTML table, setting the borders, the table header, the table rows, etc. To do that you can either embed the definitions in the TH or TD or TR segments - or use an embedded CSS style sheet.

Here is a template I use for these requests - you can adjust the background, borders, padding, fonts, etc... as needed.

    Set Nocount On;

Declare @body nvarchar(max)
      , @xmlResults varchar(max)
      , @tableHeader varchar(max)
      , @recipients varchar(max) = 'recipient1@domain.com'
      , @cc_recipients varchar(max) = 'ccrecipient1@domain.com;ccrecipient2@domain.com';

 --==== Create the table header
    Set @tableHeader = cast((Select html.hdr1 As th, ''
                                  , html.hdr2 As th, ''
                                  , html.hdr3 As th, ''
                               From (  
                             Values ('Car', 'Model', 'Engine')
                                    )  As html(hdr1, hdr2, hdr3)
                                For xml Path('tr'), elements) As varchar(max));

 --==== Get the results as an XML table
    Set @xmlResults = cast((Select Car As td, ''
                                 , Model As td, ''
                                 , Engine As td, ''
                              From Cars 
                             Order By
                                   Car
                                 , Model
                               For xml Path('tr'), elements) As varchar(max));

 --==== Send Notification if we have results
     If @xmlResults Is Not Null 
  Begin

--==== Setup the CSS style of the message
    Set @body =  '<style type=''text/css''>';
    Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
    Set @body += 'td {font-size:10pt; text-align:center; border:1px DarkCyan solid; padding:2px 2px 2px 2px;}';
    Set @body += 'th {font-size:10pt; text-align:center; padding:2px 2px 2px 2px; background-color:DarkCyan; color:White;}';
    Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
    Set @body += '</style>'

 --==== Setup the body of the message
    Set @body += '<html><body>The following table lists the cars, models and engines.
 ';

 --==== Setup the table with the defined list
    Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';

 --==== Close the body and html
    Set @body += '</body></html>';

 --==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
        @profile_name = 'Public Profile'
      , @from_address = 'FromAddress@domain.com'
      , @reply_to = 'ReplyToAddress@domain.com'
      , @recipients = @recipients
      , @copy_recipients = @cc_recipients
      , @subject = 'Cars - Models - Engines'
      , @body_format = 'HTML'
      , @body = @body;

    End 
     Go

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

XML output with xsd file could be used to present data in a table. https://www.w3schools.com/xml/schema_example.asp

tutter SSC Enthusiast Points: 116 More actions · Answer 2

Thanks Joe Torre and Jeffrey Williams!

Great Job Jeffrey Williams!