Re: Sending Email using ActiveX Script task

When a message is bounced, the receiving server will send the bounce message to whatever address is in the From: field.

I use an ActiveX script task to send mail so I can change the From: address as needed. I want the bounce messages to go to different addresses, depending on what the package is doing (e.g., Sales, Finance, myself, etc)

Here is the code I use. In this sample, I check to see if there are any records in a certain table. If there are, that means a spread*** created earlier in the DTS package has valid data in it and I can safely send it to the recipients.

Note: CDO comes with Server 2003. Server 2000 used CDONTS, which is very similar but uses a slightly different syntax. Microsoft support site has more info.

HTH,
Ed

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

set oCnn=CREATEOBJECT("ADODB.Connection")
set rs=CreateOBJECT("ADODB.Recordset")
strConn = "Provider=MSDASQL;Driver={SQLServer};Server=instancename;Database=dbname;UID=username;PWD=userpassword"
oCnn.Open strConn
If oCnn.State = 1 Then strSQl = "select * from targettable"
rs.Open strSQl, oCnn
If Not rs.EOF Then
Set oMail = CreateObject("CDO.Message")
oMail.From ="sender@xxxxxxxxxxxxx"
oMail.To = "recipient1@xxxxxxxxxxxx,recipient2@xxxxxxxxxxxx"
oMail.Cc = "recipient3@xxxxxxxxxxxx"
oMail.Subject = "Subject of this message"
oMail.TextBody = "First line of body text." & chr(13) & chr(10) & chr(13) & chr(10)
oMail.AddAttachment ("\\servername\drive:\foldername\spread***.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:
In my SQL Server 2000 DTS package i need to send e-mails to the list of recipient. I am using xp_smtp_sendmail stored procedure.

When i send email to unavailable recipient i need to bounce back the e-mail to the sender. But when i use the stored procedure the mail was not bounce back.

Is it possible to bounce back the e-mail if the recipient is unavailable.

Pls give me a suggestion for this issue.
.