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.
--
Regards,
Bhuvana

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

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 spreadsheet 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=instan cename;Database=dbname;UID=username;PWD=userpasswo rd"
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 (AT) mycompany (DOT) com"
oMail.To = "recipient1 (AT) company1 (DOT) com,recipient2 (AT) company2 (DOT) com"
oMail.Cc = "recipient3 (AT) company3 (DOT) com"
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\spreadsheet.xls")
oMail.Send
Set oMail = Nothing
End If
End If
Main = DTSTaskExecResult_Success
End Function


Bhuvaneswari Ramamurthi wrote:

On Jul 15, 7:29 am, Bhuvaneswari Ramamurthi
<BhuvaneswariRamamur... (AT) discussions (DOT) microsoft.com> wrote:
Could also be that your executing stproc with a security context to
which no mail account exists?
I mean, if your executing this stproc from sa... or from any sql
server login i believe that it would never work.
If so try executing with your windows account, log in to... let's say
Query analyzer and try.

Whatever the cause... Ed's script and suggestions could make your day
Bye
M.