1

I found several variations to send email via Gmail in VBA and watched a YouTube demo using the sub below.
I tried smtpserverport 25 and 465.
I tried smtpserver smtp.gmail.com and smtp.office365.com.
I have the Microsoft CDO Reference selected.
I verified the Gmail password and get no error messages, no compile errors.

I call the sub from the main routine above via send_email_via_gmail. I added a Msgbox just past the send statement to make sure the sub is called and it does reach it but I never get the emails. I have O365.

Sub send_email_via_gmail()
Dim myMail As CDO.Message
Set myMail = New CDO.Message
myMail.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
myMail.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
myMail.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
myMail.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
myMail.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
myMail.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxxxxx@gmail.com"
myMail.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxxx"
myMail.Configuration.fields.Update
With myMail
.Subject = "Test email from Stock App"
.From = "xxxxxxx@gmail.com"
.To = "xxxxxxx@comcast.net"
.TextBody = "Test email from Me to Me"
End With
On Error Resume Next
myMail.Send
Set myMail = Nothing
End Sub

When I remove the On Error statement I do get a run-time error

The message could not be sent to the SMPT server. The transport error code was 0x80040217. The server response was not available.

Community
  • 1
  • 1
mwnskn
  • 23
  • 5
  • 1
    Remove the `On error`-statement and see if an error occurs – FunThomas Mar 11 '21 at 17:12
  • When I remove the On Error statement I do get a run-time error stating "The message could not be sent to the SMPT server. The transport error code was 0x80040217. The server response was not available." – mwnskn Mar 11 '21 at 17:20
  • 1
    The gmail SMTP server seems not to accept your mail. see https://stackoverflow.com/questions/9157087/getting-error-transport-error-code-was-0x80040217-while-sending-email-in-asp-net – FunThomas Mar 11 '21 at 17:24
  • Thank you, that helped. Now I need to decide if I really want to turn off 2-step verification and turn on Less Secure App Access. – mwnskn Mar 11 '21 at 17:49

1 Answers1

0

Don't turn off 2-step verification ... create a custom gmail password for this app.

https://support.google.com/accounts/answer/185833?hl=en

Ruffhi
  • 1