Automating References 1

[kamfl610]

Hey,

Got a question, I am in Excel and my macro is creating an e-mail in Outlook. Unfortunately, I have users who are using two versions of Outlook so I have versioning problems. i'm wondering without having to create two versions of this Excel Spreadsheet, how I can set the Outlook reference automatically for the users so I don't get errors. Much appreciated. Thanks!!

 

[ssVBAdev]

If you know the coding for both version of Outlook then just write two different Subs for each version. Then, test for the Outlook version then run the appropriate sub based on the version.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[kamfl610]

I need the coding on how to automate the reference in the macro. I have tried the VBProject.Reference.Remove but it keeps telling me that it doesn't understand. So when 97 user opens it and then tries to e-mail, the reference is set to Outlook Object 10.0 because an XP user has been in it. So when they try to e-mail, it blows out because the reference is missing since 97 users don't use Outlook 2002, so the macro blows. I need to remove and set the Reference regardless of a 97 user or an XP user.

 

[kamfl610]

found it. thanks anyway!!

 

[ssVBAdev]

It's polite to post your solution so that others who read this thread later can view what the solution is.

********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!

 

[kamfl610]

Sorry, was in a hurry and forgot to post the answer. Here it is:

**I did this routine when the workbook opened so the references were set. I knew what position the Excel reference is, which is simple enough to do when you have the VBA editor up, and look at references. From there, you know what position the Outlook reference is.
Sub Workbook_Open()
Dim strReference As String
Dim strExcelReference As String
On Error Resume Next
strExcelReference = ThisWorkbook.VBProject.References(2).Description
'* here I know my excel reference description lies at the second position. Your's maybe different. My shop runs Office 97 with Outlook 2000 for some users and others are Office XP users. So they are different versions. If the reference is different, I just go to where I can pickup the correct reference in office.
If strExcelReference = "Microsoft Excel 8.0 Object Library" Or _
strExcelReference = "Microsoft Excel 9.0 Object Library" Then
'* this is where the reference resides.
strReference = _
"c:\program files\microsoft office\office\msoutl9.olb"
Else
strReference = _
"c:\program files\microsoft office\office10\msoutl.olb"
End If
With ActiveWorkbook.VBProject.References
'* here I remove the old reference with the new reference
'* sort of like refreshing
.Remove ThisWorkbook.VBProject.References(6)
.AddFromFile strReference
End With
End Sub

Hope this helps & if you'd like to add to it, that's fine. It works for me.