SQL 2005 DBMAIL query to multiple recipients

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 DBMAIL query to multiple recipients

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2010-02-03 : 13:24:46

I'm wanting to query a database and have it sent to multiple recipients, and part of the body of the email can be different for each recipient. (My dbmail with the correct profile seems to be working fine.) When I do the following, only the last email is received (text2). Is there a way to do this sending to multiple recipients with customized body text without a cursor?

DECLARE @t TABLE(id INT IDENTITY, email VARCHAR(50), myText varchar(10))
INSERT INTO @t (email,mytext)
SELECT 'Email_1@email.net','text1' union all SELECT 'Email_2@email.net','text2'

DECLARE @email VARCHAR(50), @text varchar(10)
SELECT @email = email, @text=mytext FROM @t 

EXEC msdb.dbo.sp_send_dbmail
@Profile_name = 'MyProfileName',
@recipients = @email,
@body = @text,
@subject = 'Test Email'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-03 : 13:26:29

you need to do it in a loop if you need separate messages to be send for each receipient