How to Send Email from Excel using Gmail

BasicExcelToGmail.vba

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 = "[email protected]"
    pass = "YourGmailPassword"
    fromEmail = """John Doe"" <[email protected]>"
    receiverEmail = "[email protected]"
    
    '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 = "[email protected]"
        .Subject = "Important message"
        .TextBody = strbody
        .Send
    End With
    
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Sub