Different versions of Excel is messing up my macro

[StevePS]

I need your help again!
I have coded the following macro, with help from this forum, to move an Excel spreadsheet to our office Email (Outlook)by clicking a VB button. This coding works fine on my version, Excel 2002, but when I send this to my co workers, who need to use the spreadsheet, who are using Excel 2000, I get an error message, "Compile Eror Can't find project library" at the line "olApp As Outlook.Application" in the code. Any suggestions how to modify this code to work on Version 2000? (upgrading all my coworkers to 2002 is not an option)I have tried saving my spreadsheet as a Excel 2000, nut that did not solve the problem.

Thanks in advance for your help.
StevePS

Sub SendRange()

'Sends a specified range in an Outlook message and retains Excel formatting


'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime


'Dimension variables
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String


'Select the range to be sent
On Error Resume Next
Set rngeSend = Application.InputBox("A46:I88", , , , , , ,
If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
On Error GoTo 0

'Now create the HTML file
ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:tempsht.htm", rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True


'Create an instance of Outlook (or use existing instance if it already exists
Set olApp = CreateObject("Outlook.Application")

'Create a mail item
Set olMail = olApp.CreateItem(olMailItem)

'Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile("C:tempsht.htm", ForReading)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = TStream.ReadAll

olMail.HTMLBody = strHTMLBody

olMail.Display


End Sub

 

[stevebausch]

Your library is something 10.

Their library is something 9.

You probably want to spec the lowest library version that will satisfy your needs. Of course, you need to have that library.

I think the Wizards might suggest a conditional compilation, or perhaps responding to the error message by referencing another library.

Let's see what they post on this topic, tonight.

 

[XL-Dennis]

Steve,

Connect through VBA to another program can be done in two ways, early or late binding.

(For a longer discussion about it see Ivan Motalas contribution in:
http://216.92.17.166/board/viewtopic.php?topic=8251&forum=2&start=10)

Early binding means that we setup a reference to OL Object Library and then declare the variables as part of this objectmodel:

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

In general this is the best approach. However the major drawback is when we face the situation You're in, different versions of OL.

With this case we need to use late binding, which means that
- we don´t put up a reference to the library via Tools | Reference in the VB-editor and
- we declare all objects as generic objects:

Dim olApp As Object
Dim olMail As Object

Please mail back if You need further help

Kind regards,
Dennis

 

[stevebausch]

Dennis,
Thank you! I appreciate the time you spent on your post. I have been working with Excel/VBA for a little more than a year now, and will be the first to tell you "A little knowledge is dangerous".

Thanks for setting me straight on how to deal with this situation. Please take all opportunity to correct my less-than-perfect attempts at information.

I am now thinking of my project, where I code it on my machine, with XL 2002, and it runs on XL97. The only issue I have seen is my attempts to use DatePicker, which has seen some changes from 97, to 2002.

Well, maybe another situation has come up. Unless I recompile in XL97, my 'tricky' BeforePrint routine is crashing XL97.

The question is: Is this a situation where late-binding would save the day? And how?

SteveBausch
This message was edited by stevebausch on 2002-10-11 21:28

 

[stevebausch]

Just stumbled upon the Automation 2000 help file, which covers the details of Automation.
If you can find 'auto2000.chm' on your system, take a look.

Somebody spent a lot of time on this help file, and if you have interest in Automation, this is a good place to start. With Examples.

 

[StevePS]

Steve is right, a little knowledge can be dangerous.
Please be patient with me as I am just learning VB (all on my own)
I have made the changes you suggested, by changing
Dim olApp As Outlook. Application (to)
Dim ol App As Object (and)
Dim olMail As Outlook.MailItem (to)
dim olMail As object

This worked on my 2002 version, but I got the folowing error when the spreadsheet was sent to a 2000 version, "Complile Error: Can't find project library". This error occured on the line
Set olMail = olApp.CreateItem(olmailtem)

So, I set (olmailtem) to ("olmailtem"), and my next error was on the next line,
Set FSObj = .....
where it could not find FSObj.

Also, on the 2000 version, the input box does not appear on the spreadsheet like it does on the 2002 version.

As you can see, I am really tangled up with this little application.

thanks for all you advice.
StevePS

 

[XL-Dennis]

Steve,

Following procedure works in XL 97 / 2000 / XP:

<PRE>
<FONT color=blue>Sub </FONT>SendRange()

<FONT color=#ff0000>'Microsoft Scripting Runtime
</FONT>


<FONT color=blue>Dim </FONT>olApp<FONT color=blue> As</FONT><FONT color=blue> Object</FONT>, olMail<FONT color=blue> As</FONT><FONT color=blue> Object</FONT>

<FONT color=blue>Dim </FONT>FSObj<FONT color=blue> As</FONT> Scripting.FileSystemObject, TStream<FONT color=blue> As</FONT> Scripting.TextStream

<FONT color=blue>Dim </FONT>rngeSend<FONT color=blue> As</FONT> Range, strHTMLBody<FONT color=blue> As</FONT><FONT color=blue> String</FONT>



<FONT color=blue>On Error</FONT> <FONT color=blue>Resume </FONT>Next

<FONT color=#ff0000>'Simplified.....
</FONT>
<FONT color=blue>Set </FONT>rngeSend = ActiveSheet.Range("A1:A10")

<FONT color=blue>On Error</FONT> <FONT color=blue>GoTo</FONT> 0



<FONT color=#ff0000>'Path is changed..
</FONT>
ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:sht.htm", rngeSend.Parent.Name, rngeSend.Address, xlHtml<FONT color=blue>Static</FONT>).Publish<FONT color=blue> True</FONT>



<FONT color=blue>Set </FONT>olApp = CreateObject("Outlook.Application")

<FONT color=blue>Set </FONT>olMail = olApp.CreateItem(0)



<FONT color=#ff0000>'Open the HTML file using the FilesystemObject into a TextStream object
</FONT>
<FONT color=blue>Set </FONT>FSObj =<FONT color=blue> New </FONT>Scripting.FileSystemObject

<FONT color=blue>Set </FONT>TStream = FSObj.OpenTextFile("C:sht.htm", ForReading)



<FONT color=#ff0000>'Now set the HTMLBody property of the message to the text contained in the TextStream object
</FONT>
strHTMLBody = TStream.ReadAll



olMail.HTMLBody = strHTMLBody



olMail.Display





<FONT color=blue>End Sub</FONT>


</PRE>

You need to revise it to fit Your need but not for the critical issues discusses above.

Kind regards,
Dennis

 

[SteveS5S]

Dennis,
The coding you supplied me to copy Excel to Outlook worker so well I decided to use the code on a second spreadsheet.
However, I am getting an error, Compile error: User-defined type not defined. The line of code this points to is

Sub SendRange1()
'Microsoft Scripting Runtime

Dim FSObj As Scripting.FileSystemObject,

I am using the same version of Excel, the only change is the range...
Any ideas what could be causing this ?

Thanks again.
Steve

 

[TommyGun]

You need to have a reference to the Microsoft Scripting Runtime in your workbook.

To do this:

1. Open VBE (Alt+F11)
2. Tools Menu
3. References
4. Find "Microsoft Scripting Runtime"

Once selected, this should allow your code to proceed.

 

[SteveS5S]

Your suggestion worked! Thanks for the help.