Language: Deutsch English















Last Update: 2018 - 06 - 02





Sending Emails from Access with VBA and CDO

by Philipp Stiefel, originally published November 15th, 2015

last updated February 27th, 2018


retro typewriter, article header image

The two other methods I discussed to send emails from within Microsoft Access have both their strengths and weaknesses.

Sending emails with the DoCmd.SendObject command is very simple and independent of the mail client installed, but it has also very limited functionality.

Using Outlook automation to send emails on the other hand is very powerful and there is very little that cannot be achieved with it. However, it totally depends on Microsoft Outlook being installed on the client computer your Access application is running on. Furthermore it might be hampered by security measures of Microsoft to restrict automation access from other applications to send email.

Meet the CDO Library

Here I want to introduce you to another method to send email from Visual Basic code. It is based on the Collaboration Data Objects (CDO). The full name of the library we are going to use is Microsoft CDO for Windows 2000. This library does not depend on an installed mail client, because it has all the functionality to send email built-in.

One major advantage of this library is, that you can safely assume it to be installed on the computer your application is running on. It is included in the Windows NT operating system ever since Microsoft Windows 2000 was released. While that is no guarantee for the future, for now you don’t have to worry about it being not available.

Send a simple email

Ok so I’ll show you how to send a simple email with CDO. Before we get start, you need to add a reference to the CDO Library. To do this use the Tools->References menu in the VBA-Editor. If you don’t find the library in the list of available references, just browse to the System32 folder (SysWow64 on 64-bit machines) and add the cdosys.dll.

Screenshot of VBA References with CDO Library added

Then you can type this procedure into a module in your VBA Environment.

Public Sub SendSimpleCDOMail() Dim mail As CDO.MESSAGE Dim config As CDO.Configuration Set mail = CreateObject("CDO.Message") Set config = CreateObject("CDO.Configuration") config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort config.Fields(cdoSMTPServer).Value = "mail.mycompany.invalid" config.Fields(cdoSMTPServerPort).Value = 25 config.Fields.Update Set mail.Configuration = config With mail .To = "someone@somewhere.invalid" .From = "me@mycompany.invalid" .Subject = "First email with CDO" .TextBody = "This is the body of the first plain text email with CDO." .AddAttachment "C:\path\to\a\file.dat" .Send End With Set config = Nothing Set mail = Nothing End Sub

Of course you should update all the values representing the mail sever name, the email addresses and the file name to the attachment with values that are valid in your environment. If you don’t want to send an attachment right now, you can comment that line out.

While doing this, you will probably recognize the (minor) problem associated with that. – You need to know the mail server name that the user of your application is going to use.

Now as you adjusted those values, try to send an email with this code. – Does it work?

You should not be too surprised if a runtime error occurs at the call to the Send method. An error message similar to this one might appear.

Screenshot of error message '530 Authentication required'
The server rejected the sender address. The server response was: 530 Authentication required

This means your mail server refuses to deliver the email you were trying to send. And this is actually a good thing. Your mail server does not allow just anyone to send emails to prevent Spam.

Authentication

To authenticate us at the mail server, we need to supply our credentials. This is simple to do, we just need to add these three lines to the code to set the username and the password for the mail server.

config.Fields(cdoSMTPAuthenticate).Value = cdoBasic config.Fields(cdoSendUserName).Value = "youUserName" config.Fields(cdoSendPassword).Value = "yourPassword"

Try again if the email gets sent now. – It usually should.

Notice that this are two more configuration settings, which need to be stored in your Access application or you could ask the user to enter these values each time he wants to send an email (inconvenient).

If your application is used within a Windows Domain with Active Directory and the user is authenticated in the domain, you can use NTLM authentication. The user (or your application) does not need to supply username and password to the mail server then.

To enable this, change the configuration setting for cdoSMTPAuthenticate to cdoNTLM.

config.Fields(cdoSMTPAuthenticate).Value = cdoNTLM

Obviously NTLM Authentication has to be enabled on the mail server for this to work.

Encryption

Now are mainly done, but we still could improve the security of our mail sending. Most mail servers nowadays support using an encrypted connection to send emails. The CDO-Library does too.

To use a SSL encryption to send your email add this line and change the value for cdoSMTPServerPort.

config.Fields(cdoSMTPServerPort).Value = 465 config.Fields(cdoSMTPUseSSL).Value = True

To the configuration settings and change the port to 465. Try again if sending the email still works.

There are a couple of situations where it will not work to send the email over an encrypted connection. The SSL certificate the server uses might not be trusted. The CDO Library will not be able to connect then. Some mail servers do not support implicit SSL connections on port 465 but only explicit TLS connections on port 587. This unfortunately is not supported by the CDO Library.

Opportunistic TLS-Encryption with STARTTLS

Some mail servers require a connection with opportunistic TLS. This connection will first be established as an unencrypted connection, usually on port 587. Then, during the handshake phase between client and server, the server will indicate that it is capable of TLS (Transport Layer Security). The client should then issue the STARTTLS command to initiate the elevation of the connection to encryption via TLS.

Officially, according to documentation, the CDO Library is not supporting this type of encrypted connection. However, there is the undocumented sendtls configuration option for CDO that makes CDO send the STARTTLS command and thus upgrades and plain text connection to TLS encryption.

To use this, you just need to add this line to the setting of your CDO configuration.

config.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendtls").Value = True

How important is encryption?

How important the encryption of the connection is, depends on the scenario you are using this in. If your mail server is within the local corporate network, there should be no need to encrypt the connection. If your mail server is somewhere on the internet, I would recommend using encryption if possible.

Requirements and (dis-) advantages

The only requirement for this code is the CDO Library (cdosys.dll) which is available in every standard installation of Windows since Windows 2000.

Advantages

  • Independent of the email client software on the computer

  • Very powerful and feature rich

Disadvantages

  • Requires the full configuration of the mail account to be used to be available in the Access application

  • More complicated to use than DoCmd.SendObject and Outlook automation

  • It is not clear for how long this technology will still be supported by Microsoft

Download

For your convenience I uploaded a VBA module containing the sample code. In this module all the constants from the CDO Library are declared locally. So you do not need to add a reference to the CDO Library but you can just import the module into you Access project and are ready to go immediately.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Subscribe to my newsletter

*

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. The data is covered by the EU-U.S. Privacy Shield Framework. See our privacy policy for further details.



© 1999 - 2018 by Philipp Stiefel - Privacy Policiy