philthompson1
asked on
sp_send_dbmail with query specific to each recipient
I have the script working to send an email to each recipient. I need to include data that is specific to each recipient though.
How do I do this? I'd like to include it in the body or subject or anywhere at this point. It's not a lot of data, just one field in the table that is related to that person.
The 'REF' in the script below is the field.
*******************
declare Maillist cursor
for select EMAIL FROM dbo.test
OPEN Maillist
FETCH FROM Maillist into @emailAddress
WHILE NOT (@@fetch_status=-1)
BEGIN
begin
set nocount on
USE [Goldmine] SELECT EMAIL, REF FROM dbo.test
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
*************
How do I do this? I'd like to include it in the body or subject or anywhere at this point. It's not a lot of data, just one field in the table that is related to that person.
The 'REF' in the script below is the field.
*******************
declare Maillist cursor
for select EMAIL FROM dbo.test
OPEN Maillist
FETCH FROM Maillist into @emailAddress
WHILE NOT (@@fetch_status=-1)
BEGIN
begin
set nocount on
USE [Goldmine] SELECT EMAIL, REF FROM dbo.test
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
*************
You can add the value in several places in the e-mail. Use this page to get the right parameter for sp_db_sendmail http://msdn.microsoft.com/en-us/library/ms190307.aspx
ASKER
Yes, I've seen that many times. I'm not sure how to show the specific data per recipient. There might be up to 10 emails each having a different Quote.
Example:
Dear Mr. Jones,
Your "quote # 12345" is about to expire in 15 days.
Thank you,
XXXX
Example:
Dear Mr. Jones,
Your "quote # 12345" is about to expire in 15 days.
Thank you,
XXXX
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did get it to work. All I had to do is declare and add the @quoteref column after the @emailaddress
--FETCH FROM Maillist into @emailAddress, @quoteref
In both FETCH lines.
Took me a while but I figured it out.
Thanks..!
--FETCH FROM Maillist into @emailAddress, @quoteref
In both FETCH lines.
Took me a while but I figured it out.
Thanks..!
great!
http://msdn.microsoft.com/en-us/library/ms190307.aspx