Link to home
Start Free TrialLog in
Avatar of Marv1n
Marv1nFlag for Afghanistan

asked on

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
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you checked your firewall that its not blocking the port?
Avatar of Marv1n

ASKER

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
Avatar of Marv1n

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Michael Fowler
Michael Fowler
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Marv1n

ASKER

Hit the mark. Many thanks!
Avatar of Marv1n

ASKER

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