Multiple Emails With One DTS package

Question

Multiple Emails With One DTS package

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

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

DavidP-340734 Right there with Babe Points: 737 More actions · Answer 1

We have been using a simple e-mail program called "Blat" that uses SMTP. You can create scripts via the dts package to select the data into a file for the body of the e-mail, a script to send to one or multiple recipients and the csv file. The command syntax is simple:

C:\Email\blat C:\Email\EmailBody.txt -subject "Your Subject Line" -to email@email.com -attach "C:\Email\YourFile.csv"

If you are running SMTP services on your server already you can use sql mail or execute sp_send_cdosysmail or use the following SQL:

DECLARE @CDO int, @OLEResult int, @Out int

EXECUTE @OLEResult = sp_OACreate 'CDONTS.NewMail', @CDO OUT

IF @OLEResult <> 0 PRINT 'CDONTS.NewMail'

EXECUTE @OLEResult = sp_OAMethod @CDO,

'Send',

Null,

'From@email.com',

'to@mail.com',

'e-mail test...',

'CDO database e-mail test.',

1

IF @OLEResult <> 0 PRINT 'error: ' + CONVERT(VARCHAR(64),@OLEResult)

EXECUTE @OLEResult = sp_OADestroy @CDO

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 2

Hi, you might also like to try the sendmail stored procedure, xp_sendmail, details in BOL.

All the best,

Duncan

John McC Hall of Fame Points: 3410 More actions · Answer 3

Or, if you can't get that sorted out (I can never get it to work on our system)

http://sqldev.net/xp/xpsmtp.htm

Cheers

John

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: