Reference Library Problem

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
23
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?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

scottmcclean

New Member
Joined
Jul 2, 2014
Messages
23
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Glad to help. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,095,803
Messages
5,446,560
Members
405,407
Latest member
apat

This Week's Hot Topics

Top