Q: sp_send_dbmail (T-SQL) 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


A: 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]


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

A: The answer depends on :


*

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

*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
