Late binding for non version specific Excel VBA code

Question

I have an Excel spreadsheet that my VBA code pulls from to create appointments on a shared Outlook calendar. It will be manipulated by multiple project managers (not at the same time of course).

I found a couple of users have and older version of Office and as such I have learned I should be using "late binding" to make this compatible with older versions.

How do I convert what I have to late binding. All of the examples that I had to create this code was early binding.

Option Explicit
    
Sub SCHMTG() 'Schedule Meeting
    
    Dim wb As Workbook
    Set wb = ThisWorkbook
    
    Dim ws As Worksheet
    Set ws = wb.Sheets("Projects")
    
    ws.Unprotect ""
    
    Dim check As Boolean
    check = False
    
    Dim o As Outlook.Application
    Set o = New Outlook.Application
    
    Dim oNS As Outlook.Namespace
    Set oNS = o.GetNamespace("MAPI")
    
    Dim FOL As Outlook.MAPIFolder
    Set FOL = oNS.GetFolderFromID("00000000F4EFC638C1F878469E872F63F51D794A0100F96BCFC3DAF87B4F8C66193C3EA6F4F40000029DA2430000")
    
    Dim oAPT As Outlook.AppointmentItem
    Dim oAPT_DATE As Date
    Dim oAPT_TIME As Date
    Dim oOBJECT As Object
    Dim b As CheckBox
    Dim r As Integer
    Dim c As Integer
    
    Set b = ws.CheckBoxes(Application.Caller)
    With b.TopLeftCell
        r = .Row
        c = .Column
    End With
    
    For Each oAPT In FOL.Items 'Search for existing meeting
                
        oAPT_DATE = Format(oAPT.Start, "MM-DD-YYYY")
        oAPT_TIME = TimeValue(oAPT.Start)
        
        If oAPT_DATE = ws.Cells(r, c - 3).Value And oAPT.Subject = ws.Cells(r, 1).Value And oAPT_TIME = ws.Cells(r, c - 2).Value Then
            check = True
        Else
        End If
    
    Next oAPT
    
    If check = False Then 'If no meeting already exist Then create new meeting
        Set oAPT = FOL.Items.Add(olAppointmentItem)
        With oAPT
            .Start = ws.Cells(r, c - 3).Value + ws.Cells(r, c - 2).Value
            .Duration = ws.Cells(r, c - 1).Value * 60
            .Subject = ws.Cells(r, 1).Value & " " & ws.Cells(1, c).Value
            .Body = "Project: " & ws.Cells(r, 1).Value & vbCrLf & "Location: " & ws.Cells(r, 2) & vbCrLf & "OASIS#: " & ws.Cells(r, 3) & vbCrLf & "Project Manager: " & ws.Cells(r, 5) & vbCrLf & "Distributor: " & ws.Cells(r, 8) & vbCrLf & "Assigned Technitian: " & ws.Cells(r, c - 5) & vbCrLf & "Date: " & ws.Cells(r, c - 3) & vbCrLf & "Start Time: " & Format(ws.Cells(r, c - 2), "h:mm am/pm") & vbCrLf & "Duration: " & ws.Cells(r, c - 1) & " Hour(s)"
            .Location = ws.Cells(r, 2).Value
            .Recipients.Add Cells(r, c - 4).Value
            .MeetingStatus = olMeeting
            .ReminderMinutesBeforeStart = 1440
            .Save
            .Send
        End With
        ws.Cells(r, c - 1).Locked = True
        ws.Cells(r, c - 2).Locked = True
        ws.Cells(r, c - 3).Locked = True
        ws.Cells(r, c - 5).Locked = True
    Else
    End If
    
    ws.Cells(r, 1).Locked = True
    ws.Cells(r, 2).Locked = True
    ws.Cells(r, 3).Locked = True
    
    ws.Protect "", True, True
    
End Sub

also I am using Excel 2016 and I have one user on 2015 and one on 2010 — Adstanley84
– Adstanley84, Commented Nov 13, 2017 at 22:08

Tim Williams · Accepted Answer · 2017-11-13 23:01:28Z

1

Remove any VBA project references to the Outlook library.

Declare any Outlook objects As Object instead of using the Outlook library typename. Use CreateObject() in place of New

eg:

Dim oAPT As Outlook.AppointmentItem
Dim o As Outlook.Application
Set o = New Outlook.Application

would be

Dim oAPT As Object
Dim o As Object
Set o = CreateObject("Outlook.Application")

Any Outlook-derived constants such as olAppointmentItem should either be declared as Constants in your code, or replaced by their numeric values (which you can find using the Object Browser)

edited Nov 13, 2017 at 23:01

answered Nov 13, 2017 at 22:34

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Adstanley84 Over a year ago

Oh ok, so instead of: Dim FOL As Outlook.MAPIFolder Set FOL = oNS.GetFolderFromID("FolderID") I would use: Dim FOL As Object Set FOL = oNS.GetFolderFromID("FolderID")

Adstanley84 Over a year ago

Sorry for the poor formatting, I gave up after editing it the 10th time and this was as close as I got

Tim Williams Over a year ago

Yes - did you try that?

Adstanley84 Over a year ago

ok I made the changes you suggested however now I am getting errors on a few more lines. Set oAPT = FOL.Items.Add(olAppointmentItem) oAPT_DATE = Format(oAPT.Start, "MM-DD-YYYY") 'says Format Cannot find Project Library

Adstanley84 Over a year ago

Thanks! that worked. I had a couple other constants to define but that did the trick. It now works on all my users workstations.

|

Collectives™ on Stack Overflow

Late binding for non version specific Excel VBA code

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related