Email/CDO/VBA

[Luis]

Hi
I work with the Office 2003 and with Outlook in Exchange , I created a
macro in Excel(VBA) that send email, the only problem is that Outlook
block the send with a message (a program is send mail with the command
Item.Send)
Exists some form of bypass this bar without the human intervention?
I already read about it and in the Outlook 2003 haven't the posibiliti
to deactivate this, and there are authors that say that to bypass this
bar use CDO from the Microsoft , but my server don´t allow the use of
POP and SMTP.

Thank´s

PS. Sorry for mi bad english

Barreiro

 

[Guest]

Check out this link...

http://www.rondebruin.nl/cdo.htm

 

[Guest]

Just an observation:
Ron says:

Briefly to explain, this code builds the message and drops it
in the pickup directory, and SMTP service running on the machine
picks it up and send it out to the internet.

I wonder if that will be problematic based on the OP saying:

 

[Guest]

Absolutely... I posted that link as it is everytihing you want to know about
CDO. If I said that the server restriction was an issue then that is just one
opinion. Ron's site is the authority on these matters and that site says it
better than I ever could. Not to mention I am really lazy and it's easiest to
just post a link...

Point take however. I should have mentioned the restriction. I should also
mention that you want to careful with CDO code. I was once debugging some CDO
code that sent emails from a loop. I accidentally sent myself about 1500
emails...

 

[Guest]

Jim,

Ahah I see. You wanted the OP to confirm for himself that CDO wasn't the
answer. Very astute.

 

[Ron de Bruin]

See
http://www.rondebruin.nl/mail/prevent.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Hi
I work with the Office 2003 and with Outlook in Exchange , I created a
macro in Excel(VBA) that send email, the only problem is that Outlook
block the send with a message (a program is send mail with the command
Item.Send)
Exists some form of bypass this bar without the human intervention?
I already read about it and in the Outlook 2003 haven't the posibiliti
to deactivate this, and there are authors that say that to bypass this
bar use CDO from the Microsoft , but my server don´t allow the use of
POP and SMTP.

Thank´s

PS. Sorry for mi bad english

Barreiro

 

[Luis]

ok,

Thank´s for the answers, but the link mentioned it was my first
research place.
I still don´t have the answer for my prob, i´m stuck with the security
off Outlook

Regard´s

Barreiro

 

[Luis]

Thank´s Ron,

I also have the opportunity to see the link you send previous. But i
don´t mention before that my pc at work don´t allow 3´s part software
installation, so the Express ClickYes and the Outlook Redemption don´t
apply to me.

I like the code mention

..Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

but i don´t know where to apply in the example of your site, if you
can give me a help...

Thank´s one more time (is not every day that a expert give us the
opportunity to learn the a, b, c, d,... of Excel)

Barreiro

 

[Ron de Bruin]

Hi Luis

From the page

***************************************************************
Instead of .Send in the code examples you can use this three lines instead of .Send
( SendKeys is not always reliable and this will not work on every computer)
Note: the S is from Send, if you not use a English version you must change this letter.
You can only use this if you use the Outlook object model examples from my site.

..Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"
**************************************************************

Replace .Send in the macro with the three lines above

If you have one machine with Office 2007 you can send mail without the security warning
Maybe a option ?

See the link to the KB on my site


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Thank´s Ron,

I also have the opportunity to see the link you send previous. But i
don´t mention before that my pc at work don´t allow 3´s part software
installation, so the Express ClickYes and the Outlook Redemption don´t
apply to me.

I like the code mention

..Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

but i don´t know where to apply in the example of your site, if you
can give me a help...

Thank´s one more time (is not every day that a expert give us the
opportunity to learn the a, b, c, d,... of Excel)

Barreiro

 

[Luis]

Hi Ron,

Regarding the code that you wroute I get the error msg "the value of
the configuration "SendUsing" is invalid"!
I post the code that i run in a module

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") _
' = "Fill in your SMTP server here"
' .Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 25
' .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 = "guim****"
.CC = ""
.BCC = ""
.From = "guim****"
.Subject = "Important message"
.TextBody = strbody
.Send
End With

End Sub


What I doing wrong?

Thanks
Luis

 

[Ron de Bruin]

Hi Luis
Not reading the text on the site <vbg>
http://www.rondebruin.nl/cdo.htm

Read this above the macro that you use

But if you not have a account in Outlook Express or Windows Mail you also need the commented
green lines in the code. Remove every ' before every green line and fill in the name of your SMTP server
where it says "Fill in your SMTP server here"

 

[Luis]

Ron,

Sorry for mi persistence, but now i have the error "Run-time error
-2147220973(80040213): Error in the connection to the carrier off the
server".!
The code that i use is
_____________________

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/
smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/
sendusername") = "lmbarre***************"
.Item("http://schemas.microsoft.com/cdo/configuration/
sendpassword") = "*******"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserver") _
= "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 25
.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 = "lmbarreiro*****"
.CC = ""
.BCC = ""
.From = """Ron"" <[email protected]>"
.Subject = "Important message"
.TextBody = strbody
.Send
End With

End Sub
_____________________

Can you help me? Like I said before, my pc use the outlook 2003 in
exchange mode, with Windows NT, and i´m not the admin (is for my
office use), can I in any way transposed the prob?

Thank´s
Luis

 

[Ron de Bruin]

Not working with gmail as far as I know.

Ask the name of the smtp server of the Internet provider on your work

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


Ron,

Sorry for mi persistence, but now i have the error "Run-time error
-2147220973(80040213): Error in the connection to the carrier off the
server".!
The code that i use is
_____________________

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/
smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/
sendusername") = "lmbarre***************"
.Item("http://schemas.microsoft.com/cdo/configuration/
sendpassword") = "*******"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserver") _
= "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/
smtpserverport") = 25
.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 = "lmbarreiro*****"
.CC = ""
.BCC = ""
.From = """Ron"" <[email protected]>"
.Subject = "Important message"
.TextBody = strbody
.Send
End With

End Sub
_____________________

Can you help me? Like I said before, my pc use the outlook 2003 in
exchange mode, with Windows NT, and i´m not the admin (is for my
office use), can I in any way transposed the prob?

Thank´s
Luis

 

[Aaron Langlo]

I encountered this issue in past projects and found a relatively simple solution...

Simply include a public sub/function (to send the mail) in the code for the ThisOutlookSession object. The procedure can be called from any other module within Outlook. But you MUST generate the olMailItem object and send it from within the ThisOutlookSession object's code.

Here's a simple example:
==========================================
Public Sub SendEmail(strFrom As String, strTo As String, strSubj As String, strBody As String)
Dim miMsg As Outlook.MailItem

Set miMsg = Outlook.CreateItem(olMailItem)

With miMsg
.SentOnBehalfOfName = strFrom
.To = strTo
.Subject = strSubj
.Body = strBody
.Send
End With

Set miMsg = Nothing

End Function

 

[Aaron Langlo]

I encountered this issue in past projects and found a relatively simple solution...

All you need to do is inclue a public sub/function (to send the mail) in the code for the ThisOutlookSession object. The procedure can be called from any other module within Outlook. But you MUST generate the olMailItem object and send it from within the ThisOutlookSession object's code.

Here's a simple example:
==========================================
Public Sub SimpleEmail(strFrom As String, strTo As String, strSubj As String, strBody As String)
Dim miMsg As Outlook.MailItem

Set miMsg = Outlook.CreateItem(olMailItem)

With miMsg
.SentOnBehalfOfName = strFrom
.To = strTo
.Subject = strSubj
.Body = strBody
.Send
End With

Set miMsg = Nothing

End Function