VBA: Send e-mail using gmail

ilsley_excel

Board Regular
Joined
Mar 5, 2015
Messages
54
Office Version
  1. 2010
Platform
  1. Windows
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:

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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I use this server:

smtp.googlemail.com
 
Upvote 0
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!
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top