This post contains a template for sending HTML tables via mail in T-SQL. A prerequisite is that you have created a Database Mail Profile. If you don’t have an email server available, you can use Gmail instead as demonstrated here.
The system stored procedure msdb.dbo.sp_send_dbmail has an @query parameter to include the results from a query in the body of the email. This method works fine for many simple queries. But sometimes I want more control over the look of the table, and that’s when I use HTML tables instead.
Let’s start with some sample data. Run the following script to create a database and table with some sample customer data:
CREATE DATABASE HTMLMailDemo
GO
USE HTMLMailDemo
GO
CREATE TABLE dbo.Customers
(
CustomerId INT IDENTITY(1,1) NOT NULL CONSTRAINT Customers_PK PRIMARY KEY CLUSTERED
,FirstName VARCHAR(100) NOT NULL
,LastName VARCHAR(100) NOT NULL
,StreetAddress VARCHAR(100) NOT NULL
,Phone VARCHAR(20) NULL
,Added DATETIME2 NOT NULL
)
GO
INSERT
dbo.Customers (FirstName, LastName, StreetAddress, Phone, Added)
VALUES
('Tomas', 'Lind', 'The Road 123', '111-222-333', '2015-06-24')
,('Test', 'Testsson', 'Steet 45A ', '555-222-333', '2015-06-23')
,('Bill', 'Bull', 'Old Town 999 abcdefghijklmnopqrstuvwxyz_abcdefghijklmnopqrstuvwxyz', NULL, '2015-06-22')
GO
SELECT * FROM dbo.Customers
Now, to send an email containing an HTML table with the customers above, use the following template:
DECLARE @MAIL_BODY VARCHAR(8000) /* HEADER */ SET @MAIL_BODY = '<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:consolas;text-align:center;">' + '<tr> <th>Customer Id</th> <th>First Name</th> <th>Last Name</th> <th>Street Address</th> <th>Phone</th> <th>Added</th> </tr>' /* ROWS */ SELECT @MAIL_BODY = @MAIL_BODY + '<tr>' + '<td>' + CAST(CustomerId AS VARCHAR(11)) + '</td>' + '<td>' + FirstName + '</td>' + '<td>' + LastName + '</td>' + '<td>' + SUBSTRING(StreetAddress, 1, 30) + CASE WHEN LEN(StreetAddress) > 30 THEN '...' ELSE '' END + '</td>' + '<td>' + ISNULL(Phone, ' ... ') + '</td>' + '<td>' + CAST(CAST(Added AS DATE) AS VARCHAR(10)) + '</td>' + '</tr>' FROM dbo.Customers SELECT @MAIL_BODY = @MAIL_BODY + '</table>' EXEC msdb.dbo.sp_send_dbmail @profile_name = <YourEmailProfile>, @recipients = <YourEmailRecipients>, @subject = 'Our Customers', @body = @MAIL_BODY, @body_format='HTML'
The actual HTML is stored in the @MAIL_BODY variable. First the initial table tag is created. Here are some properties that can be modified, such as font and border sizes.
Next, the table column headers are created (under the /* HEADER */ comment). Note the starting and closing tr (table row) tag.
The actual data is added with the next statement (under the /* ROWS */ comment). Starting and closing tr tags must be used. Note that all columns are casted to varchar (unless they already are varchar). For a normal string column, use for instance the FirstName column as template. To handle NULL values, use the Phone column template. A NULL value is replaced with three dots “…”. An empty string or whatever could also be used, but never return NULL values. Finally, the StreetAddress column shows a solution for only returning a subset of the characters in a column. The StreetAddress can be up to 100 characters, but in the code above only the first 30 are returned. If the string is shortened, “…” is appended to illustrate.
The final statement before sending the email is to close the HTML table with the /table tag.
Lastly, the email is sent using the msdb.dbo.sp_send_dbmail procedure. Remember to replace the YourEmailProfile and YourEmailRecipients placeholders.
Depending on your email client, the resulting email may look something like this:
If you want to alternate the font color per row, for instance to make the text red in case of error, replace the:
'<tr>' +
row above (line 17) with:
'<tr' + CASE WHEN CustomerId = 1 THEN ' style="color:red;">' ELSE ' style="color:black;">' END +
Replace with your own CASE statement.
How come you have black background on your management studio?
Hi, the code is not from management studio, it’s from a worpress plugin called SyntaxHighlighter Evolved.
take a look at vsql-email (sql-email.com) this tool is exactly what I needed
Thank you Tomas!
You are welcome 🙂
Hi I am getting below message. Can you help me out regarding this type conversion error
Msg 245, Level 16, State 1, Procedure automail_CustomerOrderStatusM, Line 73
Conversion failed when converting the varchar value ” to data type int.
Hi, ” is a string and that can’t be converted to a integer. If it is possible, use a 0 (or some other int) instead. If not, change the data type to string. (Don’t know what the code does, it’s not from my code in the post, right?)
How can i Convert this query into table:
SELECT
(select Categories.category from Categories where Categories.Categoryid = empattend.AttendenceType) AttendenceType,
(select Categories.category from Categories where Categories.Categoryid = empattend.CheckoutFor) CheckoutFor,
(select FirstName + ‘ ‘ + lastname from users where employeeid=empattend.EmployeeID) EmployeeName,
COALESCE(empattend.CheckinTime,empattend.Checkouttime)as AttendanceTime,
CAST(
CASE
WHEN empattend.CheckinTime IS not null and convert(time, empattend.CheckinTime)<'" + _LateCheckin+"' THEN 1 ELSE 0 END AS int) as late FROM EmployeeAttendance empattend LEFT JOIN EmployeeAttendance empattend2 ON (empattend.EmployeeId = empattend2.EmployeeId AND empattend.AttendanceID < empattend2.AttendanceID) WHERE empattend2. AttendanceID IS NULL and CONVERT(date, empattend.AttendanceDate)=CONVERT(date, getdate())
It gives following error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'CheckoutFor'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'EmployeeName'.
Great example, how to avoid not getting an email of no rows is returned? I tried your script, and it sends an email regardless. You have if @mail_body is not bull, but since we have set the @mail_body with the table header, there is always going to be a row.
Sorry Meant when no rows are returned… I was not paying attention.
I made a slight change to your query by adding a parm called it @MAIL_HDR, where I set the table definition there, and I placed the @MAIL_HDR after the checking for is not null returned from the select result.
SET @MAIL_TBL = ” +
‘
tbl rows…
‘
/* ROWS */
SELECT
@MAIL_BODY =
” +
” + … the select code goes here
from …
if @MAIL_BODY is not null
begin
set @MAIL_BODY = @MAIL_TBL + @mail_BODY
SELECT @MAIL_BODY = @MAIL_BODY + ”
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ’email_addr’,
@recipients = ’email_addr’,
@subject = @Subject,
@body = @MAIL_BODY,
@body_format=’HTML’
end
Hi, thanks for the feedback and for providing a solution too! 🙂
How would I show gridlines in the email body where the html tabular data is displayed? Is there a statement I need to add in my query ? Currently my results are populated in tabular format in the email body but the users require gridlines to be visible.
how to get date with with datetime format in table ?
How can this code work ? doesn’t give below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
This would only email the first row
What if my table has 0 rows. How will the email appear? I want the email to contain just the table headers with 0 rows whenever there is no data in the table and also with contents whenever there is data in the table. How to do that. Thanks!
Is this still working today? Apologies for the bump.
Hello, Trying to use this query, but getting only one row, the last row of a dataset. what am I doing wrong? – Thank you.