如何在收到标题为特定内容的Outlook邮件时关闭特定工作簿？

Question

我正在尝试让同事在不必知道我的电脑登录信息的情况下保存并关闭共享的工作表。
如果他们需要该文件而不是“只读”版本，则该文件将保持打开状态。

重要的是仅在工作簿处于打开状态时才触发此操作。如果可能，它还会结束从工作簿运行的所有宏的实例。

我考虑添加一个Outlook VBA触发器，在接收带有特定主题的邮件项时保存并关闭它（已存在于Excel中）。
所有Excel端的代码都可以正常工作。（在特定时间触发保存和关闭宏并已确认可行）。

在Outlook端，我添加了我认为是事件侦听器代码的ThisOutlookSession，该代码调用应触发Excel中关闭操作的模块。

ThisOutlookSession中的代码如下：

Option Explicit
Private WithEvents inboxItems As Outlook.Items
Private Sub Application_Startup()
    Dim outlookApp As Outlook.Application
    Dim objectNS As Outlook.NameSpace
      
    Set outlookApp = Outlook.Application
    Set objectNS = outlookApp.GetNamespace("MAPI")
    Set inboxItems = objectNS.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub inboxItems_ItemAdd(ByVal Item As Object)
    On Error GoTo ErrorHandler
    Dim Msg As Outlook.MailItem
    If TypeName(Item) = "MailItem" Then
        Call Excel_Closer.Close_Excel
    End If
ExitNewItem:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitNewItem
End Sub

模块中的代码（Excel_Closer）

保存和关闭Excel的宏是“mCloser.EmailClose”。

如果已经打开，“Nordic_Market_Monitor_2019.xlsm”是要激活的工作簿。

Option Explicit
Sub Close_Excel(MyMail As MailItem)
    On Error GoTo Error_Handler
    Dim xlApp As Excel.Application
    Dim xlBook As Workbook
    Dim strSubject As String

    strSubject = MyMail.Subject

    If strSubject = "Close Excel" Then
        On Error GoTo Error_Handler
        
        Set xlApp = GetObject(, "Excel.Application")
        Set xlBook = xlApp.Workbooks("Nordic_Market_Monitor_2019.xlsm").Activate
        
        xlApp.Visible = True

        xlBook.Application.Run "mCloser.EmailClose"

        Set xlApp = Nothing
        Set xlBook = Nothing
        
    End If
   
Error_Handler:
    Exit Sub
End Sub

没有触发错误信息，也没有其他任何反应。

- Nicholas S

Answer 1

如果您引用Excel或工作簿并出现错误，则无法打开。

Sub Close_Excel(MyMail As MailItem)

    ' Remove in development phase to highlight the line with the error
    'On Error GoTo Error_Handler

    Dim xlApp As Excel.Application
    Dim xlBook As Workbook
    Dim strSubject As String

    strSubject = MyMail.Subject

    If strSubject = "Close Excel" Then

        ' "On Error Resume Next" is rarely beneficial
        '  It is here for a specific purpose

        On Error Resume Next ' bypass error if Excel is not open
        Set xlApp = GetObject(, "Excel.Application")
        On Error GoTo 0 ' Remove error bypass as soon as the purpose is served

        If Not xlApp Is Nothing Then

            'Excel is open
            On Error Resume Next ' bypass error if workbook is not open
            Set xlBook = xlApp.Workbooks("Nordic_Market_Monitor_2019.xlsm")
            On Error GoTo 0 ' Remove error bypass as soon as the purpose is served

            If Not xlBook Is Nothing Then
                ' Workbook is open
                xlApp.Visible = True
                xlBook.Application.Run "mCloser.EmailClose"

            Else
                Debug.Print "Workbook not open."

            End If

        Else
            Debug.Print "Excel not open."

        End If

    End If

exitRoutine:
    Set xlApp = Nothing
    Set xlBook = Nothing
    Exit Sub

'Error_Handler:
'    MsgBox Err.Number & " - " & Err.Description
'    Resume exitRoutine

End Sub

- niton