Sending Emails from Access with VBA and CDO

by Philipp Stiefel, originally published November 15th, 2015

last updated 2024-01-15

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.

If you don’t want to add the library to the References in your project, you can also use Late Binding. To make this easier, I created a module with all the enums and constants from the CDO library for download.

After you either added the library reference or the VBA module with the constants, you can type this procedure into a module in your VBA Environment.

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

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 have 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.

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.

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.

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 SSL encryption to send your email, add this line to the configuration settings and change the value for cdoSMTPServerPort to 465.

Now, 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.

There are numerous code examples on the internet claiming, there is an undocumented sendtls configuration option that makes CDO send the STARTTLS command and thus upgrades and plain text connection to TLS encryption. – I was propagating this myth here as well for a while, like this:

This does not work! The sendtls configuration setting does not exist in the CDOSYS.dll! The CDO Library does not support opportunistic encryption with STARTTLS.

This does not work! The sendtls configuration setting does not exist in the CDOSYS.dll!

I guess, the sendtls rumor was fueled by the fact that many mail servers optionally support STARTTLS but do not strictly require it. In that case you can send emails using sendtls=true but it doesn’t do anything, and your connection is not encrypted at all. If STARTTLS is required by the server, you will see an error message like the following line.

The CDO library does support STARTTLS, but it has hardcoded logic when deciding about which encryption method to use.

If you enable the SmtpUseSsl option, CDO will try to send emails on port 25 (only!) using STARTTLS. On all other ports it will try to use SMPTS transport level encryption. – If the server does not support the option CDO uses for the respective port, sending the email will fail.

If SmtpUseSsl=True, CDO will never send an email over an unencrypted connection!

I guess, the sendtls rumor was fueled by the fact that many mail servers optionally support STARTTLS but not strictly require it. In that case you can send emails using sendtls=true but it doesn’t do anything, and your connection is not encrypted at all. If STARTTLS is required by the server, you will see an error message like the following line.

If your email provider is advising you to connect to their server on port 587 using STARTTLS, I recommend that you alternatively try to connect on port 25 or port 465 with smtpusessl set to true. Even though it is often not documented, it still works with many providers, including Microsoft/Office 365 using smtp.office365.com. (See below for more information on Office 365)

How important is encryption?

How important the encryption of the connection is depends on the environment you are using this in. If your mail server is within the local corporate network, I would still recommend encrypting the connection, but I could tolerate omitting it. However, if your mail server is somewhere on the internet, using encryption is mandatory.

Send Email with CDO via Microsoft/Office 365

CDO can send emails via Microsoft 365 (formerly Office 365) using the mail server smtp.office365.com on port 25 with the smtpusessl option set to true. – However, there are a couple of pitfalls that may make this difficult or impossible.

CDO authenticates your user account at the mail server using the SMTP AUTH protocol. Depending on your organization’s policies, SMTP AUTH might need to be enabled for Exchange Online before you can send Email with CDO. – I don’t think it is possible to use CDO with modern OAuth-Authentication. Even if it were, it would need additional code to implement the OAuth process before you can even start using CDO.

Usually Basic Authentication (username + password) is used to authenticate with mail servers outside your local network. This can work with Office 365. However, your organization admins may have disabled Basic Authentication for Exchange Online. Then you need to get them to configure an exception for the mailbox you intend to use with CDO.  If your users are authenticated at Azure-AD, you could try whether NTLM-Authentication option available in CDO works with your requirements. (I haven’t had the time to try this in practice.)

If you only want to send email to recipients inside your Office 365 organization, you may also try the approach Direct Send (Option 2) for printers and similar devices.

Send Email with CDO via Googlemail/GMail

If you try to use an Googlemail/GMail account to send email with CDO, you will very likely encounter an issue.

The code in VBA to send an email via Googlemail/GMail is the same as with every other email provider. However, there is an important detail you need to know. Direct SMPT authentication is disabled by default for Gmail. You need to explicitly enable Less secure apps in your Google account settings. After you did this, you can authenticate with your username and password as normal to send email with CDO.

If you use 2-factor authentication for your Google account, you cannot enable Less secure apps. Then you need to create a dedicated App Password for Gmail. After you generated the App Password, you use it with the VBA/CDO code instead of your normal password to authenticate at the SMTP server.

Diagnosing Common Problems and Errors

When you try to send an email and it fails with an error, the VBA error number is usually -2147220975 (0x80040211 = CDO_E_SMTP_SEND_FAILED). This is not particularly helpful, because sending the email may have failed for a number of reasons.

Instead of focusing on the main error number, look closely at the transport error code in the text of the message. If you see …

