Help with VBA code for email without Outlook

[Perpa]

I searched and followed the following link on Mr Excel:
http://www.mrexcel.com/forum/excel-questions/649148-email-userform-without-outlook.html

Refers to: email without outlook: http://www.rondebruin.nl/cdo.htm
Which provided code and tips which I have tried...

In the line of code below I use my correct 'password':
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"

I have tried both '25' and '465' in the following line of code:
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
'465' is the correct port, but I get the following VB error message:

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

The code appears to run fine right up until the error message appears. When I look at 'Debug'
to see where the code stopped it is on the line...'.Send' in the 'With' statement.
I have tried using Cox and gmail with the same result.

Have I missed something in the code, or where else might I look to sort this out?
My goal is to get this code to work, then modify it to send a pdf attachment.
I am using Windows 7 and Excel 2007.
Thank you in advance for any help.
Perpa

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
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "capopat48@gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
        .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 = "perpa48@gmail.com"
        .CC = ""
        .BCC = ""
        .From = "capopat48@gmail.com"
        .Subject = "New figures"
        .TextBody = strbody
        .Send
    End With

End Sub

 

[yky]

Please check out the following thread.

http://www.mrexcel.com/forum/excel-...cations-send-excel-worksheet-list-people.html

You seem to be missing one line:

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

 

[mas123]

Note : that you must also enable the "Less Secure" option for GMail
https://www.google.com/settings/security/lesssecureapps

 

[Perpa]

yky
I am using Cox for my ISP, but I also have Gmail.
I tried using the code you sent in Cox "smtp.coxmail.com"
I entered the line of code you suggested and tried it in two locations but received the same error code both times:

Error Code: "Run-time error '-214722095 (80040211)':
The message could not be sent to the SMTP server. The transport error code was 0x80040217.
The server response was not available."

This error came up right away, no delay as with the previous error message.

The first try was the blank line, the second try is highlighted in red:

    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        [COLOR="#FF0000"].Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True[/COLOR]
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.coxmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        '
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username@cox.net"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword"
        .Update
    End With

mas123 - I will try your suggestion using the less secure Gmail option and see if that works. However I would like to
get this Cox portion working if possible.

Thank you both so much for your prompt responses and suggestions.

Perpa

 

[Perpa]

mas123 - I tried gMail with the new line from yky and it worked without using the less secure option.

I would still like to get the' SMTP.coxmail.com' working if possible. Any ideas??

Thank you both for looking at this. Now at least I have the gmail option working without Outlook!
That is a BIG help. Many, Many thanks.
Perpa

 

[yky]

Perhaps you need to ask your ISP.

 

[Perpa]

yky,
I'll do that. Thank you for your help, and also thanks to mas123.
Perpa

 

[Perpa]

yky,
I did some digging on the Cox website and it turns out that

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.coxmail.com"
Should be:
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.cox.net"

And the correct port is 25:
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

I made those two changes and it worked like a charm!

Thanks again for your help!

Perpa

 

[yky]

Glad it works for you now.