question

DBA9 avatar image
0 Votes"
DBA9 asked Criszhan-msft edited

Run sql job with different email addresses from sql table

Hello,
I have a sql table with recipient email addresses along with unique id, names etc.. How would I run a sql job to send HTML formatted email (includes table data) to different recipients from a table.

It should start from row 1 until it reaches the last row.

Please let me know how to execute sql job with different emails.

Thank you

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
1 Vote"
OlafHelper-2800 answered OlafHelper-2800 commented

You can use a cursor to process all email addresses, create the email content and send it.
Little example:

 DECLARE @email varchar(200);
 DECLARE email CURSOR FOR  
     SELECT emailAddress
     FROM yourTable;
    
 OPEN email;
 FETCH NEXT FROM email INTO @email;
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
     PRINT @email
     -- EXEC sp_send_dbmail ....
     FETCH NEXT FROM email INTO @email;
 END;
    
 CLOSE email;  
 DEALLOCATE email;
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your prompt reply. I will try that. Do I use @recipient=@email in Exec sp-send_dbmail?

0 Votes 0 ·

Do I use @recipient=@email in Exec sp-send_dbmail?

Yes, that's the right syntax & value.



1 Vote 1 ·
Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered Criszhan-msft edited

Hi,

Through the combination of cursor, variables and the stored procedure sp_send_dbmail to traverse the mail address in the table and send mails to them in turn, Olaf gives a good example.

Specify the argument @body_format in sp_send_dbmail to send message body in HTML format.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.