• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Sco Openserver
    • Sco Unix Support
    • SCO Openserver Installation ISOs
    • SCO Openserver Binaries
    • Add a Hard Drive to an Openserver 5 Virtual Machine
    • Install SCO Vision SQL-Retriever ODBC Driver on Windows 10
    • License Expired on Virtual SCO Openserver Installation
    • How to reset the root password on SCO Openserver 5
  • Scripting
    • PowerShell
      • Add leading zeros
      • Check if a File Exists
      • Grep with Powershell
      • Create Environment Variables
      • Test for open Ports
      • Append to a Text File
    • VBScript
      • Check if a File Exists
      • lpad and rpad functions
      • Windows Update E-Mail Notification
  • Office 365
    • Connect to Office 365 with PowerShell
    • Add or remove an email alias using Powershell
    • Change Primary email address of Active Directory user
    • How to hide an AD user from the Global Address List
    • How to hide mail contacts from the Global Address List
    • Change the primary email address for an account with PowerShell
    • Change Primary email address of an AD User
    • Grant a single user access to access to all calendars
    • Forward email to an external address using Powershell
    • Convert shared mailbox to user mailbox with Powershell
  • Get in Touch
  • About Me
    • Privacy Policy

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

December 16, 2018 by Paulie 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.

 

Filed Under: How To, Office 365, Scripts & Utilities Tagged With: E-Mail, Office 365, VBA, VBScript

Reader Interactions

Comments

  1. Marty says

    October 21, 2019 at 5:39 pm

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

  2. Paulie says

    October 21, 2019 at 5:40 pm

    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

    October 25, 2019 at 2:48 pm

    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

    February 7, 2020 at 8:44 pm

    @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

  5. Shivananda says

    March 3, 2020 at 10:42 am

    Thanks for sharing. It’s worked for me.

  6. Mrigaja says

    March 12, 2020 at 5:12 am

    How to read mail using IMAP server without configuring outlook.com.??

  7. Jan says

    March 25, 2020 at 7:58 am

    Thanks for sharing. It’s worked for me.

  8. John Haigh says

    April 14, 2020 at 7:31 pm

    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

  9. Chris says

    April 21, 2020 at 8:52 am

    Brilliant, the VBScript worked like a dream first time round. Good Work!!

  10. Simon Cash says

    April 25, 2020 at 10:47 am

    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.

  11. Paulie says

    April 25, 2020 at 6:30 pm

    Thanks for the info and glad it worked for you.

  12. Davy says

    May 12, 2020 at 8:27 pm

    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!

  13. Rocky W says

    June 2, 2020 at 12:15 pm

    Woo, your VBSCRIPT code is working, thank you very much !

  14. Jeimmy says

    June 22, 2020 at 2:42 pm

    Thanks for sharing. It’s worked for me.

  15. Uttam says

    July 3, 2020 at 9:04 am

    Thanks a lot. It’s worked for me.

  16. Andre Niquet says

    July 13, 2020 at 3:39 pm

    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

    January 22, 2021 at 3:54 pm

    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

    February 5, 2021 at 11:25 pm

    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 Cancel reply

Primary Sidebar

Link to my LinkedIn Profile

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 107 other subscribers.

Go to mobile version