Auto Email using Gmail

[GeraldDLT]

Hello,

Need to create auto email using gmail with data inputs from excel.
Whenever i try simple email, i'm getting this error.

"The transport failed to connect the server."

Source: https://www.makeuseof.com/tag/send-emails-excel-vba/

Any advise?


Thanks,
Gerald

 

[Deepak]

Hi,

If you receive an error that reads The transport failed to connect to the server, make sure you’ve entered the correct username, password, SMTP server, and port number in the lines of code listed underneath With SMTP_Config

 

[GeraldDLT]

Thanks, Deepak.

Can you look into this?

Sub test()
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String

strSubject = "Results from Excel Spreadsheet"
strFrom = "TEST@gmail.com"
strTo = "TEST@gmail.com"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are: "


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.gmail.com"
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "TEST@gmail.com"
 .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "******"
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With

With CDO_Mail
 Set .Configuration = CDO_Config
End With

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
'CDO_Mail.CC = strCc
'CDO_Mail.BCC = strBcc
CDO_Mail.Send

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

End Sub

 

[Belleke]

Try changing smtpserverport from 25 to 465
If your password is let us say test then sendpassword should be "test" and not
"****"

 

[GeraldDLT]

Thanks, Belleke.

Tried changing serverport to 465, still getting the err msg.
I'm also sure i input the password correctly.

 

[Belleke]

This should be helpfull
https://www.rondebruin.nl/win/s1/cdo.htm
clear explanation and you can download a example workbook.