asked on

Enable a script with a loop

Thank you all in advance for your help!

I need some help enabling the following script with loop capabilities.

In other words, the script should cycle through the list of recipients sending an email to each.

Thanks again!

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @email NVARchar(4000)
Declare @count NVARCHAR(MAX)
Declare @lastname VARCHAR(MAX)

Set @count =
(Select count(email)
FROM vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > ' 2018-4-1'
and email = 'ana@aol.com')

Set @lastname =
(
Select distinct (lastname) from vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > ' 2018-4-1'
and email = 'ana@aol.com'
)

SET @xml = CAST(( SELECT email AS 'td','', convert (varchar, Created, 101) as 'td','', LOC AS 'td'
FROM
vw_Porivder_GenbaWalk_Activity_PM_SS_6_18_18
Where Created > '2018-4-1'
and email = 'ana@aol.com'
order by created desc

FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body><H3>'+ 'Dr.' + @lastname + ','
+
' Currently ' + @count + ' genba walks have been registered for you this quarter.'
+
' '+ 'Please let us know if you have any questions regarding this count.'

+ ' '
+ 'Thank you!'
+

'</H3>
<table border = 1>
<tr>
<th> email </th> <th> Created </th> <th> Location </th></tr>'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ACE DB Admin', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'ana@aol.com', -- replace with your email address
@subject = 'Leader Standard Work/Genba Walk Update';

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial