VBA – CDO Mail

Although I often refer people to use CDO mail, I have only briefly discussed it in prior postings and I never really gave any usage examples. Today that changes!

So what exactly is CDO Mail and when is it useful?

Collaborative Data Objects – (CDO) is the COM-based interface for accessing Exchange or any other Messaging API (MAPI)-compliant service such as fax services, POP3 services, and so on.Sean McCormick

CDO mail is one of many techniques available to developer to send e-mails.

If you are an MS Access developer you are surely aware of the SendObject method, but it will only allow one attachment and that attachment has to be an Access object (not an external file).

Another very common approach is to use Outlook automation, but with new security impositions by Microsoft it has become unreliable and now requires workarounds. Furthermore, after a recent discussion with fellow MVPs, we find out that having an outdated virus definition can stop longstanding code from working! As such, although powerful, outlook automation simply is not reliable for true production. For production tools, one must then look at integrating tools such as Outlook Redemption, but the down side to this solution is that it requires registering a COM library for it to work.

Which brings us to CDO Mail!

CDO Mail is a technique that binds you directly to your e-mail server to send out email(s).

CDO Mail Pros

No e-mail client (Outlook, Thunderbird, …) need be installed
- Hence you bypass any e-mail client limitations and/or security restrictions
Permits multiple attachments to be included
Permits external documents as attachments
Accepts both plain text and HTML formatted content
Can be used in any programs which uses VBA (MS Access, Excel, Word, PowerPoint, MS Project, …)
Can even be used in VBScript

CDO Mail Cons

Requires knowledge of all the server settings (port, username, password, authentication, …)
Does not keep a copy of sent e-mails so CCing or BCCing yourself becomes useful for archive purposes
Does not permit attaching database objects directly, but you can export them or print them as PDFs and attach those

CDO Mail VBA Code Sample

Below is a sample CDO Mail function I created many moons ago for my own purposes and have tweak slightly over the years.

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Function SendCDOMail(sTo As String, sSubject As String, sBody As String, _
                     Optional sBCC As Variant, Optional AttachmentPath As Variant)
    On Error GoTo Error_Handler
    Dim objCDOMsg       As Object
    Dim i               As Long
 
'    Set objCDOMsg = New CDO.Message             'Early Binding -> Microsoft CDO for Windows 2000 Library
    Set objCDOMsg = CreateObject("CDO.Message") 'Late Binding
 
    'CDO Configuration
    With objCDOMsg.Configuration.Fields
        '
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
        'Server port (typically 25, 465, 587) '***The next line is commented out because of the use of SSL encryption***
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 'Commonly -> 465 SSL, 587 TLS
        'SMTP server IP or Name
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.hitterslongrun.com"
        'Type of authentication, NONE, Basic (Base64 encoded), NTLM
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
        'SMTP Account User ID
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "accounts@hitterslongrun.com"
        'SMTP Account Password
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Uhdje!@@0#"
        'Number of seconds to wait for a response from the server before aborting
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10
        'Use SSL for the connection (False or True) -> If using SSL, do not specify the Port above
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True '!!!!Always use some form of encryption!!!!
        'Use TLS for the connection (False or True) - See the note below
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True 'Does not work in CDO
        .Update
    End With
 
    'CDO Message
    objCDOMsg.Subject = sSubject
    objCDOMsg.From = "accounts@hitterslongrun.com"
    objCDOMsg.To = sTo
    'objCDOMsg.TextBody = sBody 'This would be for plain text e-mails
    objCDOMsg.HTMLBody = sBody 'This would be for HTML formatted e-mails using HTML tags
    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
        If IsArray(AttachmentPath) Then
            For i = LBound(AttachmentPath) To UBound(AttachmentPath)
                If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
                    objCDOMsg.AddAttachment AttachmentPath(i)
                End If
            Next i
        Else
            If AttachmentPath <> "" Then
                objCDOMsg.AddAttachment AttachmentPath
            End If
        End If
    End If

    '****Do not forget although we can configure the following, the recipient can disable the functionality
    '       at their end on the server/email client, so this truly is not reliable!
    'Read receipt
'    objCDOMsg.Fields(CdoMailHeader.cdoReturnReceiptTo) = "accounts@hitterslongrun.com"                    'Early Binding
    objCDOMsg.Fields("urn:schemas:mailheader:return-receipt-to") = "accounts@hitterslongrun.com"           'Late Binding
    'Delivery receipt
'    objCDOMsg.Fields(CdoMailHeader.cdoDispositionNotificationTo) = "accounts@hitterslongrun.com"          'Early Binding
    objCDOMsg.Fields("urn:schemas:mailheader:disposition-notification-to") = "accounts@hitterslongrun.com" 'Late Binding

    objCDOMsg.Send
 
Error_Handler_Exit:
    On Error Resume Next
    Set objCDOMsg = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SendCDOMail" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Now that we have a function to call, we can now send out an e-mail by making a single call to the function along the lines of:

Call SendCDOMail("RecipientEmail", "Subject", "EmailBody", , array("C:\Users\Test\Documents\sample.pdf", "C:\Users\Test\Documents\ballons.gif"))

Recommendation!

I HIGHLY recommend you always use SSL. Security measures should always be implemented.

CDO And TLS

There is great debate regarding CDO and TLS. Even though the option seems to exist, people state it does nothing and doesn’t truly work. This is way out of my field of expertise, so I will not comment at this point in time. Furthermore, I have been unable to find any official documentation on the subject as Microsoft regularly purges KB article, and other pages leaving us with nothing but dead links. (Thank you Microsoft!)

As such, I’ve left the line of code in my sample, but it is a use it at your on risk, and hopefully you know what your doing to validate it actually works.

Change Your Port

If you get an error such as:

Run-time error ‘-2147220978 (8004020e)’:
The server rejected the sender address. The server response was: 530 Authentication requiredCDO Mail - Microsoft Visual Basic

Run-time error ‘-2147220973 (80040213)’:
The transport failed to connect to the server.CDO Mail - Microsoft Visual Basic

Then try using an alternate port for sending: 587, 465, 25, … You may also need to contact your e-mail host as they may have configured special ports that must be used.

Microsoft Office365 CDO Settings

Below are the settings to access Office365 e-mail accounts which worked for me in the past.

sendusing = cdoSendUsingPort
smtpauthenticate = cdoBasic
smtpserver = “smtp.office365.com”
smtpserverport = 25
smtpusessl = True
sendusername = “YourEmail@YourDomain.com”
sendpassword = “YourEmailPassword”

A Few Resources on the Subject

Sending mail from Excel with CDO

The example code is using CDOSYS (CDO for Windows 2000). It does not depend on MAPI or CDO and hence is dialog free and does not use your mail program to send email.

VBScript To Send Email Using CDO

VBScript code sample for using CDO Mail

MS Access - How To Send An Email

Now here’s a question we see all the time in the various forums. As usual, there are a number of ways to accomplish this and which one you should use has a lot to do with your needs and abilities. In this post, I’m going to briefly discuss the 3 most popular approaches: SendObject method…

Continue reading →

CDO: Part I

MSDN Library: Periodics 2000: Excerpt:

51 responses on “VBA – CDO Mail”

Judy Strickland August 26, 2015 at 12:55 pm

I have successfully sent emails from forms and reports in access 2013 using CDO but I cannot display the email (in case user wants to edit before sending). I’ve tried .Display and cdomsg.display but receive runtime error 438 every time. .Send works perfectly. Any advice?
1. Daniel Pineault Post authorSeptember 9, 2015 at 8:38 am
  
  CDO does not provide an editor in which users can modify e-mails. If this is a requirement of your then you have a couple of possibilities:
  
  – Switch technologies and use something like Outlook
  – Create a form which will enable your users to edit the e-mail subject, body, … and then use its content to populate the CDO code.
Aaron September 29, 2015 at 1:41 pm

Thanks for posting this; I have been stuck on a large email automation process, and need to use CDO to defer the time sent until 4am. I haven’t been able to find anything that works, including PR_DEFERRED_SEND_TIME. any suggestions?
1. Daniel Pineault Post authorOctober 9, 2015 at 9:05 am
  
  Why not simply create a timer event that checks the current time and waits until the specified time before launching the e-mail process?
tomas January 14, 2018 at 4:42 pm

