About using CDO to send email within Excel
 

Hello Excel Experts:

Urgent help is required from you IT guys. I need to send emails
silently within Excel using VBA, and the following code doesn't work
for me (the code is found from PaulSadowski.com):

Sub Mail_Small_Text_CDO()
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/
smtpauthenticate") = cdoBasic
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/
sendusername") = "
.Item("http://schemas.microsoft.com/cdo/configuration/
sendpassword") = "mypassword"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpconnectiontimeout") = 60

.Update
End With
'flds.Fields("urn:schemas:httpmail:importance") = 2
'flds.Fields("urn:schemas:mailheader:X-Priority") = 1
Flds.Update

strbody = "Hi the testing from CDO" & 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 = "
.CC = ""
.BCC = ""
.From = "Steineke"
.Subject = "Important message from Excel CDO"
.TextBody = strbody
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing
End Sub

The intention of above sub is to compose an email and send it from
Gmail SMTP server through Excel VBA to my yahoo's email account. The
Excel workbook is running in a machine which is connected to internet
via either dial up or intranet connection, but without any email
client program installed (at least we don't care). The fwllowing error
code is displayed when .send is executed:

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

I do have a gmail account and a yahoo account. I checked gmail help
about how to set the settings for sending and receiving emails. The
computer is connected to internet occasionally through dial-up
connection, and we want the VBA to send an email with some data back
to the headquarter without much intervention of human beings.

How to get it work? I'm not a good programmer with some knowledge
about VB. Your comments are highly appreciated.

Ken

About using CDO to send email within Excel
 

On Jun 7, 9:34 am, wrote:
Hello Excel Experts:

Urgent help is required from you IT guys. I need to send emails
silently within Excel using VBA, and the following code doesn't work
for me (the code is found from PaulSadowski.com):

Sub Mail_Small_Text_CDO()
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/
smtpauthenticate") = cdoBasic
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/
sendusername") = "
.Item("http://schemas.microsoft.com/cdo/configuration/
sendpassword") = "mypassword"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpconnectiontimeout") = 60

.Update
End With
'flds.Fields("urn:schemas:httpmail:importance") = 2
'flds.Fields("urn:schemas:mailheader:X-Priority") = 1
Flds.Update

strbody = "Hi the testing from CDO" & 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 = "
.CC = ""
.BCC = ""
.From = "Steineke"
.Subject = "Important message from Excel CDO"
.TextBody = strbody
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing
End Sub

The intention of above sub is to compose an email and send it from
Gmail SMTP server through Excel VBA to my yahoo's email account. The
Excel workbook is running in a machine which is connected to internet
via either dial up or intranet connection, but without any email
client program installed (at least we don't care). The fwllowing error
code is displayed when .send is executed:

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

I do have a gmail account and a yahoo account. I checked gmail help
about how to set the settings for sending and receiving emails. The
computer is connected to internet occasionally through dial-up
connection, and we want the VBA to send an email with some data back
to the headquarter without much intervention of human beings.

How to get it work? I'm not a good programmer with some knowledge
about VB. Your comments are highly appreciated.

Ken

Ken,

I recently got this code to work correctly...I found it off the
same site to but it was missing a few parts...

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")
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/
smtpauthenticate") = 1
.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/
sendusername") = "insertusernamehere"
.Item("http://schemas.microsoft.com/cdo/configuration/
sendpassword") = "insertpasswordhere"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpusessl") = True
.Update
End With


With iMsg
Set .Configuration = iConf
.To = Sheets("Mech").Range("AF54").Value
.CC = ""
.BCC = ""
.From = """John Doe"" "
.Subject = ""
.TextBody = "Working in Excel VBA can be very interesting..."
.send
End With

Set iMsg = Nothing
Set iConf = Nothing
End Sub