How Can I Send Dynamic Emails from SQL Server to Recipients Stored in a Table?

Sending Emails from SQL Server with Dynamic Recipients

Using T-SQL is an effective method for sending emails from SQL Server. When email addresses are stored in a table, it's possible to loop through the table and automate the email-sending process.

Prerequisites:

To configure Database Mail, follow these steps:

• Create a profile and account using the Configure Database Mail Wizard.

• Enable Database Mail XPs:

  sp_CONFIGURE 'Database Mail XPs', 1
  GO
  RECONFIGURE
  GO

Step 1: Building the Query

DECLARE @email_id NVARCHAR(450), @id BIGINT, @max_id BIGINT, @query NVARCHAR(1000)

SELECT @id=MIN(id), @max_id=MAX(id) FROM [email_adresses]

WHILE @id<=@max_id
BEGIN
    SELECT @email_id=email_id 
    FROM [email_adresses]

    set @query='sp_send_dbmail @profile_name=''yourprofilename'',
                        @recipients='''+@email_id+''',
                        @subject=''Test message'',
                        @body=''This is the body of the test message.
                        Congrates Database Mail Received By you Successfully.'''

    EXEC @query
    SELECT @id=MIN(id) FROM [email_adresses] where id>@id

END

Step 2: Executing the Query

Execute the built query to send emails to each email address in the table.

Additional Notes:

The provided query sends a predefined email message with a static subject and body. For customized messages, modify the @subject and @body parameters.

Also, remember to replace 'yourprofilename' with the name of your configured profile.

The above is the detailed content of How Can I Send Dynamic Emails from SQL Server to Recipients Stored in a Table?. For more information, please follow other related articles on the PHP Chinese website!