SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 CDOSYS Send Email
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SamC
White Water Yakist

USA
3460 Posts

Posted - 02/08/2005 :  13:58:04  Show Profile  Reply with Quote
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

Edited by - SamC on 02/08/2005 14:03:39

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/10/2005 :  02:03:35  Show Profile  Reply with Quote
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 02/10/2005 :  04:24:47  Show Profile  Reply with Quote
Very nice

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

rockmoose
Go to Top of Page

karuna
Aged Yak Warrior

581 Posts

Posted - 03/23/2005 :  08:46:35  Show Profile  Send karuna a Yahoo! Message  Reply with Quote
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
Go to Top of Page

karuna
Aged Yak Warrior

581 Posts

Posted - 03/23/2005 :  23:54:08  Show Profile  Send karuna a Yahoo! Message  Reply with Quote
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
Go to Top of Page

khautinh
Starting Member

10 Posts

Posted - 09/12/2007 :  19:26:23  Show Profile  Reply with Quote
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
Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 09/12/2007 :  19:32:52  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000