SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 iterate email recipients
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

512 Posts

Posted - 11/13/2012 :  06:41:44  Show Profile  Reply with Quote
Hi

I send out a email from sql server 2005 using....


EXECUTE msdb.dbo.sp_send_dbmail 
     @profile_name = 'Weekly Notification', 
     @recipients = 'xx@xx.xx', 
     @body = @MailBody,
	   @subject = Latest Update',
	   @body_format = 'HTML';



I would like to send this email to a bunch of recipients based on a query, how would that iteration look like?

magmo
Aged Yak Warrior

512 Posts

Posted - 11/13/2012 :  14:34:29  Show Profile  Reply with Quote
I currently try this approach

BEGIN

DECLARE @CurrentEmail nVarChar(50)

SELECT @CurrentEmail = CustomerEmail FROM dbo.UserLogin

-- Call procedure here that execute email send
--PRINT @CurrentEmail

END


But when I run this I only get one email adress. I want to execute the send_dbmail once for each recepient. Wht am I doing wrong?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 11/13/2012 :  14:37:03  Show Profile  Visit robvolk's Homepage  Reply with Quote
Are you sending the same email to multiple recipients? In that case, don't send multiple emails, but instead construct a list of email addresses and pass that as the @recipients parameter.
Go to Top of Page

magmo
Aged Yak Warrior

512 Posts

Posted - 11/13/2012 :  14:53:47  Show Profile  Reply with Quote
Yes, I want to send the same email, but will the each recepient see the other email adresses? I don't want that...
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 11/13/2012 :  17:41:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
Use @blind_copy_recipients instead of @recipients. You can construct the list like so:
DECLARE @blind nvarchar(max);
SET @blind=STUFF((
SELECT ';'+CustomerEmail FROM dbo.UserLogin 
FOR XML PATH('')
),1,1,N'')
Then send email to all of them with:
EXEC sp_send_dbmail @blind_copy_recipients=@blind, @subject='Subject', @body='blah blah blah'
You can modify the query to limit rows, etc., just keep the FOR XML PATH('') portion and enclose the whole query inside the STUFF() function. This will remove the leading semicolon from the generated list.
Go to Top of Page

magmo
Aged Yak Warrior

512 Posts

Posted - 11/14/2012 :  01:35:57  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

Use @blind_copy_recipients instead of @recipients. You can construct the list like so:
DECLARE @blind nvarchar(max);
SET @blind=STUFF((
SELECT ';'+CustomerEmail FROM dbo.UserLogin 
FOR XML PATH('')
),1,1,N'')
Then send email to all of them with:
EXEC sp_send_dbmail @blind_copy_recipients=@blind, @subject='Subject', @body='blah blah blah'
You can modify the query to limit rows, etc., just keep the FOR XML PATH('') portion and enclose the whole query inside the STUFF() function. This will remove the leading semicolon from the generated list.



Excellent, Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000