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

You need to pass a varchar value with ; as a delimiter. what does @chvEmail contains? All you need to do is parse it to the correct format. — Yosi Dahari
– Yosi Dahari, Commented Nov 26, 2013 at 20:44

Anon · Accepted Answer · 2013-11-26 20:45:44Z

2

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

SET @chvEmail = '[email protected]; [email protected]'

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]

answered Nov 26, 2013 at 20:45

Anon

10.9k1 gold badge32 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2062108 · Accepted Answer · 2013-11-26 22:17:02Z

0

Declare @cc_emailaddress set @cc_emailaddress = '[email protected]; [email protected]'

answered Nov 26, 2013 at 22:17

user2062108

Comments

DimaSUN · Accepted Answer · 2014-01-10 16:25:22Z

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

Collectives™ on Stack Overflow

sp_send_dbmail (T-SQL)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related