Sending HTML tables in mail from SQL Server

Microsoft SQL Server

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
HTML Email Demo Customers Table

HTML Email Demo Customers Table

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:

HTML Mail Demo Customers Table In Mail

HTML Mail Demo Customers Table In Mail

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.

9 comments » Write a comment

  1. 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?)

  2. 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'.

Leave a Reply

Required fields are marked *.