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:
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.