Different versions of Excel is messing up my macro

StevePS

New Member
Joined
Aug 19, 2002
Messages
7
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", , , , , , , :cool:
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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

Well-known Member
Joined
May 11, 2002
Messages
810
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

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

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

New Member
Joined
Aug 19, 2002
Messages
7
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

Well-known Member
Joined
Jul 27, 2002
Messages
1,920

ADVERTISEMENT

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

New Member
Joined
Jun 5, 2002
Messages
12
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

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
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.
 

Forum statistics

Threads
1,148,280
Messages
5,745,823
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top