Do you know CDO? - 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

Development Tools

ASP.NET

Do you know CDO?

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-05 : 10:03:15

This is the CDO function to send email from DTS.

Function SendEmail (strFrom, strTo, strCc, strBcc, strSubject, strBody)
Const cdoSendUsingPort = 2
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"

Dim objMail
on error resume next
strErrorMsg = ""

' Create the CDO Configuration Object and initialize
Set objConfiguration = CreateObject("CDO.Configuration")
Set objFields = objConfiguration.Fields
With objFields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPServer) = "localhost"
.Update
End With

' Create the CDO Message
Set objMessage = CreateObject("CDO.Message")
With objMessage
Set .Configuration = objConfiguration ' Configure the message communications
.MimeFormatted = TRUE
.From = strFrom
.To = strTo
.Cc = strCc
.Bcc = strBcc
.Subject = strSubject
With .Bodypart ' Initialize the bodypart to support UTF-8
.ContentMediaType = "text/plain"
.ContentTransferEncoding = "7bit"
.Charset = "utf-8"
SET Stm = .GetDecodedContentStream
Stm.WriteText strBody
Stm.Flush
End With
.Send
End With
SET objMessage = Nothing
SET objFields = Nothing
SET objConfiguration = Nothing

SendEmail = err.description ' Return last error if any
End Function

Now I don't understand that Stm.WriteText, but it stops at the first CHAR(13) so it needs a loop.

Anyone know how to fix this?

Sam

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-09-05 : 10:27:33

Hmmmm.

I don't recognize that function call. Where did you get this method from? The "official" CDO vbscript method for sending email through CDOSYS doesn't use it to my recollection ("official" as in the one referenced in MSDN as a code example).

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-05 : 10:37:52

Cobbled this together from Microsoft's bit's and pieces. It works fine, except it sends a single line.. I've got to fix that.

IMHO - CDO is a complex language structure for sending unicode emai.

There's a book on CDO coming out in a month or so. Maybe it'll help.

Meanwhile, I can't find any other technique for stuffing the bodytext into the message.

One thought - replace char(13) with char(10), and keep the code as-is.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-05 : 10:41:20

Not obvious how to do a unicode replace in vbscript.

SET Stm = .GetDecodedContentStream
Stm.WriteText REPLACE(strBody, CHAR(13), CHAR(10))
Stm.Flush

In SQL, there's NCHAR and CHAR. Not so in VBscript.

Whatdoido?

Sam

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-09-05 : 11:59:46

Sam - if you lose the carriage returns, won't it screw up the formatting of the email?
Replace() has an optional parameter for comparision type, try both the options

'vbBinaryCompare=0
'vbTextCompare=1

Stm.WriteText REPLACE(strBody, CHAR(13), CHAR(10),,, 0)
Stm.WriteText REPLACE(strBody, CHAR(13), CHAR(10),,, 1)

And AFAIK, VBScript uses Unicode internally to represent all strings.

Owais

Make it idiot proof and someone will make a better idiot

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-05 : 12:19:11

Char(10) and CHAR(13) provide identical results in plain text email as far as I can tell.

Maybe it's dependent on the email package viewing the email? If so, I need another solution.

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 12:24:09

I have a requirement to create a DTS package that has a destination file with only carriage returns at the end of each row. When I open up this file on my Windows XP machine, it displays all of the rows on one row, which is kinda annoying if you are trying to view the data in the file. When you open up the file on a Unix machine, it displays the rows on separate lines like it should. So I suspect that some e-mail packages will have this kind of problem as well.

Tara

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-09-05 : 12:27:01

Open it in XP with what software? Notepad??

Get UltraEdit if you don't already have it, and never look at another editor ever again.

On a PC you really should expect display like that, because only a 0x0A 0x0D combination indicates a new line. The better editors realize that you're looking at a UNIX file if you only have carriage returns.

Jonathan
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-09-05 : 12:34:49

Well, getting back to my problem. The CDO email routine I wrote writes a single line in the string.

Other than parsing the string and writing substrings (SLOW) - it would seem CDO would have a more efficent way of moving the string into the emailbody.

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-05 : 12:35:40

I've got UltraEdit and a couple of other editors, but I'm not sure which one I used to view the file.

Tara

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-09-08 : 04:05:17

Hi Sam,

not sure if this will really help you, but i have found this to be an excellent source of information and source code
http://www.cdolive.com

Cheers,
Frank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources