Tachytelic.net

Use CDO to send email from VBScript or VBA through Office 365

by 18 Comments

Sending email via VBScript or VBA using CDO is easy to do, but the correct configuration to relay through Office 365 is confusing to say the least and it took me me a while to find the correct settings.

I knew from configuring other devices and software that the preferred way to setup SMTP to relay to Office 365 was to use TLS on port 587. The problem is that officially CDO does not support TLS, but unofficially it does. So I tried in vain to develop some code that would send email via smtp.office365.com using TLS and always came up with the following error:

The server rejected the sender address. The server response was: 530 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM

So clearly the TLS support in CDO is not sufficient to be able to work with the Office 365 SMTP Server. The trick is not to use TLS at all, but to use SSL instead on port 25 instead, which seems to work fine:

VBScript to Send Email via Office 365 (smtp.office365.com)

This is just quick sample code to get you on the right path:

Dim objMessage, objConfig, Fields
Set objMessage = CreateObject("CDO.Message") 
Set objConfig = CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
With Fields
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Office365Password"
  '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
  .Update
End With
Set objMessage.Configuration = objConfig

With objMessage
  .Subject = "Test Message"
  .From = "[email protected]"
  .To = "[email protected]"
  .HTMLBody = "Test Mesage"
end With
objMessage.Send

VBA to send email via Office 365 (smtp.office365.com)

The code for VBA is almost the same as VBScript and I tested it using Excel 2016 without any problems at all. But first you need to add a reference to your project:

  1. In the Office VBA Code Editor go to Tools, and then References:
    Image showing how to add references to an Office VBA Project
  2. In the References dialog box add a reference to the “Microsoft CDO for Windows 2000 Library”:
    Image showing how to add the Microsoft CDO Library to the Office VBA Environment
  3. Use the following sample code….
Sub Office365_Email_Test()
    Dim objMessage, objConfig, fields
    Set objMessage = New CDO.Message
    Set objConfig = New CDO.Configuration
    Set fields = objConfig.fields
    With fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Office365Password"
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Update
    End With
    Set objMessage.Configuration = objConfig
    
    With objMessage
        .Subject = "Test Message"
        .From = "[email protected]"
        .To = "[email protected]"
        .HTMLBody = "Test Message"
    End With
    objMessage.Send
End Sub

A few things to note:

  • The account that you use must have at least an Exchange Online license.
  • You will only be able to send from addresses that account has send as permission for or an alias of that account.
  • It would probably be a good idea to use an account dedicated for sending SMTP because the password is being stored in clear text.
  • Your Firewall or your ISPs Firewall may block outbound port 25.
  • I’ve left the code for TLS in-place above but commented out, in case anyone else wants to have a play with it.

I hope this helps, I spent ages trying to relay through Office 365 over TLS with VBA, but SSL works just fine.

 

Reader Interactions

Comments

  1. Marty says

    I use my email address and get the error…. “The transport failed to connect to the server”

  2. Paulie says

    Maybe your firewall is not letting you access the SMTP server. If you try it with an SMTP client (with the same settings) from the same machine, does it work?

  3. Narg says

    Didn’t Microsoft just turn off SSL for Office365? I tried this today (10/25/19) and it didn’t work for me. No errors though, not sure what to look at to determine failure. New to Office365 so quite dumb to all it’s admin features.

  4. Thad says

    @Marty

    I had the same issue and resolved it by changing the code to use port 465 instead of 25. Specifically, look at the following line of code from the article:

    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25

  8. John Haigh says

    Thankyou, it worked exactly as you said it would.

    I added an extra line of code, “.Attachment = C:\ABC.txt” and but it said that it did not object does not support this property or method.
    Would you be able to advise me what method does please?
    I have tried so much code from search engines, and yours worked straight out of the box.

    Here’s hoping
    John

  10. Simon Cash says

    It worked for me in 2020 thx! BTW you don’t need to add the CDO reference because you are using late binding i.e CreateObject so it works without having to include that.

  12. Davy says

    Worked first time for me. The only challenge was that my client needed to send images inside the HTML body. The only way to do that I found was to upload the images to a website and then in the HTML code point to these images. As a slight improvement to this, note that you can add the following line inside the “WITH objMessage” block:

    .AddAttachment (attachment)

    ….where “attachment” is a string pointing to the filename.

    Many thanks Paul, very useful!

  16. Andre Niquet says

    Wow … this is great stuff. It works like a charm. Thanks for sharing …

    In the past I tried many variations in the settings, but never succeeded, so started to send automated email (from an Access database) using the smtp server of another emailaddress within our company (with another domain using POP3/SMTP). Luckily the class Message knows a member / property ‘From’ so that i can send these message on behalf of our ‘normal’ Exchange E-mail.
    Unfortunately the email messages sent with this method, are not saved in the ‘sent items’ .

    And to send those emails properly which doesn’t end up in spamfolders, we had to add an SPF record in the DNS settings.

    But now … WOW … thanks to this post I can change it all for the better …

  17. Jay says

    By the way, from what I have been able to tell, when you tell CDO to connect using SSL, it actually is using TLS, which is the successor to SSL. It is just that it starts out unencrypted and then issues a STARTTLS command to switch to encrypted communications before authenticating. If STARTTLS fails, then the connection is denied.

  18. Val says

    I’ve been helping in some troubleshooting with the following below. Our org uses port 587. we still keep getting the error “The transport failed to connect to the server”… can someone shed some light?

    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = username
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.office365.com”
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 587 ’25
    .Item(“http://schemas.microsoft.com/cdo/configuration/sendtls”) = True
    .Item(“http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout”) = 10

Leave a Reply