Run sql job with different email addresses from sql table

Question

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

Answer 1

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;

Answer 2

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 @Tomas Podoba _format in sp_send_dbmail to send message body in HTML format.