SQL Server’s Database Mail has a stored procedure called sp_send_dbmail that you can use to send emails from SQL Server.

You can use this stored procedure to send query results formatted into an HTML table.

Example

Here’s an example to demonstrate.

DECLARE @body_content nvarchar(max);
SET @body_content = N'
<style>
table.GeneratedTable {
  width: 100%;
  background-color: #ffffff;
  border-collapse: collapse;
  border-width: 2px;
  border-color: #ffcc00;
  border-style: solid;
  color: #000000;
}

table.GeneratedTable td, table.GeneratedTable th {
  border-width: 2px;
  border-color: #ffcc00;
  border-style: solid;
  padding: 3px;
}

table.GeneratedTable thead {
  background-color: #ffcc00;
}
</style>

<table class="GeneratedTable">
  <thead>
    <tr>
      <th>ArtistId</th>
      <th>ArtistName</th>
      <th>ActiveFrom</th>
    </tr>
  </thead>
  <tbody>' +
CAST(
        (SELECT td = ArtistId, '',
                td = ArtistName, '',
                td = ActiveFrom, ''
        FROM Music.dbo.Artists
        FOR XML PATH('tr'), TYPE   
        ) AS nvarchar(max)
    ) +
  N'</tbody>
</table>';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'DB Admin Profile',  
    @recipients = '[email protected]',  
    @body = @body_content,
    @body_format = 'HTML',
    @subject = 'As discussed';

In this case, I declare a variable called @body_content and put all the HTML code into it. I also add some styles using CSS, so that the table has visible borders, headers, etc. This variable is used as the value of the @body argument when sending the email with the sp_send_dbmail procedure.

If you don’t need any styles to be applied, simply remove the <style></style> tags and everything in between. In this case, you can also remove the class="GeneratedTable" bit from the actual table.

If you want to change the styles, check out my table generator over at Quackit. You can use it to generate your table styles, then you can copy/paste those styles into the code that goes into your emails.

The T-SQL code passed to the CAST() function is what returns the query results, each in their own table row (<tr> element), and each column within its own <td> element.

Be sure to include @body_format = 'HTML'. This is what sends the email in HTML format. If you omit this (or if you set it to TEXT), the recipient will receive the raw HTML code, instead of the rendered code.