TSQL: Sample Script that Sends an Email

DECLARE @toRecipients VARCHAR(1000),
 @ccRecipients VARCHAR(1000),
 @tableHTML  NVARCHAR(MAX),
 @emailSubject VARCHAR(100),
 @ProfileName VARCHAR(100)
SET @toRecipients = 'jenny.juanillas@test.com'
SET @ccRecipients = a@test.com'
SET @emailSubject = 'SQLServer Job: Duplicate Email Address(' + cast(cast(getdate() as date) as varchar) + ')'
set @ProfileName = 'Prod Email'

select Email, CountActive, CountInActive
into #temp
from
 (
 select distinct email,
  (select count(*) from table1 where email = au.email and isexistingclient = 'Y') 'CountActive',
  (select count(*) from table1 where email = au.email and isexistingclient = 'N') 'CountInActive'
 from table1 au
 ) x
where CountActive <> 0
and CountInActive <> 0
)
order by email

if exists(SELECT 1  
              FROM #temp)
   
begin   
 SET @tableHTML =
  N'<H4 style="font-family:Calibri">Duplicate Email Addresses</H1>' +
  N'<table border="1" style="font-family:Calibri,Arial;font-size:12px">' +
  N'<tr style="background-color:#C0C0C0;;font-size:12px"><th>Email Address</th><th>Count of Active Accounts</th>' +
  N'<th>Count of Inactive Accounts</th>' +
  N'</tr>' +
  CAST ( ( SELECT td = Email,       '',
      td = CountActive, '',
      td = CountInActive, ''
      FROM #temp
     FOR XML PATH('tr'), TYPE
  ) AS NVARCHAR(MAX) ) +
  N'</table> <p> Kindly fix the user accounts.</p>' ;
 EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @ProfileName,
  @recipients=@toRecipients,
  @copy_recipients = @ccRecipients,
  @subject = @emailSubject,
  @body = @tableHTML,
  @body_format = 'HTML' ;
end