Results 1 to 8 of 8

Reference Library Problem

This is a discussion on Reference Library Problem within the Excel Questions forums, part of the Question Forums category; Hi all, I wonder if you could steer me in the right direction..... I originally created a number of excel ...

  1. #1
    New Member
    Join Date
    Jul 2014
    Posts
    23

    Default Reference Library Problem

    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?

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Reference Library Problem

    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.

  3. #3
    New Member
    Join Date
    Jul 2014
    Posts
    23

    Default Re: Reference Library Problem

    Hi Rory, forgive me, I do not understand what you mean by this.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Reference Library Problem

    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.

  5. #5
    New Member
    Join Date
    Jul 2014
    Posts
    23

    Default Re: Reference Library Problem

    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

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Reference Library Problem

    It would be (changes highlighted):
    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.

  7. #7
    New Member
    Join Date
    Jul 2014
    Posts
    23

    Default Re: Reference Library Problem

    Rory - thank you so much. Works a treat!! Many thanks.

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,381

    Default Re: Reference Library Problem

    Glad to help.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com