Send SQL results as HTML email

I was recently asked to create an alert to send an email to our project managers (and QA managers) when their project was 85% or more ‘complete.’  For us, 85% meant revenue compared to budgeted revenue.

I thought I would share the SQL code to create these types of alerts. If you wanted to create your own, you would need to update the @Data table variable and populate it with the data that meets your criteria. You would need to get your employee names and email addresses into the temp Employee table. Then you need to update the table headers and the select statement in the @tablerows section.

The steps involved in the SQL code:

  1. Declare variables for the employee cursors (used to email results to employees)
  2. Declare variable table to put SQL results into (this is the detail for the table rows)
  3. Insert the detail data to email out into the table variable
  4. Get distinct list of employees to send emails to from the results of the table variable
  5. Use cursor to loop through the employees and send them an email using dbmail with only their respective data

The result is sent as an HTML email that has some styling added in the <head> section of the html. Notice, too, that we are adding class ‘r’ to SOME <td> but not all, to align these cells to the right.

Then what I usually do is schedule this stored procedure to run as a job using SQL Server Agent.  Set it and forget it!

SQL code:

if object_id('zsp_sendPercentCompleteEmail', 'P') is not null drop proc zsp_sendPercentCompleteEmail;
go

create proc zsp_sendPercentCompleteEmail
as

--created by MSDE 10/4/19 to send emails to project managers and QA managers alerting them of their projects that are >=85% completed (based on revenue / budgeted revenue)
--per requestor, 1 email to each employee and exclude project contract type = 'TM'

set nocount on;
    	
declare @csrEmployee char(10), @csrEmpName varchar(100), @csrEmail varchar(100), @subject varchar(200), @bccEmail varchar(100)
select @subject = 'Project revenue >= 85% of budget'
select @bccEmail = 'someone@youremail.com'

---table to house data to include in table rows
declare @data table
(project varchar(16),
 project_desc varchar(100),
 manager1 varchar(10),
 pm_id36 varchar(10),
 budget varchar(20),
 revenue varchar(20),
 pcnt varchar(20)
 )

--insert data into the table
insert @data 
select 
	LEFT(p.Project,8) + '-' + RIGHT(RTRIM(p.project),4) as project,
	p.project_desc,
	p.manager1, 
	p.pm_id36,
	format(sum(s.eac_amount),'N2') budget,
	format(sum(s.act_amount),'N2') as revenue,
	format(case when sum(s.eac_amount) = 0 then 100 else (sum(s.act_amount) / sum(s.eac_amount))*100 end,'N2') + ' %' pcnt
from pjproj p (NOLOCK)
 	join pjptdsum (NOLOCK) s 
 		on p.project = s.project
 	join pjacct (NOLOCK) a 
 		on a.acct = s.acct 
 where p.status_pa = 'a'
 		and p.contract_type <> 'TM'
 		and a.acct_type = 'RV'
 group by p.project, p.project_desc, p.manager1, p.pm_id36
 having case when sum(s.eac_amount) = 0 then 100 else sum(s.act_amount) / sum(s.eac_amount) end >= .85

--table for employees to loop through
if object_id('tempdb..#EmployeesToEmail') is not null drop table #EmployeesToEmail;
create table #EmployeesToEmail
(employee varchar(10), empname varchar(100), email varchar(100))

--get list of employees we need to send emails to
;with cte as (
---Project Managers 
select	d.manager1 as employee, 
		dbo.nameflip(e.emp_name) as empname,
		RTRIM(e.em_id04) + '@youremail.com' as email
from @data d
	join pjemploy e (NOLOCK) on e.employee = d.manager1
UNION ALL
---QA Managers
select	d.pm_id36 as employee, 
		dbo.nameflip(e.emp_name) as empname,
		RTRIM(e.em_id04) + '@youremail.com' as email
from @data d
	join pjemploy e (NOLOCK) on e.employee = d.pm_id36
)
insert into #EmployeesToEmail 
select distinct employee, empname, email
from cte

--loop through each employee and send email with their respective data
declare c cursor for
select employee, empname, email 
from #EmployeesToEmail
where employee <> ''

open c;        
fetch next from c into @csrEmployee, @csrEmpName, @csrEmail;      
while @@FETCH_STATUS = 0 
begin

declare @emailBody NVARCHAR(MAX)
select @emailBody = 
			'<html>
				<head>
					<style>
						table, th, td {border: 1px solid #bbb; border-collapse:collapse}
						td, th {padding: 5px}
						th, h4 {font-size: 14px; text-transform:uppercase; font-family: helvetica, sans-serif; text-align:left;}
						td.r {text-align:right}
						thead {background-color: #e8e8e8}
					</style>
				</head>
				<body>
				<p>Dear ' + RTRIM(@csrEmpName) + ', <br><br> You are receiving this notification because you have used 85% or more of the budget on your project(s), as of the last timecard posting period. It is important to review your project progress to ensure you can complete the scope within the remaining budget amount available. If not, please start working with your client to obtain a change order/amendment now, or contact your supervisor to create a project recovery plan. </p>
				<h4>Projects approaching full budget</h4>
				<hr/>
				<table> 
					<thead>
						<tr>
							<th>Row</th>  
							<th>Project</th>  
							<th>Project Name</th> 
							<th>Budgeted Revenue</th> 
							<th>Revenue To Date</th> 
							<th>Percent Used</th>
						</tr>
					</thead>'    

--now populate the table rows using SQL results
--notice we are adding class 'r' to align some data to the right
declare @tablerows NVARCHAR(MAX);          
select @tablerows = convert(nvarchar(max),
					(select 
						(select rn as '*' for xml path('td'),type),
						(select RTRIM(project) as '*' for xml path('td'), type),
						(select RTRIM(project_desc) as '*' for xml path('td'), type),
						(select 'r' as '@class', budget as '*' for xml path('td'), type),
						(select 'r' as '@class', revenue as '*' for xml path('td'), type),
						(select 'r' as '@class', pcnt as '*' for xml path('td'), type)
				 from (select *, row_number() over (order by project) as 'rn' from @Data where manager1 = @csrEmployee OR pm_id36 = @csrEmployee ) x
				FOR XML PATH('tr') , elements))

select @emailBody = @emailBody + @tablerows + '</table><br><b>This is an automated email - please do not reply.</b></body></html>'

--send email
EXEC msdb.dbo.sp_send_dbmail @recipients = @csrEmail, @blind_copy_recipients=@bccEmail, @importance = 'high', @subject = @subject, @body = @emailBody, @body_format = 'HTML';

fetch next from c into @csrEmployee,@csrEmpName, @csrEmail;
END

close c; 
deallocate c;


An example of the email result is below:

Feel free to share...

This Post Has One Comment

  1. Hi Marc, it looks great
    Could you share how to build a account status or AR aged report and email results to sales person or customers, it would be very usefull. thanks in advance

Leave a Reply

Close Menu