VBA Excel - Add references programmatically

1
votes

I built a excel macro using the "Microsoft Outlook 15.0 Object Library" reference, but when I send the file to other people they get this error: "Can’t find project or library".

This file will be used by a lot of people, so I have to add it from VBA Code.

I'm using this code that runs when you open the excel file, which is returning the following error: "Error in loading DLL"

Private Sub Workbook_Open()
       Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (x86)\Microsoft Office\Office15\MSOUTL.OLB\"
End Sub

Do you have any idea why this is happening?

Thanks

2
excelvbareference
Because you are early binding. Use late binding. It is not necessary that you and the other person is using the same version of MS Office. – Siddharth Rout
Probably because the other person uses another version of Office (not 15.0). If this is the case then use Late Binding (works without references) instead of Early Binding (uses references). See excelmatters.com/2013/09/23/… – Pᴇʜ
[From the comment below my deleted answer]... Would you be distributing mscomct2.ocx with the excel file? If yes then you will have to get an elevated registration of mscomct2.ocx. This ocx is not native to VBA environment. It is a VB6 control and should be avoided in such a case. – Siddharth Rout
Quick Question: Why are you even using mscomct2.ocx? – Siddharth Rout
You might be interested in How can I create a calendar input in VBA Excel? which comes with pure VBA. – Pᴇʜ

2 Answers

2
votes

If you still want to keep the early binding you could use the GUID in order to add the libray, i.e. 

Sub AddOL_Ref()

Application.VBE.ActiveVBProject.REFERENCES. _
    AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 1, 0

End Sub

Advantage of using the GUID is that it is constant and does not change with the version of the program.

Here one finds an extened discussion on dis/advantages of early resp. late binding.

0
votes

You will have to remove the references & use late binding

or you can also try this. Send your code to one of the user having 2010MS.

Goto VBA > Tools > References

Check for any missing references. You may have Microsoft Outlook 15.0 Object Library showing as missing. Un-check this, browse down and select Microsoft Outlook 14.0 Object Library.