sending email using macros

[s99999999s2003]

hi
How can i send email to a lotus notes server or other email servers
using excel macro by clicking a button.
I read in the newsgroups to use SendKeys or CDO but is there any
examples on how to do that?
thanks

 

[Ron de Bruin]

Hi

I have CDO examples on my site
http://www.rondebruin.nl/cdo.htm

I believe the Body Outlook Express examples are also working with Notes
http://www.rondebruin.nl/mail/oebody.htm

 

[AndyOne]

Ron, can you help me to get started with this..?

From this part on I´m totally confused.

"Sub Message()
' This example use late binding, you don't have to set a reference
' You must be online when you run the sub
Dim iMsg As Object
Dim iConf As Object
Dim cell As Range
' Dim Flds As Variant"

Can you tell me what to press and where to insert all this stuff..??

Thanks!

 

[Ron de Bruin]

Hi Andy

You must copy the macro in a module in your workbook

We use this test macro

Fill in your server in this macro in this line
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here"
And your e-mail address in this line
.To = "(e-mail address removed)"


Sub Mail_CDO()
Dim iMsg As Object
Dim iConf As Object
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
With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = """Ron"" <[email protected]>"
.Subject = "Important message"
.TextBody = "Hi there" & vbNewLine & vbNewLine & _
"Good evening"
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
End Sub

First we open the VBA editor with Alt-F11
Then we use Insert>Module
Copy/Paste the macro in this module (change the address in the macro to yours(for testing))
Now we use Alt-Q to go back to Excel


Then In Excel we use Alt-F8 to get the list with macro's
Select the macro and press Run

Post back if you need more help

 

[AndyOne]

Hey Ron;

thanks for your help.

Do I have to (and how) save the macro before going back to excel?
Because; if not, it does not appear on the list of macros.

Andy

 

[Ron de Bruin]

Because; if not, it does not appear on the list of macros.

Try it again

First we open the VBA editor with Alt-F11
Then we use Insert>Module
Copy/Paste the macro in this module (change the address in the macro to yours(for testing))
Now we use Alt-Q to go back to Excel

It is in the Alt-F8 list now
If you save the file you will save the macro also

 

[AndyOne]

Hey,

so far so good. But when running the macro an error occurs. In the code
it highlights ".Send". In one of the last lines of the code.

Do you whats wrong?

Thanks a lot so far!

 

[Ron de Bruin]

Hi Andy

Are you online ?
Do you use Win 2000 or Win XP ?
Have you fill in your smtp server in the code ?

 

[AndyOne]

Ron;

yes, I am online and I filled in the server as you told me to.

I am using Windows XP.

 

[Ron de Bruin]

Hi Andy

Maybe your IT department or firewall block it