How to send an email to multiple recipients ?

email, sql-server, stored-procedures

Solution

You need to add `;` (semicolon) between e-mail addresses using string concatenation:

DECLARE @copy_to varchar(max)= @Mail2+';'+@Mail3

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'mail', 
    @recipients = @Mail1,
    @copy_recipients = @copy_to, 
    @body =@body ,
    @subject =@subject

You can read MSDN article here

[ @recipients= ] 'recipients'

Is a semicolon-delimited list of e-mail addresses to send the message to. The recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

[ @copy_recipients= ] 'copy_recipients'

Is a semicolon-delimited list of e-mail addresses to carbon copy the message to. The copy recipients list is of type varchar(max). Although this parameter is optional, at least one of @recipients, @copy_recipients, or @blind_copy_recipients must be specified, or sp_send_dbmail returns an error.

Problem

I have a stored procedure which sent emails to few recipients. In this I want to send to two differet recipients using @copy_recipients. But I get a syntax error. How to make this work? stored procedure code ``` EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mail', @recipients = @Mail1, @copy_recipients = @Mail2;@Mail3, @body =@body , @subject =@subject ```

Original source