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
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
