Send Email to Multiple Recipient using msdb.dbo.sp_send_dbmail

While using msdb.dbo.sp_send_dbmail for sending email, we can send same email to multiple email-addresses by separating every single email by a semicolon(;). But If we have hundreds of individuals who needs to be sent the same email address, typing every single email can be time taking. Here is an easier way by which we can accomplish the same.

--declare a temporary table and populate the email address in it. Note that this can also be done by retrieving email address from a --table where email adress of recepient are stored like Employee table

 

declare @table as table(Emp_Email nvarchar(100), ID int identity(1,1))

--populate the above table

insert @table (Emp_Email) values('xxx@devtechie.com')

insert @table (Emp_Email) values('abc@devtechie.com')

insert @table (Emp_Email) values('yyy@devtechie.com')

insert @table (Emp_Email) values('1234@devtechie.com')

 

--declare a parameter for storing count of records in table

Declare @count as int

set @count =1 --initialize the count parameter

 

--declare a parameter for storing recepients email address

Declare @Recepient_Email as varchar(100)

--Now we will loop through the email address and email will be sent one at a time to all the recepients

while (@count <=(select COUNT(*) from @table))

        begin

        set @Recepient_Email =(select top(1) Emp_Email from @table where ID=@count)

        EXEC msdb.dbo.sp_send_dbmail

            @profile_name='Profile1',

            @recipients=@Recepient_Email,            

            @subject = 'This is subject of test Email'

            @body = 'This is body of test Email',

            @body_format = 'HTML'

            set @count =@count +1

            END

 

For detailed information on setting up profile etc... in Database Mail please see my article here