We help IT Professionals succeed at work.

Send email from Excel via Gmail

In the prior solution, everything looks great but the code provided fails with the error "The transport failed to connect to the server".

I have all the smtp details specified, can someone assist with troubleshooting?


Many thanks!

M
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2014

Commented:
Have you checked your firewall that its not blocking the port?

Author

Commented:
Yup, I can ping/trace to the gmail smtp with no issues.

Any other ideas?

Code:

Sub new_opp()
   Dim CDO_Mail_Object As Object
   Dim CDO_Config As Object
   Dim SMTP_Config As Variant
   Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String

   Email_Subject = "New Opportunity Registration"
   Email_Send_From = "doh@doh.com"
   Email_Send_To = "doh@doh.com"
   Email_Cc = "doh@doh.com"
   Email_Bcc = "doh@doh.com"

   For i = 1 To Range("A1:B18").Rows.Count
      Email_Body = Email_Body & Range("A" & i).Value & "," & Range("B" & i).Value & vbNewLine
   Next

   Set CDO_Mail_Object = CreateObject("CDO.Message")

   On Error GoTo debugs
   Set CDO_Config = CreateObject("CDO.Configuration")
        CDO_Config.Load -1
        Set SMTP_Config = CDO_Config.Fields
   With SMTP_Config
      .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") = "doh@doh.com"
      .Item("http://schemas.Microsoft.Com/cdo/configuration/sendpassword") = "fakepw"
      .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      'Put your server name below
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
      .Update
   End With
       
   With CDO_Mail_Object
      Set .Configuration = CDO_Config
   End With

   CDO_Mail_Object.Subject = Email_Subject
   CDO_Mail_Object.from = Email_Send_From
   CDO_Mail_Object.To = Email_Send_To
   CDO_Mail_Object.TextBody = Email_Body
   CDO_Mail_Object.cc = Email_Cc                      'Use if needed
   CDO_Mail_Object.bcc = Email_Bcc                    'Use if needed
   'CDO_Mail_Object.AddAttachment FileToAttach        'Use if needed
   CDO_Mail_Object.send

debugs:
If Err.Description <> "" Then MsgBox Err.Description

End Sub

Author

Commented:
Well, yes and no.

My intention was to have many other people with different configurations be able to submit this form with no additional configuration, is that not possible?

My assumption was that the code above would use the gmail smtp to send the email (unless blocked).

If not, is there another route?
Solutions Consultant
Commented:
It does not matter who uses the form but rather that that gmail account being used to send the email have POP enabled. Once this has been set up it the form should send email with no further configuration required (assuming no local issues eg firewalls)

Due to the fact that it is so easy to access VBA scripts, even when password protected, that I would set a gmail account specifically for the purposes of this script. That way if the login details are compromised it will minimise the possible harm.

Michael

Author

Commented:
Hit the mark. Many thanks!

Author

Commented:
Tested with all possible settings - ports, servers, different email accounts.

Still not working.  Can you recommend anything else I can try, please?

I'm desperate!


Many thanks,

Brian