SMTP email from Excel Macro using Office 365

[BatchFile]

I've got the ReportSender spreadsheet from the SIMS Success Team (here).

Run by itself, by double-clicking, it works fine through Outlook, but as a scheduled taks, it doesn't. I'm therefore exploring the SMTP option. The macro code I've got for that bit is as follows:

 

Sub send_mail_SMTP()


   Dim CDO_Mail As Object
   Dim CDO_Config As Object
   Dim SMTP_Config As Variant
   
   Set CDO_Mail = CreateObject("CDO.Message")
   On Error GoTo Error_Handling
   
   Set CDO_Config = CreateObject("CDO.Configuration")
   CDO_Config.Load -1
   
   Set SMTP_Config = CDO_Config.Fields
   
   With SMTP_Config
       .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") = 587
       .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") = "MyP@55w0rd"
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpencryption") = STARTTLS
     
       .Update
   End With
   
   With CDO_Mail
       Set .Configuration = CDO_Config
   End With
   
   CDO_Mail.Subject = ThisWorkbook.Sheets("Set up").Range("B9")
   CDO_Mail.From = ThisWorkbook.Sheets("Set up").Range("B6")
   CDO_Mail.To = ThisWorkbook.Sheets("Set up").Range("B7")
   CDO_Mail.TextBody = ThisWorkbook.Sheets("Set up").Range("B10")
   CDO_Mail.CC = ""
   CDO_Mail.BCC = ""
   CDO_Mail.AddAttachment thepath + "" + reportname + ".xlsx"
   CDO_Mail.Send


Error_Handling:
   If Err.Description <> "" Then MsgBox Err.Description


End Sub

but it's failing with

"The Server rejected the sender address. The server response was: 451 5.7.3 STARTTLS is required to send email"

 

Any ideas, anyone?

Edited March 8, 2024 by ZeroHour

[Foresthippy]

I had issues sending with SMTP via 365. The obvious one is whether you've enabled SMTP access for the user in 365 admin. If you have conditional access set up, there's a policy that blocks legacy authentication in there too, which we also had to bypass for a specific mailbox.

[chaplic]

My recommendation would be "I woudn't start from here". Lets assuming you can fight through all the O365 settings stopping this from working you might run into your source IP is prohibited from sending SMTP as well. Using Graph API to send the mail would be much more future proof and portable.