excel - 如何关闭特定工作簿以响应接收具有特定标题的 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

没有错误消息被触发，也没有发生任何其他事情。

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