Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to Send Email from Excel using Gmail
Option Explicit
' 4 variables below require configuration.
' Once you get this working, refactor, etc.
Sub CDO_Mail_Small_Text_2()
Dim user As String
Dim pass As String
Dim port As Integer
Dim receiverEmail As String
Dim fromEmail As String
'change these variables:
user = "YourGmail@gmail.com"
pass = "YourGmailPassword"
fromEmail = """John Doe"" <YourGmail@gmail.com>"
receiverEmail = "YourGmail@gmail.com"
'If you get this error: "The transport failed to connect to the server..."
' then change the SMTP port from 465 to 25
port = 465
'If you get another error, you may need to enable the "Less Secure" option for GMail:
' https://www.google.com/settings/security/lesssecureapps
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/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = user
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = pass
.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") = port
.Update
End With
strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"
With iMsg
Set .Configuration = iConf
.To = receiverEmail
.CC = ""
.BCC = ""
.From = fromEmail
' Note: The reply address is not working if you use this Gmail example
' It automaticaly uses your Gmail address. But you can add this line to change the reply address:
'.ReplyTo = "human@example.com"
.Subject = "Important message"
.TextBody = strbody
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
End Sub
@bergerjac

This comment has been minimized.

Copy link
Owner Author

bergerjac commented Jul 28, 2018

Here's a great starter's resource: Sending mail from Excel with CDO by Ron de Bruin

@bergerjac

This comment has been minimized.

Copy link
Owner Author

bergerjac commented Jul 28, 2018

This is the accompanying Google Doc on How to Send Email from Excel using Gmail

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.