CDO: Anybody Using It To Send eMail?

P

PeteCresswell

I'll confess to shopping around on this one.

Started threads in two of the Outlook NGs, but I'm not getting
anywhere.

This all started when we moved an MS Access app to a Citirx server and
DoCmd.SendObject began provoking
"A program is trying to automatically send e-mail on your behalf."
dialogs - courtesy, as I undestrand it, of Outlook's "Object model
guard".

I'm hearing that the workaround is to use CDO (Collaboration Data
Objects) to feed the messages direct to "the user's SMTP
server" (whatever that is.....)

But in trying to implement that, I'm running into what seems tb a
fairly common problem: "The "SendUsing" configuration value is
invalid."

Tried a few fixes, but found no joy.

Upon reflection, it seems like there's at least one obvious problem:
authentication of the user. No way that SMTP server is going to let
just anybody dump email into it - at least in a well-run corporate
environment. I'd guess that the only hope would be some way to
present the user's credentials (ID/PW) to CDO from VBA.

Bottom Line: Has anybody had success in using CDO to send email from
MS Access?

For the masochistically-inclined, my current source code - which I
can email it as a .txt file if the wrapping is excessively heinous:
-----------------------------------------------------------------------------------------
Public Sub Email_Report(ByVal theObjectName As String,
theReportDescription As String, ByVal theEmailReportSelectionBasis As
Long)
1000 DebugStackPush mModuleName & ": Email_Report"
1001 On Error GoTo Email_Report_err

' PURPOSE: To send a copy of the named report to each person on the
' list in ttblEmailAddresses
' ACCEPTS: - Object name of the report. e.g. "rptMaturities"
' - Description of the report
' - Whether we want addresses selected for report or trade
buy ticket
'
' NOTES: 1) We discontinued using MS Access' .SendObject command
because it was provoking
' an "Object model guard" issue in Outlook - resulting
in an irritating
' "...program is trying to automatically send e-mail on
your behalf."
' confirmation dialog issued by MS Outlook.
'
' Supposedly the CDO.Message object goes direct to
SMTP, bypassing Outlook.
' CDO = Collaboration Data Objects

1002 Dim myRS As DAO.Recordset
Dim myCdoMessage As CDO.Message
Dim myCdoConfig As CDO.Configuration

Dim curAddress As String
Dim myQueryName As String
Dim myTempDir As String
Dim mySnpPath As String

Dim i As Long

'
------------------------------------------------------------------
' Get path to user's "Documents and Settings", then create a Temp
' directory under it

1010 myTempDir = Environ("UserProfile")
1019 myTempDir = myTempDir & "\Temp"

On Error Resume Next
MkDir myTempDir
On Error GoTo Email_Report_err

'
------------------------------------------------------------------
' Create a snapshot of our report in the temp dir, after having
' deleted any pre-existing file

1020 mySnpPath = myTempDir & "\" & theObjectName & ".snp"

On Error Resume Next
Kill mySnpPath
On Error GoTo Email_Report_err

1030 DoCmd.OutputTo acOutputReport, theObjectName, "Snapshot Format",
mySnpPath

'
------------------------------------------------------------------
' Customize CDO configuration as needed
' Docs are in http://msdn.microsoft.com/en-us/library/ms526318(EXCHG.10).aspx
' PROBLEM: Got to assume that whatever "SMTP Server" is, it's going
to require
' an ID/PW.... but how to get/supply those?

1040 Set myCdoConfig = New CDO.Configuration

1050 With myCdoConfig.Fields
1051 .Item(cdoSendUsingMethod).Value = cdoSendUsingPort
1052 .Item(cdoSMTPServerPort).Value = 25
1053 .Item(cdoSMTPServer).Value = "localhost"
1054 .Update
1059 End With

'
------------------------------------------------------------------
' Create a CDO Message object, whose "TO:" we will customize for
each

1090 Set myCdoMessage = New CDO.Message
1091 With myCdoMessage
1092 .From = CurrentUserGet()
1093 .Subject = theReportDescription
1094 .textbody = theReportDescription & " report attached as .SNP
file."
1095 .AddAttachment mySnpPath
1099 End With

