Created
July 27, 2018 15:34
-
-
Save bergerjac/7355d4e528fa6c64a02dc494f3d241a1 to your computer and use it in GitHub Desktop.
How to Send Email from Excel using Gmail
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is the accompanying Google Doc on How to Send Email from Excel using Gmail