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.