'
------------------------------------------------------------------
' Determine our input query and open our recordset of email
addresses

1110 Select Case theEmailReportSelectionBasis
Case gEmailReportSelectionBasis_Report
1112 myQueryName = "qryEmailAddresses_Selected_Report"

1113 Case gEmailReportSelectionBasis_Trade_Buy
1114 myQueryName = "qryEmailAddresses_Selected_Trade_Buy"

1115 Case Else
1116 BugAlert True, "Unexpected EmailReportSelectionBasis=" &
theEmailReportSelectionBasis & "'."
1119 End Select

1120 Set myRS = CurrentDb.OpenRecordset(myQueryName, dbOpenSnapshot,
dbForwardOnly)

'
------------------------------------------------------------------
' Loop through the email addresses, sending a copy of the message
to each

1130 With myRS
1131 If ((.BOF = True) And (.EOF = True)) Then
1132 MsgBox "Please select at least one eMail address and try
again.", vbExclamation, "Cannot eMail: No Addresses Selected"
1133 Else
1134 Do Until .EOF = True
1139 curAddress = !EmailAddress & ""

1140 If Len(curAddress) > 0 Then
1150 With myCdoMessage
1151 .To = curAddress
1152 .Send
1159 End With
'1159 DoCmd.SendObject acSendReport, theObjectName,
"Snapshot Format", curAddress, , , theReportDescription,
theReportDescription & " report attached as .SNP file...", False
1990 End If

1991 .MoveNext
1992 Loop
1993 End If
1999 End With

Email_Report_xit:
DebugStackPop
On Error Resume Next
Kill mySnpPath
Set myCdoMessage = Nothing
Set myCdoConfig = Nothing
myRS.Close
Set myRS = Nothing
Exit Sub

Email_Report_err:
BugAlert True, "curAddress='" & curAddress & "'."
Resume Email_Report_xit
End Sub
-----------------------------------------------------------------------------------------
 
B

bcap

Yep, I do it all the time.

Don't like the look of your code - all those line numbers, eeeugh! - here's
how I typically do it. Note that this uses basic authentication, but many
SMTP servers do NOT require authentication.

