Post reply

sql 2005 dbmail send email via query to multiple recipients

  • February 3, 2010 at 11:39 am

    #156642

    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'

  • February 3, 2010 at 12:12 pm

    #1113098

    Your code will only generate a single email, and you cannot guarantee which one it will be. You need to execute a msdb.dbo.sp_send_dbmail call for each recipient, because each recipient can have a different body.

    However, you don't have to use a cursor, but that is one way, you do have to use a loop though. Whether it be a while loop or a cursor.

    I'm wondering if you understand what:SELECT @email = email, @text=mytext FROM @t is actually doing?

    CEWII

  • February 3, 2010 at 12:29 pm

    #1113114

    Thanks so much Elliott - will proceed. Was hoping a bigger and brighter brain than mine would have a slicker way.... Cheers.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply