Excel VBA - Sending an email using smtp.gmail.com

[Padawan018]

I have a code that is supposed to send an email using Gmail but it fails at the .Send line in the code with the error:

"The Transport failed to connect to the server."

I have looked up other post online and tried changing from server port 25 and 465 but with the same results.

Here is the code I am using, which is loaded from a form to get the email, password, and message.

See my module code below.

Sub SendEmailUsingGmail(email As String, pw As String, msg As String)
    
    Dim NewMail As CDO.Message
    Dim msgConf As CDO.Configuration
    
    ' Object creation
    Set NewMail = CreateObject("CDO.Message")
    Set msgConf = CreateObject("CDO.Configuration")
    
    ' Server Configuration
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = email
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = pw
    msgConf.Fields.Update

    'Set All Email Properties
    With NewMail
        .Subject = "Excel Tool"
        .From = email
        .To = "testEmail@gmail.com"
        .CC = ""
        .BCC = ""
        .TextBody = msg
        .Configuration = msgConf
        .Send
    End With
    
    MsgBox ("The Message has been sent")

'Set the NewMail Variable to Nothing
    Set NewMail = Nothing
    Set msgConf = Nothing
    
End Sub

 

[Perpa]

I have a code that is supposed to send an email using Gmail but it fails at the .Send line in the code with the error:

"The Transport failed to connect to the server."

I have looked up other post online and tried changing from server port 25 and 465 but with the same results.

Here is the code I am using, which is loaded from a form to get the email, password, and message.

See my module code below.

Sub SendEmailUsingGmail(email As String, pw As String, msg As String)
    
    Dim NewMail As CDO.Message
    Dim msgConf As CDO.Configuration
    
    ' Object creation
    Set NewMail = CreateObject("CDO.Message")
    Set msgConf = CreateObject("CDO.Configuration")
    
    ' Server Configuration
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = email
    msgConf.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = pw
    msgConf.Fields.Update

    'Set All Email Properties
    With NewMail
        .Subject = "Excel Tool"
        .From = email
        .To = "testEmail@gmail.com"
        .CC = ""
        .BCC = ""
        .TextBody = msg
        .Configuration = msgConf
        .Send
    End With
    
    MsgBox ("The Message has been sent")

'Set the NewMail Variable to Nothing
    Set NewMail = Nothing
    Set msgConf = Nothing
    
End Sub

Padawan018,
How are you passing the 'email' and 'pw' to the macro? I have used the CELLS command to load information from
my spreadsheet to the macro, you might try that. 'email' has to be the same on both lines of code. I use some very
similar code to send out a monthly calendar. I didn't see any big differences between your code and mine.
Below is the code I use. The 'email' and 'pw' I put directly in the code, not reading from the spreadsheet.
Perpa

Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant
    Dim PDFfileName As String
    
    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/smtpusessl") = True
        
        .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/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = [COLOR="#FF0000"]"youremailname@gmail.com"[/COLOR]
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = [COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]"yourGmailPassword"[/COLOR]

        .Update
    End With

    'PDFfileName = Cells(28, 3)   'This needs to be the complete path and filename if it is used, I kept that info in C28.

    strbody = "Automated email" & vbNewLine & vbNewLine & _
              "Info Here" & vbNewLine & vbNewLine & _
              "Your Name Here"

    With iMsg
        Set .Configuration = iConf
        .To = "someoneElse@gmail.com"    'this can be any email address
        .CC = ""
        .BCC = ""
        .From = [COLOR="#FF0000"][/COLOR][COLOR="#FF0000"]"youremailname@gmail.com"[/COLOR]     'MUST BE THE SAME AS USED ABOVE
        .Subject = Cells(4, 6) & " " & Cells(4, 8)      'I had the Month and Year in these two cells, remove and change 
        .TextBody = strbody
        .AddAttachment     'PDFfileName  - this was my calendar
        .Send
    End With

End Sub

 

[Padawan018]

I use a form to allow the users to enter their UN and PW.