I would like to be able to include VBA code in a workbook, which will
automatically send an email message when certain criteria are
satisfied. I do not want to send a copy of a worksheet or workbook or
any other file - I just want to send a standard message to a known
internal email address.

I am using Excel 97 (SR-2) in Windows NT 4 (SP 6a)

Our company uses Netscape Communicator as our email tool. It uses an
IMAP server, (whatever that means). We are not able to send emails
using Outlook.

I have seen the advice from Ron de Bruin on using CDO to send emails,
(http://www.rondebruin.nl/cdo.htm), which does not depend on any
particular email software. Unfortunately this does not work for me. I
assume this is because I am using Windows NT? Any help would be greatly
appreciated.

The code I have is as follows. This Sub is called from another Sub when
the relevant conditions are satisfied. It generates an error at the
line indicated.


Sub Send_email()

Dim iMsg As Object
Dim iConf As Object

Application.ScreenUpdating = False

Set iMsg = CreateObject("CDO.Message") ' Error occurs on this line
Set iConf = CreateObject("CDO.Configuration")
With iMsg
Set .Configuration = iConf
.to = " ' enter real email address
here
.From = """Facility Centre weekly report generator""
" ' enter sender's email address
.Subject = "Weekly report differences"
.TextBody = "This is an automatically generated message." &
vbNewLine & vbNewLine & _
"There is at least one difference between" & vbNewLine &_
"this week's report and last week's." & vbNewLine &
vbNewLine & _
"You should open this week's report" & vbNewLine & _
"to view the differences."
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing

Application.ScreenUpdating = True

End Sub

I haven't used the code in question, but it looks like you need a reference
to the CDO library. In the VBE, click Tools | References... and scroll down
the list in the dialog which appears. The library you're looking for is
called Microsoft CDO 1.21 Library, or Microsoft CDO For Exchange 2000
Library.

One (or both) of these libraries will contain the code that is being looked
for below and causing your routine to fail.
--
HTH -

-Frank Isaacs
Dolphin Technology Corp.
http://vbapro.com



"Graham Standring" wrote in message
...
I would like to be able to include VBA code in a workbook, which will
automatically send an email message when certain criteria are
satisfied. I do not want to send a copy of a worksheet or workbook or
any other file - I just want to send a standard message to a known
internal email address.

I am using Excel 97 (SR-2) in Windows NT 4 (SP 6a)

Our company uses Netscape Communicator as our email tool. It uses an
IMAP server, (whatever that means). We are not able to send emails
using Outlook.

I have seen the advice from Ron de Bruin on using CDO to send emails,
(http://www.rondebruin.nl/cdo.htm), which does not depend on any
particular email software. Unfortunately this does not work for me. I
assume this is because I am using Windows NT? Any help would be greatly
appreciated.

The code I have is as follows. This Sub is called from another Sub when
the relevant conditions are satisfied. It generates an error at the
line indicated.


Sub Send_email()

Dim iMsg As Object
Dim iConf As Object

Application.ScreenUpdating = False

Set iMsg = CreateObject("CDO.Message") ' Error occurs on this line
Set iConf = CreateObject("CDO.Configuration")
With iMsg
Set .Configuration = iConf
.to = " ' enter real email address
here
.From = """Facility Centre weekly report generator""
" ' enter sender's email address
.Subject = "Weekly report differences"
.TextBody = "This is an automatically generated message." &
vbNewLine & vbNewLine & _
"There is at least one difference between" & vbNewLine &_
"this week's report and last week's." & vbNewLine &
vbNewLine & _
"You should open this week's report" & vbNewLine & _
"to view the differences."
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing

Application.ScreenUpdating = True

End Sub

I don't have these libraries in the list of references. I have looked through
the microsoft website and it seems that CDO will not have been installed on my
PC as I do not have any of the software that it is included with, eg Outlook
98, or Windows 2000.

Back to the drawing board I think.

Thanks for your help.

Graham Standring
University of Sunderland, UK

Frank Isaacs wrote:

I haven't used the code in question, but it looks like you need a reference
to the CDO library. In the VBE, click Tools | References... and scroll down
the list in the dialog which appears. The library you're looking for is
called Microsoft CDO 1.21 Library, or Microsoft CDO For Exchange 2000
Library.

One (or both) of these libraries will contain the code that is being looked
for below and causing your routine to fail.
--
HTH -

-Frank Isaacs
Dolphin Technology Corp.
http://vbapro.com

"Graham Standring" wrote in message
...
I would like to be able to include VBA code in a workbook, which will
automatically send an email message when certain criteria are
satisfied. I do not want to send a copy of a worksheet or workbook or
any other file - I just want to send a standard message to a known
internal email address.

I am using Excel 97 (SR-2) in Windows NT 4 (SP 6a)

Our company uses Netscape Communicator as our email tool. It uses an
IMAP server, (whatever that means). We are not able to send emails
using Outlook.

I have seen the advice from Ron de Bruin on using CDO to send emails,
(http://www.rondebruin.nl/cdo.htm), which does not depend on any
particular email software. Unfortunately this does not work for me. I
assume this is because I am using Windows NT? Any help would be greatly
appreciated.

The code I have is as follows. This Sub is called from another Sub when
the relevant conditions are satisfied. It generates an error at the
line indicated.


Sub Send_email()

Dim iMsg As Object
Dim iConf As Object

Application.ScreenUpdating = False

Set iMsg = CreateObject("CDO.Message") ' Error occurs on this line
Set iConf = CreateObject("CDO.Configuration")
With iMsg
Set .Configuration = iConf
.to = " ' enter real email address
here
.From = """Facility Centre weekly report generator""
" ' enter sender's email address
.Subject = "Weekly report differences"
.TextBody = "This is an automatically generated message." &
vbNewLine & vbNewLine & _
"There is at least one difference between" & vbNewLine &_
"this week's report and last week's." & vbNewLine &
vbNewLine & _
"You should open this week's report" & vbNewLine & _
"to view the differences."
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing

Application.ScreenUpdating = True

End Sub

Hi Graham

You use Late binding so I think you can't use it in Windows NT
I never try it in Windows NT?

It will run great in 2000 and Win XP
(Microsoft CDO for Windows 2000 Library (Exchange in Win Xp))

This Library's you must use there if you use Early binding


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Graham Standring" wrote in message ...
I would like to be able to include VBA code in a workbook, which will
automatically send an email message when certain criteria are
satisfied. I do not want to send a copy of a worksheet or workbook or
any other file - I just want to send a standard message to a known
internal email address.

I am using Excel 97 (SR-2) in Windows NT 4 (SP 6a)

Our company uses Netscape Communicator as our email tool. It uses an
IMAP server, (whatever that means). We are not able to send emails
using Outlook.

I have seen the advice from Ron de Bruin on using CDO to send emails,
(http://www.rondebruin.nl/cdo.htm), which does not depend on any
particular email software. Unfortunately this does not work for me. I
assume this is because I am using Windows NT? Any help would be greatly
appreciated.

The code I have is as follows. This Sub is called from another Sub when
the relevant conditions are satisfied. It generates an error at the
line indicated.


Sub Send_email()

Dim iMsg As Object
Dim iConf As Object

Application.ScreenUpdating = False

Set iMsg = CreateObject("CDO.Message") ' Error occurs on this line
Set iConf = CreateObject("CDO.Configuration")
With iMsg
Set .Configuration = iConf
.to = " ' enter real email address
here
.From = """Facility Centre weekly report generator""
" ' enter sender's email address
.Subject = "Weekly report differences"
.TextBody = "This is an automatically generated message." &
vbNewLine & vbNewLine & _
"There is at least one difference between" & vbNewLine &_
"this week's report and last week's." & vbNewLine &
vbNewLine & _
"You should open this week's report" & vbNewLine & _
"to view the differences."
.Send
End With

Set iMsg = Nothing
Set iConf = Nothing

Application.ScreenUpdating = True

End Sub