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

DECLARE @blind nvarchar(max);
SET @blind=STUFF((
SELECT ';'+CustomerEmail FROM dbo.UserLogin 
FOR XML PATH('')
),1,1,N'')

EXEC sp_send_dbmail @blind_copy_recipients=@blind, @subject='Subject', @body='blah blah blah'

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.

---