1

I want to send same mail to multiple email-ids fetched from a table separately using sp_send_dbmail . If I use below query, the mail is being sent once to all user in "To" section. But my requirement is to send mail to them separately.

DECLARE @Receipientlist varchar(8000)  
SET @ReceipientList = STUFF((SELECT ';' + emailaddress FROM Your query here 
FOR XML PATH('')),1,1,'')


EXEC sp_send_dbmail  @profile_name= your email profile in db,  
@recipients= @Receipientlist,  
@subject='your subject here',  
@body='body message here'

Please suggest some solution.

Ayush Jain
  • 21
  • 2
  • Use a `Cursor` over the recipients and send email per recipients within the loop. – Nope Nov 30 '17 at 11:10
  • Hi @Fran, Thanks for the suggestion. Can you please give some example of query ? – Ayush Jain Nov 30 '17 at 11:16
  • See the linked duplicate which has the structure and enough sample code in the answer to get what you need done. Though you can also look up the official documentation google for `SQL Cursor` Top Result: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql – Nope Nov 30 '17 at 11:25
  • Also, you can look my answer. – lucky Nov 30 '17 at 11:29

1 Answers1

0

Define a cursor and try like this;

    declare @emailaddress nvarchar(250)

    DECLARE db_cursor CURSOR FOR  
    SELECT emailaddress FROM Your query here  

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @emailaddress   

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

            EXEC sp_send_dbmail  @profile_name= your email profile in db,  
            @recipients= @emailaddress,  
            @subject='your subject here',  
            @body='body message here' 

           FETCH NEXT FROM db_cursor INTO @emailaddress   
    END  

CLOSE db_cursor   
DEALLOCATE db_cursor
lucky
  • 12,734
  • 4
  • 24
  • 46