Sending mail from Excel with CDO by Ron de Bruin

  • Thread starter Thread starter Mumbai WH
  • Start date Start date
M

Mumbai WH

It seems that my 1st post is not appearing in group so posting again.

I am using CDO code for sending mail from excel given by Ron de Bruin
at
http://www.rondebruin.nl/cdo.htm
1st code on page.

I am getting error as below.

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

using Excel 2007 and outlook 2007.

Pl help.

Regards,
Madiya
 
It seems that my 1st post is not appearing in group so posting again.

I am using CDO code for sending mail from excel given by Ron de Bruin
at
http://www.rondebruin.nl/cdo.htm
1st code on page.

I am getting error as below.

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

using Excel 2007 and outlook 2007.

Pl help.

Regards,
Madiya
Click to expand...


Madiya

did you read this section:

Read this!!!

This code will not work in Win 98 and ME.
You must be connected to the internet when you run a example.

It is possible that you get a Send error when you use one of the examples.
AFAIK : This will happen if you haven't setup an account in Outlook
Express or Windows Mail.
In that case the system doesn't know the name of your SMTP server.
If this happens you can use the commented green lines in each example.
Don't forget to fill in the SMTP server name in each code sample where
it says "Fill in your SMTP server here"

When you also get the Authentication Required Error you can add this
three lines.
..Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
"username"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
"password"

Don't remove the TextBody line in the code. If you do you can't open
the attachment (bug in CDO).
If you don't want to have text in the body use this then .TextBody = ""

Note: It is always possible that your firewall block the code (Check
your firewall settings)


As you have not posted the actual code you are using and any references
it is difficult to pinpoint the problem.

HTH
Mick.
 
Madiya

did you read this section:

Read this!!!

  This code will not work in Win 98 and ME.
  You must be connected to the internet when you run a example.

  It is possible that you get a Send error when you use one of the examples.
AFAIK : This will happen if you haven't setup an account in Outlook
Express or Windows Mail.
In that case the system doesn't know the name of your SMTP server.
  If this happens you can use the commented green lines in each example..
Don't forget to fill in the SMTP server name in each code sample where
it says "Fill in your SMTP server here"

  When you also get the Authentication Required Error you can add this
three lines.
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") =
"username"
  .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") =
"password"

  Don't remove the TextBody line in the code. If you do you can't open
the attachment (bug in CDO).
  If you don't want to have text in the body use this then .TextBody = ""

Note: It is always possible that your firewall block the code (Check
your firewall settings)

As you have not posted the actual code you are using and any references
it is difficult to pinpoint the problem.

HTH
Mick.
Click to expand...

Yes. I have already made that addition but still facing the same
error.
I have also tried to change the port number from 25 to 465 but still
same error.
Here is the code I am using.
=====================
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") = "(e-mail address removed)"
.Item("http://schemas.microsoft.com/cdo/configuration/
sendpassword") = "123456789"
.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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = "(e-mail address removed)"
.Subject = "New figures CDO MAIL"
.TextBody = strbody
.Send
End With

End Sub
=======================
Pl help.

Regards,
Madiya
 
Yes. I have already made that addition but still facing the same
error.
I have also tried to change the port number from 25 to 465 but still
same error.
Here is the code I am using.
=====================
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") = "(e-mail address removed)"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "123456789"
.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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = "(e-mail address removed)"
.Subject = "New figures CDO MAIL"
.TextBody = strbody
.Send
End With

End Sub
=======================
Pl help.

Regards,
Madiya
Click to expand...

This post mentions port 587

http://forums.asp.net/p/1235845/4145828.aspx/1?Re+Sending+Email+smtp+gmail+com+Net+Mail+SmtpClient+
---

This is slightly different, it worked for the author:

Dim lobj_cdomsg As CDO.message
Set lobj_cdomsg = New CDO.message
With lobj_cdomsg.Configuration
..Fields(cdoSMTPServer) = "smtp.gmail.com"
..Fields(cdoSMTPConnectionTimeout) = 60
..Fields(cdoSendUsingMethod) = cdoSendUsingPort
..Fields(cdoSMTPServerPort) = 465
..Fields(cdoSMTPAuthenticate) = True
..Fields(cdoSMTPUseSSL) = True
..Fields(cdoSendUserName) = "(e-mail address removed)"
..Fields(cdoSendPassword) = "xxxxx"
..Fields.Update
End With

http://www.codeguru.com/forum/showthread.php?t=461327

Mike
 
This post mentions port 587
http://forums.asp.net/p/1235845/4145828.aspx/1?Re+Sending+Email+smtp+gmail+com+Net+Mail+SmtpClient+
Click to expand...

Here's google on port 587:

http://mail.google.com/support/bin/answer.py?hl=en&answer=13287

Outgoing Mail (SMTP) Server - requires TLS3 or SSL:
smtp.gmail.com (use authentication)
Use Authentication: Yes
Port for TLS/STARTTLS: 587
Port for SSL: 465

TLS: TLS (Transport Layer Security) is a way of changing data such as
your username and password into code as it travels across the Internet,
so that the data will be secure and private. With mail delivery, TLS
begins with an unsecured connection to the mail servers, and then
upgrades to a secure connection once information is sent.
 
Back
Top