SQL Server - How to send the contents of a table or query in the body of the email as HTML

Views: 1.054
Reading Time: 8 minutes

Speak guys !!
In this post I come to bring a solution to a problem that almost every day I see in Whatsapp and Telegram groups: Send the content of one or more tables or queries in the body of an email as HTML.

The procedure that we are going to use in this post to take the contents of the table and transform it to HTML I had already shared in the article How to export data from a SQL Server table to HTML, but I see that whenever I sent the article, people still had difficulty sending the email, so I decided to create this article to resolve this doubt once and for all.

If you don't know how to set up Database Mail to start sending emails via SQL Server or are having trouble sending simple emails, visit the article SQL Server - How to enable and configure Database mail to send and monitor database emails (sp_send_dbmail).

Using sp_send_dbmail itself and the @query parameter

Probably one of the most used ways to send the result of a table by e-mail is using the parameter @query from sp_send_dbmail.

Example:

Result:

As you can see, although it is extremely simple to use, the result is very bad for data visualization.

Using sp_send_dbmail itself and the @attach_query_result_as_file parameter

I'll try sending this content as an attachment to see if it improves. Perhaps exporting to file, the output is a little better.

Example:

Result:

Another very bad result ... It didn't improve much.

Using XML to create a variable with the result in HTML

We will then use another approach to try to export the data from the table and send it in an email so that it is easy to view the data.

Example:

Result:

Oops !! It's getting much better! Now I'm going to customize it a little bit just to show you that you can play with the message design.

Example:

Result:

This email is getting really cool, huh ?? What if I want to send more than one table in the same email? Has as???

Example:

Result:

Easy Mode: stpExporta_Tabela_HTML_Output

Thinking of facilitating the sending of the contents of a table or query by email as HTML, I decided to create the Stored Procedure stpExporta_Tabela_HTML_Output to automate the transformation of the result of a table or query to HTML and be able to use this to create a file or send the HTML generated as the body of an email.

This procedure had already been shared in the article How to export data from a SQL Server table to HTML, but I decided to share it again here, with a focus on sending HTML email.

StpExporta_Tabela_HTML_Output code:
Click here to view the source code

Its use is very simple:

Result:

If I want to change the horizontal alignment, just use the @Ds_Alignment parameter:

Result:

If you want to use the result of a query instead of the content of an entire table, just use a temporary global table (## table), as the local temporary table (#table) will not work:

And to send the same email as the example using XML, it would look like this:

Result:

And that's it folks!
I hope you enjoyed this tip and see you in the next post.