Sending email with CDO loses connection or requires 5.7.3 STARTTLS

Question

I have Excel VBA code which uses CDO to send an email using smtp.office365.com. This has worked for the last 5 years but suddenly stopped working in October 2021.

Error handling returns either:

1. The transport lost its connection to the server.
2. 5.7.3 STARTTLS is required to send email.

Interestingly enough, when the Excel sheet is used on any other workstation the code still works as expected. I have since reinstalled Office and Windows thinking it must be a PC related issue. However, the issue still persists.

I can send an email using PowerShell on the same workstation using the same server, ports & credentials.

Code:

Const cdoSendUsingPort = 2  ' Send the message using SMTP
Const cdoBasicAuth = 1      ' Clear-text authentication
Const cdoTimeout = 60       ' Timeout for SMTP in seconds

Const mailServer = "smtp.office365.com"
Const SMTPport = 25
Const mailusername = 
Const mailpassword = 

    Set objEmail = CreateObject("CDO.Message")
    Set objConf = objEmail.Configuration
    Set objFlds = objConf.Fields

    With objFlds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
        .Update
    End With

I am not quite sure how to further troubleshoot this. I originally thought it may relate to blocked ports or some sort of corruption. I have formatted the PC and reinstalled Windows and Office.

Answer 1

It seems there is some conflict between Windows 10 Pro and Office 2019 Professional and using port 25 for smtp.office365.com.

As mentioned I have several PC's with combinations of Windows 10 Pro/Home and Office 2019 and Office 365 and all of these worked. However, the specific combination of Office 2019 Professional and Windows 10 Pro causes an issue. This is even if the CDO is used outside of Excel VBA (using a python script).

Troubleshooting:

1. Reinstall Windows 10 Pro 21H2 on a new partition and installing Office 2019 - fail
2. Reinstall Windows 10 Pro 2004 (to test earlier version of Windows) on a new partition and not installing Office - pass
3. Reinstall Windows 10 Pro 2004 on a new partition and installing Office 2019 Pro - fail
4. Reinstall Windows 10 Pro 2004 on a new partition and installing Office 365 - pass

Next step is to see if going to 21H2 and Office 365 still works.

Strangely enough, no ports are shown as blocked on the specific machine, no ports are blocked by the ISP, so Office 2019 must be doing something either to CDO or to ports - I wouldn't know how to identify what though

Answer 2

Try to add the configuration field bellow to the ones you are setting:

.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True

Also, try to change the port from 25 to 465 or 587.