Marv1n
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
I have all the smtp details specified, can someone assist with troubleshooting?
Many thanks!
M
Have you checked your firewall that its not blocking the port?
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.Configur ation")
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.AddAttach ment FileToAttach 'Use if needed
CDO_Mail_Object.send
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
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.Configur
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.AddAttach
CDO_Mail_Object.send
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
In gmail have you enabled POP
http://support.google.com/mail/bin/answer.py?hl=en&answer=13273
as advised here
http://support.google.com/mail/bin/answer.py?hl=en&answer=13287
Michael
http://support.google.com/mail/bin/answer.py?hl=en&answer=13273
as advised here
http://support.google.com/mail/bin/answer.py?hl=en&answer=13287
Michael
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hit the mark. Many thanks!
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
Still not working. Can you recommend anything else I can try, please?
I'm desperate!
Many thanks,
Brian