Hi, can we combine .TextBody and .HtmlBody in a message? Aim is to have 1st part of message with just a text note and then an html Signature (logo, card ..) below it. Not confused the signature with attachment. Thanks!
1. Daniel Pineault Post authorJanuary 14, 2018 at 8:16 pm
  In that case, it is an HTML e-mail. The minute you need one piece of content to be HTML, then the whole thing is considered to be HTML, but truthfully, for what you are describing it is very simple to accomplish.
  Simply use the paragraph tag for your plain text
```
< p >...< /p > //I've added extra space so it will display
```
  and then at the end you can use fancy tags for your signature.
  
  Also, I have seen certain e-mail programs do actually send 2 versions of e-mails: Plain text and HTML. So depending on the recipients e-mail client settings, they will get one version or the other displayed. In my test, using Outlook as my client, I only see the HTMLBody displayed.
Matt Taylor May 13, 2019 at 6:15 pm

Hi,

This is really helpful and works well. When calling the function, is there a way to add Sender, CC and BCC addresses, or does that need to be in the function itself (ie objCDOMsg.From, objCDOMsg.CC, objCDOMsg.BCC)?
1. Daniel Pineault Post authorMay 16, 2019 at 8:14 am
  
  Yes, you would need to add these to the function and pass the appropriate input variables to populate them.
  
  Alternately, you could convert the function to a class and pre-populate certain properties with default values, but personally, I prefer simple functions.
Vytas May 21, 2019 at 3:26 am

Hi,
How to add report as attachment – need to save it as pdf first and enter file name in attachment or is another method?
Thanks
1. Daniel Pineault Post authorMay 21, 2019 at 12:10 pm
  
  You’ll need to use DoCmd.OutputTo to generate the PDF off of your report, and then supply that file path/name to the function as shown in the example.
  1. Vytas May 24, 2019 at 7:00 am
    
    Thanks.
Joe Polimeni September 9, 2019 at 6:11 pm

What to I suppress if I do not want any attachments?
1. Daniel Pineault Post authorSeptember 10, 2019 at 9:16 am
  
  Nothing, just don’t supply an AttachmentPath variable when you call the function. If nothing is supplied then it won’t try and include any. The function can work either way; with or without attachments.
  1. Joe Polimeni September 10, 2019 at 9:19 am
    
    Awesome. Thanks Daniel.
Joe Polimeni September 26, 2019 at 4:30 pm

If I only have one attachment:

Call SendCDOMail(“RecipientEmail”, “Subject”, “EmailBody”, , array(“C:\Users\Test\Documents\sample.pdf”, “C:\Users\Test\Documents\ballons.gif”))
or
would it be this
Call SendCDOMail(“RecipientEmail”, “Subject”, “EmailBody”, , “C:\Users\Test\Documents\sample.pdf”)

I’m also getting a Compile error “Variable Not Define” and point to the “i” variable in the AttachmentPath code below objCDOMsg.AddAttachmentAttachmentPath

Thanks!
1. Daniel Pineault Post authorSeptember 26, 2019 at 4:49 pm
  I updated the function with the missing ‘Dim i’ statement which will resolve your compilation error. Thank you for mentioning it.
  
  For a single attachment, it should be
```
Call SendCDOMail("RecipientEmail", "Subject", "EmailBody", , "C:\Users\Test\Documents\sample.pdf")
```
Andrew November 19, 2019 at 10:33 am

Is there a way to paste from Microsoft Clipboard directly into a CDO message body?
1. Daniel Pineault Post authorNovember 20, 2019 at 7:51 am
  
  You can use APIs to get the clipboard content text and use it as a variable to populate you message body.
Billybob Sand March 2, 2020 at 4:15 pm

