Send emails from Excel using my Gmail account

[Roland Hoelscher]

I am developing a VBA solution which will send emails from an Excel spreadsheet using my Gmail account.
I found the code below in a previous MrExcel post (I changed only the TO and FROM email addresses).
When I run this code, after a while, it triggers this Run-time error message and points to the ".Send" line at the end.

Is the approach in my code the correct way to send emails from Excel using Gmail?
And how can I get this to work?
Thank you very much in advance!!

'============================================================
Sub CDO_Mail_Small_Text()

Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults

Set Flds = iConf.Fields

With Flds
.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/smtpserverport") = 465
.Update
End With

strbody = "Test Email"

With iMsg
Set .Configuration = iConf
.To = "rolandhoelscher@yahoo.com"
.CC = ""
.BCC = ""
.From = "rolandhoelscher@gmail.com"
.Subject = "New figures"
.TextBody = strbody
.Send
End With

End Sub

 

[MARK858]

Could be your firewall blocking it but try changing the 465 in the below to 587

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

it's been a few years since I've used Gmail rather than Outlook though

 

[rollis13]

My old snippets suggest you could use this configuration:

With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youremail@gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    .Update
End With