CODEWITHSUNDEEP
excelvbaemailms-accesscdo.message
Thetafinity
Thetafinity

Reputation: 35

CDO.Message Email from Office 365 Account in VBA

I'm trying to send an email from an account on Office 355 using CDO.message in VBA to automate some email tasks in Microsoft Access.

The code is from this source.

I get

"The transport failed to connect to the server"

From my research, it seems to be an issue with TLS authentication.

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 '465
    .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") = "passowrd"
    '.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

EDIT: The email needs to be sent from the same email address no matter the user, and thus, I don't believe using Outlook will work.

EDIT 2: It seems to be that ports 25 and 465 are blocked by my ISP. Using telnet I was able to get a response from port 587, however, I get the same error as before (except this time I get it immediately instead of after a long delay). Likely due to lack of TLS authentication.

Upvotes: 1

Views: 8488

Answers (2)

Gustav
Gustav

Reputation: 55816

Your settings are correct.

What you miss is, most likely, to create and use an App Password.

Official documentation:

Manage app passwords for two-step verification

As a very first step, open a command prompt and verify you can reach the server:

telnet smtp.office365.com 25

This shall at once return something like this:

220 HE1PR09CA0075.outlook.office365.com Microsoft ESMTP MAIL Service ready at Fri, 19 Feb 2021 18:48:46 +0000

If that times out, check your firewall and router settings.

Upvotes: -1

DenimChicken
DenimChicken

Reputation: 59

This is an alternative that I've used before:

Private Sub btnSendEmail_Click()
    Dim olObject As Object
    Dim mail As Object
    Dim address As String, subject As String, body As String
    
    Set olObject = New Outlook.Application
    Set mail = olObject.CreateItem(olMailItem)
    
    address = "[email protected]"
    subject = "THIS IS THE EMAILS SUBJECT LINE"
    body = "THIS GOES INTO EMAILS MESSAGE BODY"
    
    mail.To = address
    mail.subject = subject
    mail.HTMLBody = body
    mail.Send
    
    If MsgBox("Email Sent!", vbExclamation + vbOKOnly, "Submitted") = vbOK Then: Exit Sub
End Sub

And here are the required references: vba references

I've used this from a MS Access form where the user enters text that goes into the message, then clicks a "Send Email" button to send via Outlook. Hopefully this helps!

Upvotes: 2

Related Questions