HTML Mail with more than one line in Resultset

  • tutter

    SSC Enthusiast

    Points: 116

    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.

  • Joe Torre

    SSChampion

    Points: 10247

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

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • tutter

    SSC Enthusiast

    Points: 116

    Thanks Joe Torre and Jeffrey Williams!

     

    Great Job Jeffrey Williams!

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

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