Help making VBA Gmail mail merge code work

[robgoldstein]

I am adapting some code to make a mail merge work directly from Excel using a Gmail address, but I am having some problems. Any help would certainly be appreciated.
I want this to send an email to every column that does not equal "not scheduled this week" in column "I"
my info is in the following columns

First Name: C
Team: H
Game Date: I
Game Location: J
Game time: K
Field:L
Email: M

Sub SendWith_SMTP_Gmail_To_Parent()
'Works On Windows (Not Mac). Mac Users Should Use Zapier Integration
'Created by Randy Austin www.ExcelForFreelancers.com
Dim EmailMsg, EmailConf As Object
Dim Subj, Mess, Json, URL, LastName, FirstName, Email, Attach As String
Dim ContactRow, LastRow, SentCounter As Long
Dim EmailFields As Variant
Set EmailMsg = CreateObject("CDO.Message") 'CDO (Collaboration Data Objects) -Make sure you have the 'CDO For Windows' Library Selected
Set EmailConf = CreateObject("CDO.Configuration")
    EmailConf.Load -1    ' Set CDO Source Defaults
     Set EmailFields = EmailConf.Fields
     With EmailFields
        .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
        .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") = "Robgoldstein@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
        .Update
     End With
With Sheet1
    
    
    LastRow = .Range("E999").End(xlUp).Row 'Get Last Row Of Table
    
    For ContactRow = 2 To 55
        Subj = .Range("B53").Value 'Email Subject
        Mess = .Range("B54").Value 'Email Message
        If .Range("I" & ContactRow).Value <> "not scheculed this week" Then GoTo NextRow
        FirstName = .Range("C" & ContactRow).Value
        Date = .Range("I" & ContactRow).Value
        Team = .Range("H" & ContactRow).Value
        Location = .Range("J" & ContactRow).Value
        Time = .Range("K" & ContactRow).Value
        Field = .Range("L" & ContactRow).Value
        Email = .Range("M" & ContactRow).Value
        Subj = Replace(Replace(Subj, "#date", Date), "#LastName#", LastName)
         Mess = Replace(Replace(Mess, "#FirstName#", FirstName), "#LastName#", LastName)
        Mess = Replace(Replace(Mess, "#FirstName#", FirstName), "#team#", Team),"#date#", Date), "#location#",Location), "#gametime#",Time"), "#field#",Field)
       
        With EmailMsg
            Set .Configuration = EmailConf
            .To = Email
            .CC = ""
            .BCC = ""
            .From = """SC Toronto 2011 Boys Winter Soccer"" <robgoldstein@gmail.com>"
            .Subject = Subj
             If Attach <> Empty Then .AddAttachment Attach
            .TextBody = Mess
            .Send
        End With
        SentCounter = SentCounter + 1
NextRow:
    Next ContactRow
    
      'Cleanup
    Set EmailMsg = Nothing
    Set EmailConf = Nothing
    Set EmailFields = Nothing
End With
MsgBox SentCounter & " Emails have been sent"
End Sub

</robgoldstein@gmail.com>

 

[RoryA]

Perhaps you need to put an actual email address in the From field. Or at least don't surround the name in quotes.

 

[robgoldstein]

Could this be because the 1st email is in row 2 and not row 1?

 

[RoryA]

Your From address is hardcoded and the same for every email. I'm suggesting that it may need to be an actual email address, not "SC Toronto 2011 Boys Winter Soccer".

 

[robgoldstein]

sadly, that didn't help.

thanks for trying.

 

[RoryA]

I only get that error if the From text is not an actual email address.

 

[robgoldstein]

strange. I can't get past it. Even when I change the From to my gmail address.

 

[RoryA]

And you did put it as just:

.From = "youremailaddress@gmail.com"

without any additional quotes or spaces in there?

 

[robgoldstein]

Yes.
it is highlighting the
.send
line

 

[RoryA]

With the same error message?

 

[robgoldstein]

yes. Thanks so much, I feel bad about taking so much of your time on this.