Sending HTML Table in Email Using SQL Server

•    Today I am going explain how we can send html table email from SQL server.  One of my client have requirement they want employee’s list are highlight with some background color when employee‘s basic salary is more than 15000 in Email.

•  Instead of going to application level I will manage things from SQL server and I have used SQL dbmail functionality which is available in SQL Server full version.

•  SQL DB email function, first we need to create profile name in SQL Server and need to configure with our domain.

•  First I have declare tables

DECLARE @tableHTML NVARCHAR(MAX)

• From Above query I have get Employees in html tables structure which include Basic Salary and  I have took those employee in “Red” background whose Basic Salary is more than 15,000 and rest of employee have white background.

• Generated  “tableHTML” looks like following in HTML form

• Now I need to send email of above template from SQL Server. We can use following query to send email to receipt

EXEC msdb.dbo.sp_send_dbmail

    @profile_name='Nilay',

    @recipients = 'qa1@orangewebtech.com',

    @from_address = 'nilay@orangewebtech.com',

    @subject = 'Employee List',

    @body = @tableHTML,

    @body_format = 'HTML';

• We can also used SQL Jobs for email list of employees on specified duration.