VBA: Send e-mail using gmail

[ilsley_excel]

Hi,

I am trying to build a routine that will allow me to send an e-mail in Excel via my work gmail account, but I keep receiving the following error: "Could be no internet connection! The transport failed to connect to the server."

Our local IT team gave me these Gmail config settings:

SMTP Server = smtp-relay.gmail.com
SMTP Server Port = 25, 465 or 587 depending on which transport method is being used


This is the code:

Sub SendEmailUsingGmail()

    On Error GoTo Err


    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant
    Dim msConfigURL As String


    Set NewMail = CreateObject("CDO.Message")
    Set mailConfig = CreateObject("CDO.Configuration")


    ' load all default configurations
    mailConfig.Load -1


    Set fields = mailConfig.fields


'Set All Email Properties


    With NewMail
        .Subject = "testing"
        .From = "myemail@myworkplace.co.uk"
        .To = "youremail@myworkplace.co.uk"
        .CC = ""
        .BCC = ""
        .textbody = "Testing!"
    End With


    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"


    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True


        'Make SMTP authentication Enabled=true (1)
        .Item(msConfigURL & "/smtpauthenticate") = 1


        'Set the SMTP server and port Details
        .Item(msConfigURL & "/smtpserver") = "smtp-relay.gmail.com"
        .Item(msConfigURL & "/smtpserverport") = 587   
        .Item(msConfigURL & "/sendusing") = 2


        'Set your credentials of your Gmail Account
        .Item(msConfigURL & "/sendusername") = "myemail@myworkplace.co.uk"
        .Item(msConfigURL & "/sendpassword") = "Football1"


        'Update the configuration fields
        .Update


    End With
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox ("Mail has been Sent")


Exit_Err:


    Set NewMail = Nothing
    Set mailConfig = Nothing
    End


Err:
    Select Case Err.Number


    Case -2147220973  'Could be because of Internet Connection
        MsgBox " Could be no Internet Connection !!  -- " & Err.Description


    Case -2147220975  'Incorrect credentials User ID or password
        MsgBox "Incorrect Credentials !!  -- " & Err.Description


    Case Else   'Rest other errors
        MsgBox "Error occured while sending the email !!  -- " & Err.Description
    End Select


    Resume Exit_Err


End Sub

I would greatly appreciate any help or advice!

Thanks.

 

[yky]

I use this server:

smtp.googlemail.com

 

[ilsley_excel]

I use this server:

smtp.googlemail.com

Thanks for this. I tried it but it still came up with the same error.

I also spoke to our IT team, who adjusted permissions to allow for third-party apps to access Gmail, but that still hasn't fixed the issue.

Any ideas...?

Thanks!

 

[yky]

Your parameters are pretty much same as mine. I use port 465. That should not be the problem. My username and server are both gmail. Not sure if that is the reason.

 

[ilsley_excel]

<Post removed - resolved>