Sending mail to many per selected rows

Question

Sending mail to many per selected rows

tendo

SSC Veteran

Points: 241
More actions
October 4, 2004 at 9:11 am

#60113

I have a bunch of rows in a table, each with columns whose data forms various parts of an email that needs to get to as many as 50k people. How do I individually select the email address (& other columns to include in the message) and then send it out? The trigger below isn't workin out!...
======================================
ALTER TRIGGER TgwArticles
ON dbo.Articles
FOR INSERT
AS
IF UPDATE   (Q)
SET NOCOUNT ON
SELECT     Solution AS XSolution, Series AS XSeries, Catalogue AS XCatalogue, ItemID AS XItemID, SubItemID AS XSubItemID, ArticleID AS XArticleID, P AS XP,
                      Q AS XQ, AccessEmail AS XAccessEmail
FROM         Articles
RETURN
BEGIN
EXEC MASTER.dbo.xp_sendmail
  @recipients = XAccessEmail,
  @message = 'This Article-ID is XArticleID. Please get XQ of XSolution, XSeries, XCatalogue, at XP As per your agreement.',
  @subject = 'Orders'
END
=====================================
Thx in advance

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

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

Andrew Llewellyn SSC Enthusiast Points: 157 More actions · Answer 1

I am assuming that you want to send an email to the person in the row just inserted and not to all people in the table.

First you need to query the inserted table (this is a special table available only in triggers) You will need to declare variables for the columns you read from the table.

eg.

DECLARE @XAccessEmail varchar(255),

@Msg varchar(2000),

SELECT @XAccessEmail = AccessEmail ,

@Msg = @message = 'This Article-ID is ' + convert(varchar(10),ArticleID) +

'. Please get ' + Q +

' of ' + Solution + ', ' + Series + ', ' + Catalogue, at ' +

P + ' As per your agreement.'

FROM Inserted

EXEC MASTER.dbo.xp_sendmail

@recipients = @XAccessEmail,

@message = @Msg,

@subject = 'Orders'

The above should be a guide as to how you might make it work.

NB. If more than one row is inserted at a time the trigger only fires once so you will need to step through the inserted table with a cursor.

RGDS

Andy Llewellyn

hildevan Say Hey Kid Points: 691 More actions · Answer 2

HI

One tip !!!!

Try using cdonts.

Cdonts work better than xp_sendmail and you can improve a format that e-mail send it.

Hildevan O Bezerra

Andrew Llewellyn SSC Enthusiast Points: 157 More actions · Answer 3

Andrew Llewellyn

SSC Enthusiast

Points: 157

October 6, 2004 at 1:45 am

#525542

What is Cdonts and how would you use it?

Rgds

Andy

ade-165491 SSC Enthusiast Points: 185 More actions · Answer 4

CDONTS is very usefull and easy to use the following link gives a comprehensive guide to it.

http://support.microsoft.com/default.aspx?scid=kb;en-us;312839&sd=tech

regards, ade

Hari Takella-174750 SSC Enthusiast Points: 191 More actions · Answer 5

Hi ,

Is there a way that i can use CDONTS in widows 2000 to send attachements in the mail.

Thanks

Hari\

hildevan Say Hey Kid Points: 691 More actions · Answer 6

Hi

You can use two attach methods : simple text ou HTML format.

Yoy may decide what format you´ll are use in parameters passed do cdonts.

Please, check documentation available in microsoft or in the alltheweb.com

[]s

Hildevan O Bezerra