M
meiwr9
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") = "(e-mail address removed)"
.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 there: 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 = "(e-mail address removed)"
.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
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") = "(e-mail address removed)"
.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 there: 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 = "(e-mail address removed)"
.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