ilsley_excel
Board Regular
- Joined
- Mar 5, 2015
- Messages
- 54
- Office Version
- 2010
- Platform
- 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:
I would greatly appreciate any help or advice!
Thanks.
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.