I have been using CDO successfully for quite some time and now want to add a group email through the BCC option. My question: Is there a limit to the number of email addressess I could use in one sending? I was thinking of over 2000 addresses in one CDO event. Comments? Thank you
1. Daniel Pineault Post authorMarch 3, 2020 at 6:34 am
  
  I don’t know about limits, but such an e-mail would automatically be blocked as SPAM. You are always best to create individual e-mails. If you need that quantity, then you may wish to look into using a specialized e-mail providers.
  1. BillyBob Sand March 3, 2020 at 1:49 pm
    
    So I have been told by others. Do think the ISP would block it if I tweaked the code to send out one email approximately every 30 seconds? Not much coding from my perspective. I could probably get away with sending say 10 bcc email addresses per transaction. Even 5 would reduce the elapsed time significantly. BTW, this is not spam, but a bonnafide email distribution to the customer’s legitimate and private members. Thank you for your opinion (and I realize it is just opinion).
    1. Daniel Pineault Post authorMarch 4, 2020 at 7:52 am
      
      Most E-mail providers (ISP or Hosting company) have a limit on the total number of e-mails that can be sent/received within an hour or day. You’d need to check with your hosting company.
      
      Also, the information I provided is based on experience, as I’ve hit these issues myself creating various mass e-mailing tools for various clients. When your numbers get big enough you either have to get into loops to do batch sends, pause, send, pause, send, …. OR use service providers made for mass e-mails which don’t put caps on the numbers allowed.
Keith Broaddrick March 17, 2020 at 4:47 pm

Is there a return code that will verify that the email send was successful?
1. Daniel Pineault Post authorMarch 17, 2020 at 5:07 pm
  If it is successful, all will be fine, but if there is an issue it will err, so you can use standard error trapping or do something like
```
On Error Resume Next
objCDOMsg.Send
If Err.Number = 0 Then
    'Everything appears to have gone smoothly
Else
    'Houston we have a problem
    'display message to user...
End If
```
  Or
```
On Error Resume Next
objCDOMsg.Send
If Err.Number <> 0 Then 'Only deal with errors
    'Houston we have a problem
    'display message to user...
End If
```
  but since my code already has an error handler, it should already report back any issues.
Roberto March 27, 2020 at 12:39 pm

Great job, thanks!
However, I can’t understand the syntax for sending two or more attachments. Can you give me a tip for this?
1. Daniel Pineault Post authorMarch 31, 2020 at 10:37 am
  
  Just pass an array() of the attachment files as your AttachmentPath argument.
  
  So the basic idea would be:
  array(file1, file2, file3, …)
  and in practice it would look like:
  array(“C:\temp\myfile.xls”, “C:\temp\mydoc.docx”, “D:\Test\dev.txt”)
Brentin April 17, 2020 at 10:09 am

Is there an option to get a delivery/read receipt?
1. Daniel Pineault Post authorApril 21, 2020 at 11:37 am
  
  No, I do not believe that to be possible, but I’ve never gone digging about this.
Dre Van Sebroeck December 17, 2020 at 8:31 am

Dear Daniel,
I have placed the code in a module. Then I put the following command in the immidiate window:
Call SendCDOMail(“somconsult@gmail.com”, “Titel Test”, “EmailBody Test”)
I then get the following warning:
– Error Number; -2147220973
– Error Source: SendCDOMail
– Error Description: The transport failed to connect to the server.
1. Daniel Pineault Post authorDecember 17, 2020 at 9:10 am
  Did you adjust the code by putting your server information and credentials?
  
  You need to, at a minimum, update:
```
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.hitterslongrun.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "accounts@hitterslongrun.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Uhdje!@@0#"
```
  1. Daphne April 18, 2022 at 10:59 am
    
    Dear Daniel.
    
    I updated the server and credentials, still receiving the error.
    
    Error Description: The transport failed to connect to the server.
    
    Could you please help to solve. Thanks.
  2. pdh March 24, 2023 at 8:36 am
    
    Hi Daniel,
    I get the same error. I updated the 3 items mentioned above (smtpserver/ sendusername/ sendpassword). Is there a solution?
    1. pdh March 24, 2023 at 10:18 am
      
      Actually, I found the solution. 465 nor 25 were working. I went to advanced settings for my outgoing server and used the right port.
      One issue I do see is that the mail is not being delivered to gmail?
      1. Daniel Pineault Post authorMarch 24, 2023 at 10:53 am
        
        CDO has always worked for me once I had the proper setting and in some cases (Google – less secure apps) configure special access permissions.
        
        If a certain provider isn’t receiving e-mails, then you’d need to check and see if you’ve been blacklisted for some reason.
    2. Albert January 23, 2025 at 9:32 pm
      
      Daniel, can I get locked out if I keep on trying various ports? I changed the server to office365 and the credentials but keep getting the error saying it cannot connect to the server.
      1. Daniel Pineault Post authorJanuary 25, 2025 at 6:17 am
        
        I don’t know Microsoft’s policy, but I have managed to get locked out of my own e-mail for something similar and had to contact my host to unlock my IP.
