sp_send_dbmail to variable recipients

Question

sp_send_dbmail to variable recipients

farhana.sethi

Mr or Mrs. 500

Points: 556
More actions
August 24, 2014 at 2:37 am

#290065

Below is my code which i want to convert to send to variable email recipients. Which can be find from same table.
use
db
go
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H2>Dear User(USD)</H2>' +
N'<table border="1">' +
N'<tr><th>Column1</th><th>Column2</th>' +
CAST ( ( SELECT td = [Column1],'',
td = [Column2], ''
FROM dbo.table1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
+N'
Regards,</br>' +
+ N'XX'
EXEC msdb.dbo.sp_send_dbmail @recipients='user@mail.com',
@subject = 'Subject Line',
@profile_name = 'MyProfile',
@body = @tableHTML,
@body_format = 'HTML' ;

Viewing 8 posts - 1 through 7 (of 7 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

This is one of the rare scenarios where you'll have to use a loop and iterate through every distinct user.

Assign the mail addresse to a separate variable (e.g. @userMail) and change @recipients='user@mail.com' to @recipients=@userMail

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

farhana.sethi Mr or Mrs. 500 Points: 556 More actions · Answer 2

It worked for me. I sorted out by myself.

But thanks for your reply:-)

LutzM SSC Guru Points: 107049 More actions · Answer 3

farhana.sethi (8/24/2014)
It worked for me. I sorted out by myself. ...

Care to share your solution so others may benefit?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

farhana.sethi Mr or Mrs. 500 Points: 556 More actions · Answer 4

It was too early to say it is working!!

When I add below to my code:

DECLARE @recep varchar(500)

SET

@recep= (SELECT email

FROM

dbo.mytable

)

Now i can send email to variable emails. But there is a constraint.

It send all table rows to all email IDs.

I want to bind row to the particular eMail ID and send only the specific data (row). I'm not sure how i can make it work???

LutzM SSC Guru Points: 107049 More actions · Answer 5

see my previous reply using a loop.

as a side note: it would be very helpful if you wouldn't flood all forums with your very same question (= numerous duplicate posts). Thank you.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

farhana.sethi Mr or Mrs. 500 Points: 556 More actions · Answer 6

i realized i posted my question to sql 2005 tthats why i posted here as well. Its not a floooood:hehe:

farhana.sethi Mr or Mrs. 500 Points: 556 More actions · Answer 7

i dont get what u have replied

This is one of the rare scenarios where you'll have to use a loop and iterate through every distinct user.

Assign the mail addresse to a separate variable (e.g. @userMail) and change @recipients='user@mail.com' to @recipients=@userMail

Can you elaborate?

thanks