在 Outlook 中使用日历约会触发 VBA 宏 [英] Use calendar appointment in outlook to trigger VBA macro

问题描述

如何在 Outlook 中设置约会以使其通过约会提醒触发 VBA 宏?在我的情况下，我希望 Outlook 被安排在某个时间打开一个 excel 文件.

有一些例子但没有一个符合我的要求，因为大多数使用 Outlook 任务而不是约会.

例如:

还要创建一个类别并为其命名.

然后我使用粘贴到ThisOutlookSession"中的代码的修改版本:

粘贴到ThisOutlookSession"中的代码

'私人订阅进入ThisOutlookSession'用Events声明这个对象，显示所有事件'可能需要访问库Microsoft Excel 16.0 对象库"；私人 WithEvents olRemind 作为 Outlook.RemindersPrivate Sub Application_Reminder(ByVal Item As Object)设置 olRemind = Outlook.Reminders如果 Item.MessageClass <>IPM.约会"然后退出子万一如果 Item.Categories <>每周运行脚本更新"然后 '添加另一个 If 语句以添加其他约会退出子万一Call ExecuteFile ' call sub结束子Private Sub olRemind_BeforeReminderShow(Cancel As Boolean)'这是解除提醒对于 olRemind 中的每个 objRem如果 objRem.Caption = 脚本运行"然后如果 objRem.IsVisible 那么objRem.Dismiss取消 = 真万一退出万一下一个 objRem结束子

为了触发打开 excelfile，我们使用位于Module1"的子程序.它看起来像这样:

---

版本 1:

Sub ExecuteFile()Call Shell("G:\Till\Budget script.exe", vbNormalFocus) '调用外部程序'运行Excel宏Dim xlApp 作为 Excel.ApplicationDim xlBook 作为工作簿设置 xlApp = 新建 Excel.ApplicationSet xlBook = xlApp.Workbooks.Open("G:\Till\Budget.xlsm") ' 用 Excel 名称更新xlApp.Visible = True'//在 Excel_File 中运行宏xlBook.Application.RunModule1.CheckDates"设置 xlApp = 无设置 xlBook = 无结束子

---

版本 2(后期绑定):

当您的授权访问权限有限时，这对工作很有用.

Sub ExecuteFile()Call Shell("G:\Till\Budget script.exe", vbNormalFocus) '调用外部程序'运行Excel宏Dim xlApp 作为对象Dim xlBook 作为工作簿Dim blnEXCEL 作为布尔值'建立 EXCEL 应用程序对象，作者:Ken Snell'https://social.msdn.microsoft.com/Forums/office/en-US/81d29bf1-524c-4303-8101-611cc30d739b/using-excel-objects-via-late-binding?forum=accessdev出错时继续下一步Set xlApp = GetObject(, "Excel.Application")如果 Err.Number <>0 那么Set xlApp = CreateObject("Excel.Application")blnEXCEL = 真万一错误清除出错时转到 0Set xlBook = xlApp.Workbooks.Open("G:\Till\Budget.xlsm") ' 用 Excel 名称更新xlApp.Visible = True'//在 Excel_File 中运行宏xlBook.Application.RunModule1.CheckDates"设置 xlApp = 无设置 xlBook = 无结束子

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

解决方案

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

这篇关于在 Outlook 中使用日历约会触发 VBA 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！