sp_send_dbmail (T-SQL)

Question

I'm fairly new to this type of thing I need to query a table and use the results to populate a CC list for an email message. I can't find anything that shows an example of this specifically. I'm using a Job in SQL server to accomplish this task. Sending to one recipient and one CC recipient works great but I need to send multiple CCs. Any advice would eb greatly appreciated.

Here's my code that sends an email to one recipient:

declare     @chvFirstName nvarchar(100),
@chvLastName nvarchar(100),
@chvEArea nvarchar(5),
@chvAppDate nvarchar(20),
@chvExpDate nvarchar(20),
@chvEmail varchar(50),
@chvEmailMessage varchar(800)

declare @CrsrVar Cursor

Set @CrsrVar = Cursor For
Select App.dbo.CommitteeAPC.AppointDate, App.dbo.CommitteeAPC.ExpiryDate, App.dbo.CommitteeAPC.ElectoralArea, App.dbo.CommitteeMemberContact.FirstName,  App.dbo.CommitteeMemberContact.LastName, App.dbo.CommitteeAPC.StaffEmail
From RDOS_App.dbo.CommitteeAPC INNER JOIN App.dbo.CommitteeMemberContact ON App.dbo.CommitteeAPC.MemberID = App.dbo.CommitteeMemberContact.MemberID
Where CurrentDate > NotifDt

Open @CrsrVar

 Fetch Next From @CrsrVar
Into @chvAppDate, @chvExpDate, @chvEArea, @chvFirstName, @chvLastName, @chvEmail

While(@@FETCH_STATUS = 0)

Begin

set @chvEmailMessage = 'DO NOT REPLY TO THIS MESSAGE! This is an automatic e-mail notification ' +
    'message sent to you from the Committees Database.' +
    CHAR(13) + CHAR(10)+
    CHAR(13) + CHAR(10)+
    'The following APC membership will expire in approximately 30 days.' +
    CHAR(13) + CHAR(10)+
    CHAR(13) + CHAR(10)+
    'Name: ' + @chvFirstName + ' ' + @chvLastName +
    CHAR(13) + CHAR(10)+
    CHAR(13) + CHAR(10)+
    'Area: ' + @chvEArea + ' Advisory Planning Commission' +
    CHAR(13) + CHAR(10)+
    'Date Appointed: ' + @chvAppDate +
    CHAR(13) + CHAR(10)+
    'Date of Expiry: ' + @chvExpDate 

--Use Master

Exec msdb.dbo.sp_send_dbmail
@profile_name = 'App Database Mail Profile',
@recipients = @chvEmail,
@subject = 'An APC Membership will expire within 30 days',
@body = @chvEmailMessage

Fetch Next From @CrsrVar
Into @chvAppDate, @chvExpDate, @chvEArea, @chvFirstName, @chvLastName, @chvEmail

End

Close @CrsrVar
Deallocate @CrsrVar

Answer 1

The answer depends on :

1. If You want to send THE SAME letter to all the people - You can see Answer 1

2. If You use to send SPECIFIC letter ( @chvAppDate, @chvExpDate, @chvEArea for example can change from one user to other ) see my answer :)

   declare @chvFirstName nvarchar(100),
   @chvLastName nvarchar(100),
   @chvEArea nvarchar(5),
   @chvAppDate nvarchar(20),
   @chvExpDate nvarchar(20),
   @chvEmail varchar(256),
   @chvEmailMessage nvarchar(800)
   
   declare CrsrVar Cursor FAST_FORWARD For
   Select APC.AppointDate, APC.ExpiryDate, APC.ElectoralArea, CMC.FirstName, CMC.LastName, APC.StaffEmail
   From RDOS_App.dbo.CommitteeAPC APC
   JOIN App.dbo.CommitteeMemberContact CMC ON APC.MemberID = CMC.MemberID
   Where CurrentDate > NotifDt
   
   Open CrsrVar
   
   Fetch Next From CrsrVar
   Into @chvAppDate, @chvExpDate, @chvEArea, @chvFirstName, @chvLastName, @chvEmail
   
   While(@@FETCH_STATUS = 0)
   
   Begin
   select @chvEmailMessage = 'DO NOT REPLY TO THIS MESSAGE! '+
   'This is an automatic e-mail notification message sent to you from the Committees Database.' + CHAR(10) + CHAR(10)+
   'The following APC membership will expire in approximately 30 days.' + CHAR(10) + CHAR(10)+
   'Name: ' + COALESCE(@chvFirstName+' ','') + isnull(@chvLastName,'') + CHAR(10) + CHAR(10)+
   COALESCE('Area: ' + @chvEArea + ' Advisory Planning Commission' + CHAR(10),'') + 
   'Date Appointed: ' + isnull(@chvAppDate,'') + CHAR(10)+
   'Date of Expiry: ' + isnull(@chvExpDate,'') 
   
   --Use Master
   
   Exec msdb.dbo.sp_send_dbmail
                   @profile_name = 'App Database Mail Profile',
                   @recipients = @chvEmail,
                   @subject = 'An APC Membership will expire within 30 days',
                   @body = @chvEmailMessage
   
   Fetch Next From CrsrVar
   Into @chvAppDate, @chvExpDate, @chvEArea, @chvFirstName, @chvLastName, @chvEmail
   
   End
   
   Close CrsrVar
   Deallocate CrsrVar
   

You are welcome for any question

Answer 2

Declare @cc_emailaddress set @cc_emailaddress = 'a@abc.com; b@abc.com'

Answer 3

You can concatenate multiple recipients in @chvEmail. Separate them with semi-colons.

SET @chvEmail = 'user1@domain1.com; user2@domain2.com'

sp_send_dbmail just passes the @recipients string to MX as is, with no validation.

If you have a table of email addresses, you can concatenate them all into one string with:

DECLARE @email_to varchar(max);
SELECT @email_to = COALESCE(@email_to + ';', '') + [email] FROM [email_addresses]