Use calendar appointment in outlook to trigger VBA macro

Question

How do you setup a appointment in Outlook to make it trigger a VBA macro by the appointment reminder? In my case I want the outlook to be scheduled to open a excel file at a certain time.

There's are some examples but none that fits my requirements as most use Outlook task and not appointment.

For example: https://www.slipstick.com/developer/code-samples/running-outlook-macros-schedule/ and this Outlook and Excel VBA task Scheduler

Answer 1

Assume we create an appointment and call it "Script Run".

We set the time when it should run (you could add Recurrence) and don't forget to choose reminder!

Also create a category and name it.

Then I use a modified version of the code which it's pasted into the "ThisOutlookSession":

Code to paste into "ThisOutlookSession"

'The Private subs go in ThisOutlookSession
'declare this object withEvents displaying all the events
'might need to access library "Microsoft Excel 16.0 Object Library"

Private WithEvents olRemind As Outlook.Reminders

Private Sub Application_Reminder(ByVal Item As Object)

Set olRemind = Outlook.Reminders

If Item.MessageClass <> "IPM.Appointment" Then
    Exit Sub
End If

If Item.Categories <> "Run weekly script updates" Then 'Add another If statement to add additional appointments
    Exit Sub
End If

Call ExecuteFile ' call sub

End Sub

Private Sub olRemind_BeforeReminderShow(Cancel As Boolean)

'This is to dismiss the reminder

For Each objRem In olRemind
        If objRem.Caption = "Script Run" Then
            If objRem.IsVisible Then
                objRem.Dismiss
                Cancel = True
            End If
            Exit For
        End If
    Next objRem
End Sub

To trigger to open the excelfile we use a sub routine which is located at "Module1". It will look something like this:

---

Version 1:

Sub ExecuteFile()


Call Shell("G:\Till\Budget script.exe", vbNormalFocus) 'To call an external program

'Run Excel macro
Dim xlApp As Excel.Application
Dim xlBook As Workbook

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("G:\Till\Budget.xlsm") ' update with Excel name
xlApp.Visible = True

'// Run Macro in Excel_File
xlBook.Application.Run "Module1.CheckDates"

Set xlApp = Nothing
Set xlBook = Nothing

End Sub

---

Version 2 (late-binding):

This is good for work when you have limited authorization access.

Sub ExecuteFile()

Call Shell("G:\Till\Budget script.exe", vbNormalFocus) 'To call an external program

'Run Excel macro
Dim xlApp As Object
Dim xlBook As Workbook
Dim blnEXCEL As Boolean


'Establish an EXCEL application object, by Ken Snell
'https://social.msdn.microsoft.com/Forums/office/en-US/81d29bf1-524c-4303-8101-611cc30d739b/using-excel-objects-via-late-binding?forum=accessdev
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlApp = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0


Set xlBook = xlApp.Workbooks.Open("G:\Till\Budget.xlsm") ' update with Excel name
xlApp.Visible = True

'// Run Macro in Excel_File
xlBook.Application.Run "Module1.CheckDates"

Set xlApp = Nothing
Set xlBook = Nothing

End Sub