Excel 2000 / 2003 Object Library problem 1

[Skyshadow5]

A spreadsheet containing VBA created in Excel 2000 references the MSOUTL9.OLB object library. When the spreadsheet needed an update done the VBA was edited on a PC running Excel 2003 and when saved it kept the references to xx11xx libraries. When the spreadsheet was then opened by users running Excel 2000 the scripting failed as it was "missing" the version 9 object libraries. It would appear that Excel 2003 "removes" the version 9 library files.
My question - is it possible to have Excel 2003 leave the version 9 references alone, allowing a user running either Excel 2000 or Excel 2003 to update the VBA, without causing a user running only Excel 2000 to have the script fail on them when using the spreadhseet?
I hope I've explained this well enough, if not I'll try and gather some evidence and error messages.

 

[timfoster]

Not sure if this would work as the older libraries may not exist on the machine. You could always write some code to check for the existence of the correct library on open. I remember I wrote something similar a while back. It ended up being more of a pain than it was worth. I think I ended up telling the client to sort their infrastructure so that everyone used the same versions.

 

[Skyshadow5]

Thanks Tim, I agree. Would be so much easier if everyone used Office 2003; I'm working on it.

 

[HughLerwill]

I suspect the OutLook object you are using in your code is Early Bound, have you tried Late Binding which should;
1) do away with the need for a Project Reference and
2) make the code runnable under various versions of Office

 

[Skyshadow5]

Hugh, I'm not that well versed with the process you describe, could you explain further please.

 

[Skyshadow5]

Hugh,
This is what is currently in the script.

Dim olApp As Outlook.Application
Dim olMail As MailItem

=====================================================

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = strEmailAdd
'.CC = If required
.Subject = "XXX"
.Attachments.Add strEmailPath
.Body = vbCr & "xxx"
.Display '.Send
End With

Set olMail = Nothing
Set olApp = Nothing

=====================================================

Can you expand on what I'd need to add, thanks.

 

[HughLerwill]

'Dim olApp As Outlook.Application
'Dim olMail As MailItem
Dim olApp As Object
Dim olMail As Object
'=====================================================

'Set olApp = New Outlook.Application
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = strEmailAdd
'.CC = If required
.Subject = "XXX"
.Attachments.Add strEmailPath
.Body = vbCr & "xxx"
.Display '.Send
End With

Set olMail = Nothing
Set olApp = Nothing

 

[HughLerwill]

A couple of points I overlooked;

Add the following to the top of my previous code;

Const olMailItem = 0

and remove/ untick the Project Reference to the Excel Library.

 

[Skyshadow5]

Thank you Hugh, you're a star.

I guess when you know, you know. I've added the code and it works a treat (and commented you at the end of each change/addition, credit to you, not me bud).

I can use this in some of my other projects as well, thanks again.

 

[HughLerwill]

Thanks for the star.

You may be able to abbreviate the code to just this;

With CreateObject("Outlook.Application").CreateItem(0)
.To = strEmailAdd
'.CC = If required
.Subject = "XXX"
.Attachments.Add strEmailPath
.Body = vbCr & "xxx"
.Display '.Send
End With

 

[Skyshadow5]

I'll give it a try and let you know, thanks Hugh.