Dim CDOMessage As Object
Dim CDOConf As Object
Dim CDOFlds As Object
Set CDOConf = CreateObject("CDO.Configuration")
Set CDOFlds = CDOConf.Fields
CDOFlds("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
CDOFlds("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "my
SMTP server name"
CDOFlds("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =
25
CDOFlds("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") =
1
CDOFlds("http://schemas.microsoft.com/cdo/configuration/sendusername") =
"user name"
CDOFlds("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
"password"
CDOFlds.Update
Set CDOMessage = CreateObject("CDO.Message")
Set CDOMessage.Configuration = CDOConf
With CDOMessage
.From = strFrom
.To = strEmail
.Subject = "TEST TEST TEST"
.TextBody = strBody
.Send
End With
 
P

PeteCresswell

Don't like the look of your code - all those line numbers, eeeugh!
Click to expand...

There's definately a PITA factor in typing/maintaining the line
numbers.
The do come in handy, though, when logging errors to a file - lets me
zero
in on the problem quicker.
here's how I typically do it.
Note that this uses basic authentication, but many SMTP servers do NOT
Click to expand...
require authentication.

Well, at least I've got it down to where all the pieces are probably
there - having replicated your code pretty much exactly - albeit using
the eNums instead of spelling things out.

viz:
----------------------------------------------
Set myCdoConfig = New CDO.Configuration

With myCdoConfig.Fields
.Item(cdoSendUsingMethod).Value = cdoSendUsingPort
.Item(cdoSMTPServer).Value = "post04.corp.seic.com"
.Item(cdoSMTPServerPort).Value = 25
.Item(cdoSMTPAuthenticate).Value = cdoBasic
.Item(cdoSendUserName).Value = cdoBasic
.Item(cdoSendPassword).Value = "suedog7a"
.Update
End With
----------------------------------------------

But I'm getting the same error.

My guess is that the Configuration object is talking to the SMTP
server I specified and determining that, somehow, I have sinned.

Sounds to me like I have to dig into exactly what my SMTP server is
and
what it wants from me.
 
S

schasteen

Here is the code I use, maybe you can get something out of it

Const cdoSendUsingPort = 2
Const cdoBasic = 1
Dim objCDOConfig As Object, objCDOMessage As Object
Dim strSch As String


Location = "S:\Public\" & SendAttachment & ".snp"

If SendAttachment <> "" Then
DoCmd.OutputTo acOutputReport, SendAttachment, "Snapshot Format", Location
End If


strSch = "http://schemas.microsoft.com/cdo/configuration/"
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(strSch & "sendusing") = cdoSendUsingPort
.Item(strSch & "smtpserver") = "Servername"

.Item(strSch & "SMTPAuthenticate") = cdoBasic
.Item(strSch & "SendUserName") = "(e-mail address removed)"
.Item(strSch & "SendPassword") = "Password"
.Update
End With


Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.from = "UserName"
.Sender = "UserName"
.to = "To adress"
If Len(SendCc) > 0 Then
.cc = SendCc
End If
.Subject = "Subject"
.TextBody = "Body"
If Len(SendAttachment) > 0 Then
.AddAttachment Location
End If
.send
End With

Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
 
P

(PeteCresswell)

Per schasteen:
Here is the code I use, maybe you can get something out of it

Const cdoSendUsingPort = 2
Const cdoBasic = 1
Dim objCDOConfig As Object, objCDOMessage As Object
Dim strSch As String


Location = "S:\Public\" & SendAttachment & ".snp"

If SendAttachment <> "" Then
DoCmd.OutputTo acOutputReport, SendAttachment, "Snapshot Format", Location
End If


strSch = "http://schemas.microsoft.com/cdo/configuration/"
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(strSch & "sendusing") = cdoSendUsingPort
.Item(strSch & "smtpserver") = "Servername"

.Item(strSch & "SMTPAuthenticate") = cdoBasic
.Item(strSch & "SendUserName") = "(e-mail address removed)"
.Item(strSch & "SendPassword") = "Password"
.Update
End With


Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.from = "UserName"
.Sender = "UserName"
.to = "To adress"
If Len(SendCc) > 0 Then
.cc = SendCc
End If
.Subject = "Subject"
.TextBody = "Body"
If Len(SendAttachment) > 0 Then
.AddAttachment Location
End If
.send
End With

Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
Click to expand...

Thanks. I think that's re-enforcing my notion that I'm doing
more-or-less the right thing, but have some unresolved issue with
the corporate environment's server.
 
B

bcap

Unless I've missed something, you haven't actually said anywhere WHAT error
you are getting.

The user name is cdoBasic??? Shome mishtake shurely?

Also, if you try to authenticate with a server that doesn't require it, it
may well fail. And "basic" isn't the only authentication method.

My error logging also logs line numbers, but I don't put them in as a matter
of course, only when trying to focus in on a specific problem.

As for enums, I'm a dedicated fan of late binding: it can save a great deal
of heartache when distributing applications far and wide. Could've created
my own constants I suppose, but since my emailing code is encapsulated in
just one class module it hardly seems worth it.

Finally, it probably isn't a good idea to publish the actual server name and
password on Usenet.
 
P

Paul Shapiro

Others have given you code. I would add that CDO appears to have been
deprecated and is likely not installed on most computers. In Office 2003 it
was an optional part of the Outlook installation, not selected in a Typical
installation. It's not even an option in Office 2007, so those users would
need to install it from a separate MS download.

There are a few solutions to continuing to use Outlook while avoiding the
object model guard. If Outlook is connected to Exchange, you can globally
control the object model guard from Exchange. If not using Exchange, there
is a free application called Express ClickYes, distributed by
www.ContextMagic.com, which can be set to automatically click the Yes button
in the object model guard. You can add simple VBA code to enable and disable
this utility, so it's only enabled when your code is sending mail. I see
that there is a license fee for use with Citrix. Finally, I think I read
that Outlook 2007 allows object model access as long as it sees an
up-to-date antivirus on the user's computer, although I haven't tested this.
A good source of Outlook info is www.slipStick.com.
 
P

(PeteCresswell)

Per bcap:
The user name is cdoBasic??? Shome mishtake shurely?
Click to expand...

A feature, not a bug. Sort of in the same spirit as not
publishing the actual server name.
 
D

david

background information:

"CDO" on a server was not the same as "CDO" on a client.
I don't remember if the objects have different names: server
cdo, cdoNTS, had more features. What version of CDO
are you using?

"CDO" is not the same as SMTP. On a client, CDO
connected to mapi which connected to a mail client.
On a server, CDONTS connected to Exchange, (probably
using extended MAPI?). Both are .com objects: neither
use SMTP.

You can find third-party SMTP objects to use instead of CDO.
You can find third-party MAPI objects to use instead of CDO.

On a server, you can may be able to use the existing SMTP
service by just dropping mail messages into the SMTP queue,
which used to be just a folder, and may still be.

The 'send' dialog (but not other dialogs) can be avoided by not
sending mail: I used to just place the mail in the Outlook Outbox,
leaving it to the user to Send the mail. When there are 100s of
messages, they only have to select everything and send, but they
also have the opportunity to review, so it was a win both ways.

(david)
 
A

a a r o n _ k e m p f

I just prefer to do this from a single database server-- that way, you
can enforce security using standard 'stored procedure' security.

using Access-- which is no longer reccomended-- means that it is a
pain to configure email on 20 different machines.
SQL Server is much easier to configure-- you only need to do it from
one machine-- and it is much more powerful.
 
B

bcap

From SQL Server Books Online 2005:

"Database Mail is not available in SQL Server 2005 Express Edition".

Which would make it a prohibitively expensive solution for anyone who does
not already have, and does not otherwise need, a paid-for edition. Not sure
what the situation is with 2008.

When I implement CDO-based email within Access applications, I store the
SMTP details/credentials in a table in the database. There's no need to
configure *anything* on each individual client.

As for CDO being "deprecated", as far as I can tell it's not included with
Outlook 2007 because Microsoft reckons Outlook 2007 already gives you
everything you need. Which is as much use as a chocolate teapot to anyone
distributing runtime Access applications who cannot be sure that Outlook
2007 will already be installed. The solution, as always, is to make sure
that when you distribute your app you make sure that you include all dll's
that *might* be needed. Having said that, I've yet to encounter a computer
that *didn't* have CDO already installed.
 
A

a a r o n _ k e m p f

it's available in MSDE 2.0

and it's available in MSDE 1.0.
and it's available via xp_sendsmtpmail

it's called google brotha

-Aaron
 
A

a a r o n _ k e m p f

jesus criminy

I cannot believe you say this:
------------------------------------------------
When I implement CDO-based email within Access applications, I store
the
SMTP details/credentials in a table in the database.
-----------------------------------------------

_YOU_ are probably the reason I get hundreds of spam emails.
Literally.

and btw, SQL Server Workgroup is what $3000 for a processor license?
I don't think that is prohibilitively expensive for anyone.

better that than a piece of crap database that has compacting problems
once a month and requires a programmer to change network locations
(one of my main complaints with Jet is that SQL Passthrough connection
strings are not modified with the Linked Table Manager)
 
A

a a r o n _ k e m p f

and for the record? I've actually worked places where the network
admin will remove (unregister) _ANY_ dlls that don't meet his
approval.
 
B

bcap

Aaron, you're a dickhead.

jesus criminy
I cannot believe you say this:
------------------------------------------------
When I implement CDO-based email within Access applications, I store
the
MTP details/credentials in a table in the database.
----------------------------------------------
Click to expand...
YOU_ are probably the reason I get hundreds of spam emails.
Literally.
Click to expand...
 
A

a a r o n _ k e m p f

I love how you can use Erl in order to print the lineNumber in error
handling... pretty slick effect
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CDO Won't Send Across Domains 2
CDO Email Default Settings 5
Trying to Create Email Function 7
How to use a combo box variable in a VBA module 1
Send Workbook as an Attachment Via Gmail - Code Review 1
Sending mail with CDO through a remote SMTP server 2
Email Using CDO - mixed results 6
CDO-email 0

Top