Last Update: 2017 - 12- 27
Sending Emails from Access with VBA and CDO
by Philipp Stiefel, originally published November 15th, 2015
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.
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 = "email@example.com" .From = "firstname.lastname@example.org" .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.
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.
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.
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.
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.
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.
© 1999 - 2017 by Philipp Stiefel