Email to Mutiple users with different subject i.e Subject variable

Question

Post reply

Email to Mutiple users with different subject i.e Subject variable

best_yunus

SSC-Addicted

Points: 409
More actions
July 29, 2013 at 5:36 am

#281136

Hi,
I stuck in a weekly report which i have to send to every user for their access.
i want to send it through Database Mail.
I have a table like this
REQID firstname lastname accessenddate Mail ID
1234 Yunus Parvez 8-Aug-13 best_yunus@yahoo.com
12345 ABC xyz 9-Aug-13 best_yunus@live.com
Now i want to send mail to these user with edited subject line and body.
for ex.
subject line: RequestID-ReqID accessenddate name
body of mail:
Hi <first name>,
We have received a notification informing your access is due to expire on <accessenddate>
Kindly use this as the subject line for renewal access request: Renew: <Old access - Request#ReqID>
Please help to get rid off this report in quick way.
Response appreciated.
Regards,
Yunus

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

for individualized emails, i think you'll need two things:

a cursor to go thru the list of email recipients, and which builds the custom subject/body

sp_send_dbmail to actually send the email.

do you already have Database Mail set up on your server? does it work?

here's a basic example for you: note it does nto ahndle NULLS gracefully: if you ahve nulls in any of the fields, your emails will be blank.

--sample data:

SELECT 1234 AS [REQID],'Yunus' AS [firstname],'Parvez' AS [lastname],CONVERT(datetime,'8-Aug-13') AS [accessenddate] ,'best_yunus@yahoo.com' AS [Mail ID]

INTO #MySampleData UNION ALL

SELECT 12345,'ABC','xyz','9-Aug-13','best_yunus@live.com'

declare

@reqid int,

@firstname varchar(50),

@lastname varchar(100),

@accessdate datetime,

@email varchar(100),

@mysubject varchar(100),

@htmlbody varchar(max)

declare c1 cursor for

select

reqid,

firstname,

lastname,

accessenddate,

[Mail ID]

from #MySampleData

open c1

fetch next from c1 into @reqid,@firstname,@lastname,@accessdate,@email

While @@fetch_status <> -1

begin

select @mysubject = REPLACE(

REPLACE(

REPLACE('RequestID-<ReqID> <accessenddate> <name>',

'<ReqID>',CONVERT(varchar,@reqid)),'<accessenddate>',

CONVERT(varchar,@accessdate,101)),

'<name>', @firstname + ' ' + @lastname)

select @htmlbody = REPLACE('Hi <first name>,

We have received a notification informing your access is due to expire on <accessenddate>

Kindly use this as the subject line for renewal access request: Renew: <Old access - Request#ReqID>

Please help to get rid off this report in quick way.

Response appreciated.

Regards,

Yunus','<first name>',@firstname)

EXEC msdb.dbo.sp_send_dbmail

--@profile_name='MyProfileName for DBMail',

@recipients=@email,

@subject = @mysubject,

@body = @htmlbody,

@body_format = 'HTML'

--@body_format = 'TEXT'

fetch next from c1 into @reqid,@firstname,@lastname,@accessdate,@email

end

close c1

deallocate c1

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

best_yunus SSC-Addicted Points: 409 More actions · Answer 2

Thanks a ton man.

I modify this script according to me and it worked smoothely..

🙂

Superb..

Lowell SSC Guru Points: 323518 More actions · Answer 3

best_yunus (7/29/2013)
Thanks a ton man.
I modify this script according to me and it worked smoothely..
🙂
Superb..

glad i could help; it's so much easier to take an example and adapt!

enjoy!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

best_yunus SSC-Addicted Points: 409 More actions · Answer 4

Hi,

I modified like this:

SELECT Request_ID,firstname,Subject,End_Date,Mail_ID

INTO #Data from test

declare

@Request_ID varchar(20),

@firstname varchar(50),

@End_Date varchar(10),

@Mail_ID varchar(100),

@Subject nvarchar(max),

@htmlbody nvarchar(max)

declare c1 cursor for

select Request_ID,

firstname,

End_Date,

Mail_ID ,

Subject

from #Data

open c1

fetch next from c1 into

@Request_ID,

@firstname,

@End_Date,

@Mail_ID,

@Subject

While @@fetch_status <> -1

begin

select @Subject = 'RE: [Request ##'+@Request_ID+'##]:('+@Subject+')'

select @htmlbody = '<html><body><p>

Hi'+' '+@firstname+',

We have received a notification informing your access is due to expire on '+ @End_Date+'.

Renew: ##'+@Request_ID+'## Access Request for Kronite – '+@firstname+'</b>

Thanks and Regards,

</p></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@profile_name='Mail Profie Name',

@recipients=@Mail_ID,

--@copy_recipients ='Mail Id you want to keep in CC',

@subject = @Subject,

@body = @htmlbody,

@body_format = 'HTML'

--@body_format = 'TEXT'

fetch next from c1 into

@Request_ID,

@firstname,

@End_Date,

@Mail_ID,

@Subject

end

close c1 deallocate c1

use tempdb

Drop table #Data

GO