… 0x80040217 (=CDO_E_LOGON_FAILURE) or 0x80040215 (=CDO_E_AUTHENTICATION_FAILURE), the error message indicates a problem with authentication. If you double checked you used the correct username and password and you still see this error, the reason is most likely that SMTP Authentication with username and password is not enabled for the account you are trying to use. For Microsoft 365 and Gmail I explained potential causes and solutions above, for other mail providers see their documentation or contact their support.

… 0x80040213 (=CDO_E_FAILED_TO_CONNECT) the cause is either that you are trying to connect to a server or port that doesn’t accept connections or that you connect to a server/port that is only supporting unencrypted connections while you enabled the smtpusessl-option in your configuration. The error probably may also happen if the server uses an untrusted certificate for SSL/TLS encryption, but that should not happen with reputable mail providers.

… 0x80040212 (=CDO_E_CONNECTION_DROPPED) the cause is probably that you did not enable SSL/TLS encryption in the CDO configuration, but the server strictly requires an encrypted connection.

… a different error code, go through the CdoErrors defined in the CDO library to find the error name (constant name) to the numeric/hexadecimal error code. This may provide a hint to what is the root cause of the problem.

Charset and Content-Transfer-Encoding

An email is basically a text file that is transferred via the network from one computer to another. Whenever text information exceeding the range of US-ASCII (7 bit, only 128 different characters) is transferred between different computers or even different applications there might be loss of information or ambiguity on how to interpret the information.

To prevent information loss and ambiguity, email clients should specify the charset of the text and may use content transfer encoding. By default, CDO will use Content-Transfer-Encoding: 8bit and will not specify a charset. The email client displaying the email for the recipient will then use its default charset to display the text. – This can already go wrong if either sender or recipient is outside of an English-speaking country or Western Europe. It will definitely go wrong once the email text contains characters outside of the basic 8bit range (only 256 characters), e.g., Asian, Cyrillic or Arabian characters. Any such character will be displayed as a question mark for the recipient of the email.

To prevent such problems, specify a charset suitable to represent all characters used in the text of your email. Unless you are only writing plain English, which can be represented by US-ASCII, the best choice is probably UTF-8 which covers almost any existing character with a reasonable low overhead.

Here is an example showing how to specify charset and encoding for a CDO email.

Note, the 8bit encoding is the default and is only specified here as an example. I think, in this day and age it’s reasonably safe to assume that all software involved in processing email can handle 8bit text data. Only if you expect your email to travel via some old legacy systems, Quoted Printable transfer encoding would be a better choice.

HTML Emails

CDO supports sending emails with HTML text instead of plain text. To send an HTML email, you would just set the HTMLBody property instead of TextBody.

Email Importance, Read-/Delivery-Receipts, and Custom Headers

CDO also supports SMTP email properties like Importance, Delivery- and Read-Receipts, Priority, and user defined, custom headers.

You can use the Fields collection of the Message object (as opposed to the Configuration object) to add lots of additional meta information to the email message to be created. The modules CdoMailHeader and CdoHTTPMail of the CDO library contain the property references for all commonly used header names from the SMTP email specifications.

Example for Importance and Priority

This is a code excerpt to create an email with high Importance and urgent Priority:

Please note: The Priority of an email is not intended for the recipient but is an instruction for the mail transfer agents (mail servers), to process an email with higher priority, i.e. quicker. – This header is mostly obsolete today; it was relevant in a time when resources, particularly bandwidth, were more limited and there was significant delay until an email was processed by a busy, underpowered server on a slow connection.

Example for Read- and Delivery-Receipts

To request a Read- and/or Delivery-Receipt for an email, you must add the email address to receive the receipt as value for the respective headers.

This will add a Disposition-Notification-To header, which is the delivery notification, and a Return-Receipt-To header, which is the read receipt, to the email.

Do not rely on read receipts and delivery receipts! They are not reliable at all. It is at the discretion of the recipient and his software whether they will be processed. Also, they may increase the chance that your email is classified as junk mail. Some email servers may even outright refuse to accept your email if it contains a read or delivery receipt request. Gmail is reported to do this.

Example for Custom X-Headers

There are more useful predefined headers in CdoMailHeader and CdoHTTPMail. But you can also use a similar mechanism to add a completely custom mail header. To do that use the prefix “urn:schemas:mailheader:” and then append your own, custom header name.

Non-standard, custom headers should begin with “X-“ to distinguish them from official header names defined in the official SMTP specifications.

Requirements and (dis-) advantages of using CDO to send email

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 your Access project and are ready to go immediately.

Share this article: