Sending Email in Excel with VBA, no Outlook, 64 bit

Question

I am using the code below to try and send an email in Excel using VBA. I keep getting an error:

Run-time error '-2147220973 (80040213)': The transport failed to connect to the server.

I have tried changing ports (25, 465, 587), changing email servers (smtp.gmail.com, smtp.live.com), tried both gmail and Hotmail, enabled less secure on both gmail accounts, even setup Outlook and disabled the firewall in Avast.

Here's the code:

Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant



    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 '587 '465 '25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'cdoBasic
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xyz@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "123456"
        .Update
    End With

    strbody = "Hi there" & vbNewLine & vbNewLine & _
        "This is line 1" & vbNewLine & _
        "This is line 2" & vbNewLine & _
        "This is line 3" & vbNewLine & _
        "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "charlie@gmail.com"
        .CC = ""
        .BCC = ""
        .From = "xyz@gmail.com"
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With
End Sub

I now am beginning to think it has to do with the fact I'm using Windows 10 64 bit and Office 2016 64 bit. - Just a hunch, also, I'm new to 64 bit, love it, but am learning.

Can some one advise if I'm on the right track and how I can go about getting this to fly. If I'm not on the right track, what else might be the hangup?

I searched the Search Q&A but haven't found anything specific.

Run time Error is network related error, cannot connect to the specified mail server. try using ssl connection, see my answer it should work. — 0m3r, Aug 29 '16 at 21:28

score 0 · Answer 1 · edited Aug 18 '16 at 09:32

0

At Google's web site for GMail you have to turn this feature on for CDO to work.

At your Gmail page click:

Settings - Accounts and Import - Other Google Account Settings - [At very bottom of page] Allow less secure apps.

Also from memory you have to click a link in an email the first time you use it (it's been a few years).

edited Aug 18 '16 at 09:32

Kurt Van den Branden

11,995
10
76
85

answered Aug 18 '16 at 07:41

Kurt - Thanks for your reply. I double checked the less secure app settings on both accounts, both are set to less secure. I sent an email from one to the other, on both accounts, then opened the emails (I assume this is what you mean by "click the link"), then ran the code. Same results. – awsmitty Aug 18 '16 at 16:13

score 0 · Answer 2 · answered Aug 29 '16 at 21:30

0

Run time Error is network related Error, cannot connect to the specified mail server, try using ssl connection = True

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

answered Aug 29 '16 at 21:30

0m3r

12,286
15
35
71

Om3r - Thanks for the suggestion - Tried both true and false, same error, exactly. I also have a small update in a way. I did get another code (significantly different than what's posted here) to work using Outlook, but in that I used a Hotmail account as the sender (the Hotmail account is setup with Outlook). Are we sure the above works with 64 bit? I've seen posts, somewhat related, that suggest it isn't, but I don't know that for sure – awsmitty Aug 29 '16 at 23:33
@awsmitty you don't need outlook, I just tested on `Excel 2010` using `GMail` setting. it works - Make sure From or Sender fields are the same. – 0m3r Aug 29 '16 at 23:44
Om3r - Are you using 64 bit. I really don't want to use Outlook. Let me know if you're using 64 bit OS and Office, please – awsmitty Aug 29 '16 at 23:54
I'm using Excel 2010 32-bit, I have 64 bit at home will test it later. are you on 2010? – 0m3r Aug 30 '16 at 00:03
1

no windows 10, 64 bit with Office 2016, 64 bit. Let me know what happens, please. When I Google Microsoft CDO for Windows 2000 I do see some threads suggesting that reference lib doesn't work for 64 bit and the work around is over my head. That thread I can no longer find – awsmitty Aug 30 '16 at 00:08

Sending Email in Excel with VBA, no Outlook, 64 bit

2 Answers2