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:
- In the Office VBA Code Editor go to Tools, and then References:
- In the References dialog box add a reference to the “Microsoft CDO for Windows 2000 Library”:
- 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.
Marty says
I use my email address and get the error…. “The transport failed to connect to the server”
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?
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.
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
Shivananda says
Thanks for sharing. It’s worked for me.
Mrigaja says
How to read mail using IMAP server without configuring outlook.com.??
Jan says
Thanks for sharing. It’s worked for me.
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
Chris says
Brilliant, the VBScript worked like a dream first time round. Good Work!!
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.
Paulie says
Thanks for the info and glad it worked for you.
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!
Rocky W says
Woo, your VBSCRIPT code is working, thank you very much !
Jeimmy says
Thanks for sharing. It’s worked for me.
Uttam says
Thanks a lot. It’s worked for me.
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 …
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.
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
Maykon William says
I performed the tests using the server smtp.office365.com that uses port 587, but I was returning the transport error to the server.
I changed the port to 25 and it worked, if it doesn’t work, I recommend trying port 465
Carolyn Rainaud says
This works fine – thanks for this. I’d like to create and then add a .pdf or just a worksheet to the email using Office 365 Outlook. I’ve done it many times using Outlook desktop application, but can’t get it to work with Office 365. Any help is appreciated!
Paulie says
You mean you want to add an attachment to the message?