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
 

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
I wish I had access to the Scripting Runtime Library here at work, but IT will not allow it. :(
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,148,276
Messages
5,745,806
Members
423,976
Latest member
vladm1010

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