Kip October 5, 2021 at 7:32 pm

Can you add multiple lines of HTMLBody text in the email? I typed in this code and it sends perfectly, but every time I have attempted to tweak the email body it only sends it as one long line. Even tried vbNewLine, etc and it still just sends as one line. Any thoughts on how I can have numerous lines in my email body using this CDO method?
1. Daniel Pineault Post authorOctober 6, 2021 at 1:40 pm
  
  You can send as many lines as you wish, but you need to create proper HTML content to do so. So use p, br, … tags.
Alemadgt October 28, 2021 at 8:11 am

Daniel, is there an alternative to CDO that can use a server that requires TLS?
Kamlesh Jain June 27, 2022 at 7:44 am

Hello,
Till June 1st week, my code was running successfully. But suddenly it has stopped working.
It was designed to send mail at specific time, say 10.50 AM everyday.
Is there any changes made by Google in their setup ?
Can some one help please ?
1. Daniel Pineault Post authorJune 28, 2022 at 8:12 am
  
  Take a look at https://www.devhut.net/no-longer-use-cdo-with-gmail/ for all the details on this security change.
Edgar Hurtado July 12, 2022 at 3:45 am

Hola Daniel gracias por tus aportes, estoy utilizando CDO en VisualStudio para recibir los correos vía IMAP, pero no he podido recibir los attachments me podrías dar una luz como hacerlo ?
1. Daniel Pineault Post authorJuly 12, 2022 at 10:36 am
  
  Me temo que nunca he usado CDO para leer/recibir correos electrónicos, así que puedo ser de ayuda con esto. Le sugiero que publique su pregunta, con su código existente, en un foro y, con suerte, alguien más podrá ayudarlo.
Marc Slatter August 9, 2022 at 6:47 am

Hi Daniel,

I am using MS Access to send out emails and I have multiple users using the database to do so. Is there a way I can automate the login credentials in the SMTP settings? i.e. can I get a box to appear when a user clicks the ‘Send Email’ button that they can use to enter their gmail user and password details which will feed into the vba code?

Many thanks!
1. Daniel Pineault Post authorAugust 9, 2022 at 9:07 am
  
  What I have done is create a table in which a user inputs (via a form limited to a single entry) the credentials (I encrypt the values). Then I modified the function to lookup the values at runtime (DLookUp is the quickest and dirtiest way to do this).
  1. Marc Slatter August 11, 2022 at 5:06 am
    
    Thanks for your reply and forgive me for following up with another question.
    
    I have a login screen and have used a Dlookup to get the user and password but when I try to use this in the code itself it doesn’t work. I’m very new to VBA and might be getting the syntax wrong? I have the login details in the query that I’m using to send the email and then use ‘ .item(msConfigURL & “/sendusername”) = [UserName] ‘ but this doesn’t work. Thank you for your help
    1. Daniel Pineault Post authorAugust 11, 2022 at 9:13 am
      
      You should be able to do
      
      .item(msConfigURL & “/sendusername”) = DLookUp("FieldName", "TableQueryName", "Condition")
      
      or
      
      .item(msConfigURL & “/sendusername”) = ![UserName]
      
      Have you ensured that the query returns the value in the specified field?
      Does your code compile without errors?
      What happens when you run your code?
      Have you tried adding a breakpoint and checking what values are returned?
      1. Marc Slatter August 11, 2022 at 11:19 am
        
        Perfect, putting the dlookup into the .item line worked a treat, many thanks!
      2. Daniel Pineault Post authorAugust 11, 2022 at 2:27 pm
        
        I’m glad that worked! Good luck with the rest of your project.
Raizy Rosenberg March 28, 2023 at 4:04 pm

When I call the SendCDOMail function I get this warning:
– Error Number; -2146644475
– Error Source: SendCDOMail
– Error Description: The requested body part was not found in this message.

I tried stepping through the code, and I see it happens by the line: objCDOMsg.Subject = sSubject
Can you help me?
1. Daniel Pineault Post authorMarch 28, 2023 at 4:31 pm
  
  You don’t give enough information to be able to help. I’d urge you to post your question in one of many Forums and provide a copy of the code you are using, how you are calling it…