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('firstname.lastname@example.org')
insert @table (Emp_Email) values('email@example.com')
insert @table (Emp_Email) values('firstname.lastname@example.org')
insert @table (Emp_Email) values('email@example.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))
set @Recepient_Email =(select top(1) Emp_Email from @table where ID=@count)
@subject = 'This is subject of test Email'
@body = 'This is body of test Email',
@body_format = 'HTML'
set @count =@count +1
For detailed information on setting up profile etc... in Database Mail please see my article here