CDOSYS Send Email - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

CDOSYS Send Email

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-08 : 13:58:04

This was a P A I N to get working. Maybe someone else here is sending email and could use it.

A UNICODE Send Mail using CDOSYS with ReadReceipt and Importance...

Sub SendMail  (sFromAddress, sToAddress, sCcAddress, sBccAddress,  sSubject, sBody, boolReadReceipt, intImportance ) 
'	on error resume next
	Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
	Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
	dim cdoMessage, cdoConfiguration

	Set cdoConfiguration = Server.CreateObject ("CDO.Configuration")
	' Outgoing SMTP server
	With cdoConfiguration
		.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
		.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
		.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
		.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
		.Fields.Update
	End With
	

	Set cdoMessage = CreateObject("CDO.Message")
    With cdoMessage  
		' Update the CDOSYS Configuration
		SET .Configuration = cdoConfiguration 
		.BodyPart.charset = "unicode-1-1-utf-8" 

		IF boolReadReceipt Then
			.Fields(cdoDispositionNotificationTo)	= sFromAddress  
			.Fields(cdoReturnReceiptTo)		= sFromAddress  
		End If
	
		' Set the Importance: 0:Low, 1:Normal, 2:High
		.Fields("urn:schemas:httpmail:importance").Value	= intImportance
		.Fields.Update  
        .From		= sFromAddress
	.ReplyTo	= sFromAddress
        .To		= sToAddress
	.Cc		= sCcAddress
	.Bcc		= sBccAddress
        .Subject	= sSubject
	.Textbody	= sBody
        .Send  
    End With  
 
    Set cdoMessage = Nothing  
    Set cdoConfiguration = Nothing   
End Sub

Kristen
Test

22859 Posts

Posted - 2005-02-10 : 02:03:35

We use EasyMail Objects (COM thingie) to send Emails. Seems to do everything we want with very little hassle. And has an "Express" SMTP sending tool - does a load of concurrent sends - I forget how many, hundreds I think, but it certainly flys.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-10 : 04:24:47

Very nice

Here are more resources for those interested:
[url]http://www.sqlservercentral.com/scripts/contributions/510.asp[/url]
[url]http://www.sqlservercentral.com/columnists/dharris/sendingsqlnotificationswithcdosys.asp[/url]

rockmoose

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-23 : 08:46:35

Hi,

I tried with the above code,I has this in an ActiveXscript task. Initially it gave me some errors, Then fiddled a little with it (Not much changes really just a few)

This is the code I use.

DTS ActiveXscript:-


Function Main()
Call SendMail("p.karunakaran@gmail.com","p.karunakaran@gmail.com","p.karunakaran@gmail.com","p.karunakaran@gmail.com",  "This is a test mail from Sql Server", "This is a test mail from Sql Server using CDO", True, 2 ) 

End Function

Sub SendMail (sFromAddress, sToAddress, sCcAddress, sBccAddress,  sSubject, sBody, boolReadReceipt, intImportance ) 
'	on error resume next
	Const cdoDispositionNotificationTo = "urn:schemas:mailheader:disposition-notification-to"
	Const cdoReturnReceiptTo = "urn:schemas:mailheader:return-receipt-to"
	dim cdoMessage, cdoConfiguration
	
	Set cdoMessage = CreateObject("CDO.Message") --Removed Server from here.
	Set cdoConfiguration = cdoMessage.Configuration -- Added this

	' Outgoing SMTP server
	With cdoConfiguration
		.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "IP of smtp server"
		.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
		.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
		.Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
		.Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1" --SMTP server requires authentication
		.Fields("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username for smtp"
		.Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password for smtp"
		.Fields.Update
	End With
	



    With cdoMessage  
		' Update the CDOSYS Configuration
		.BodyPart.charset = "unicode-1-1-utf-8" 

		IF boolReadReceipt Then
			.Fields(cdoDispositionNotificationTo)	= sFromAddress  
			.Fields(cdoReturnReceiptTo)		= sFromAddress  
		End If
	
		' Set the Importance: 0:Low, 1:Normal, 2:High
		.Fields("urn:schemas:httpmail:importance").Value	= intImportance
		.Fields.Update  
       		.From = sFromAddress
		.ReplyTo = sFromAddress
		.To = sToAddress
		.Cc = sCcAddress
		.Bcc = sBccAddress
	 	.Subject 	= sSubject
		.Textbody = sBody
		.Send  
    End With  
 
    Set cdoMessage = Nothing  
    Set cdoConfiguration = Nothing   
End Sub

When I execute this dts package i get the following error:

Multi-step ole db operation generated errors. check each ole db status value, if available. No work was done

It says task failed. But The email goes out...

Any idea why it is happening?

Karunakaran

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-03-23 : 23:54:08

Ok, found what the error is...

My Main doesnt return status...

Function Main()
Call SendMail("p.karunakaran@gmail.com","p.karunakaran@gmail.com","p.karunakaran@gmail.com","p.karunakaran@gmail.com", "This is a test mail from Sql Server", "This is a test mail from Sql Server using CDO", True, 2 )
Main = DTSTaskExecResult_Success --> Added this line here.
End Function

Now it works absolute beauty...

Thanks very much for this code Samc...

Karunakaran

khautinh
Starting Member

10 Posts

Posted - 2007-09-12 : 19:26:23

Help please. I try to use the code post here and Whenever I have '.Fields.Update' I will get error:

Error Source: MS Data Transformation services (DTS) Package
Error Description: ActiveX Scripting encountered a Run Time Error During the execution of the script.

Thanks

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-12 : 19:32:52

I use the xp_sendsmtpmail from Gert Drappers. http://www.sqldev.net/xp.htm

Works great.

PS: If anyone is interested in a SQL Job in Connecticut with excellent pay please send me a resume to ValterBorges@msn.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources