Reference Library Problem

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hi all,
I wonder if you could steer me in the right direction.....

I originally created a number of excel VBA forms in Excel 2007. My system had to be upgraded and I now use Office 2010. I have to regularly update these forms with new functionality for my team, all are using office 2007. All forms use a an MS WORD module and an OUTLOOK module. Whenever the users open the forms after an update they get a debug error - which means I have to pop round to each of them and change the reference library entries for Outlook and Word.

Is there a way that this can either be done automatically, or a way I can save as a 2007 version?
 

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.
You should late bind the code if you can't develop on a 2007 machine - then you won't have to worry about references at all.
 
Upvote 0
Essentially it means that you:
Remove the references to Word and Outlook
Declare any object variables you use from these libraries as simply Object rather than Word.Document or Outlook.Application for example
Use Createobject instead of Set... = New...
Declare any constants that you use from those libraries (eg wdPasteText)

If you can post one of your routines, I can demonstrate how it would look late bound.
 
Upvote 0
Hi Rory, that is very kind. Much appreciated....here is the Outlook routine:

Code:
 Private Sub cmdAdd_Click()
    'This calls the send mail module that will send an email to the line manager to let them know that they can complete their section.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
     
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
     
    Set OutMail = OutApp.CreateItem(olMailItem)
     
    With OutMail
        .To = Me.Reg5.Column(1)
        .Subject = "Level 1 Commentary Completed"
        .Body = "Hi," + " " + Me.Reg5.Column(1) + " " + "Please complete your Level 2 Commentary for the Weekly Report."
        .Send
    End With
     
    Set OutMail = Nothing
    Set OutApp = Nothing
 
Upvote 0
It would be (changes highlighted):
Rich (BB code):
Private Sub cmdAdd_Click()
    'This calls the send mail module that will send an email to the line manager to let them know that they can complete their section.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim OutApp As Object 'Outlook.Application
    Dim OutMail As Object 'Outlook.MailItem
     
    Const olMailItem As Long = 0
    
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
     
    Set OutMail = OutApp.CreateItem(olMailItem)
     
    With OutMail
        .To = Me.Reg5.Column(1)
        .Subject = "Level 1 Commentary Completed"
        .Body = "Hi," + " " + Me.Reg5.Column(1) + " " + "Please complete your Level 2 Commentary for the Weekly Report."
        .Send
    End With
     
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

It's actually remarkably similar to the example I have on my site here.
 
Upvote 0
Glad to help. :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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