如何在Outlook触发的Excel宏中发邮件，解决运行时错误91？

错误根因 #

运行时错误'91'的核心诱因是跨进程COM对象调用被拦截，和自动触发的执行逻辑完全匹配：

• 手动触发宏时，代码运行在当前打开的Excel进程内，和已启动的Outlook进程权限匹配，可正常创建Outlook实例
• 自动触发时，触发逻辑在Outlook的宏里重复创建了全新的Excel独立进程，该进程未获得Outlook互操作的安全授权，执行CreateObject("Outlook.Application")时实际返回空值，后续调用CreateItem就会抛出对象未设置的报错

修复步骤 #

• 第一步：优化Outlook侧的触发代码，删除冗余的Excel实例创建逻辑，复用已经初始化的xlApp对象
  修改后的XL_hh_mck_weekly代码如下：

Sub XL_hh_mck_weekly()
Dim Item As Outlook.MailItem
Dim olOutmail As Outlook.MailItem
' 此处filepath需要提前定义赋值，原代码遗漏请补充
If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.DisplayAlerts = False

' 直接复用已创建的xlApp，不要新建Excel实例
With xlApp
    .Workbooks.Open (filepath & "PERSONAL.xlsb")
    .Workbooks.Open (filepath & "hhweekly.csv")
    .Visible = False

    ' Ensure Autocalculation is on
    .Calculation = -4105 ' xlCalculationAutomatic

    .Run "'PERSONAL.xlsb'!hh_mck_weekly"

    ' Wait until calculation is done
    Do Until .CalculationState = 0   ' xlDone
        DoEvents
    Loop
    ' 补充清理逻辑，避免Excel进程残留
    .Quit
End With
Set xlApp = Nothing
End Sub

• 第二步：优化Excel侧宏的Outlook实例创建逻辑，优先调用已运行的Outlook实例，避免新建实例触发权限拦截
  修改后的hh_mck_weekly代码如下：

Sub hh_mck_weekly(nm As String)
' 若未引用Outlook对象库，建议改为晚绑定避免版本兼容问题
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim OutlookMail2 As Object

' 优先获取已运行的Outlook实例，失败再新建
On Error Resume Next
Set OutlookApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
    Set OutlookApp = CreateObject("Outlook.Application")
End If
On Error GoTo 0

' 新增实例非空判断，避免后续空对象调用报错
If OutlookApp Is Nothing Then
    MsgBox "Outlook启动失败，请检查权限设置"
    Exit Sub
End If

Set OutlookMail = OutlookApp.CreateItem(0)
Set OutlookMail2 = OutlookApp.CreateItem(0)

' 后续原有逻辑保持不变
'   Open Need Category
    If IsOpen("NeedCategory.xls") = True Then

'     Get category from Mckesson
      With OutlookMail
        .To = address
        .Subject = "Category"
        .Body = "Byron, I need the category for these items.  Thanks"
        .Attachments.Add (filepath & "NeedCategory.xls")
        .Display
        .Send
    End With

End If


With OutlookMail2
    .To = address
    .Subject = "Weekly Mckesson Report"
    .Body = "Thanks"
    .Attachments.Add (filepath & nm)
    .Display
End With

Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

• 第三步：验证运行环境一致性
  确保Outlook和Excel的运行权限相同，不要出现一个用管理员身份运行、另一个用普通用户身份运行的情况；同时在Office信任中心设置中，启用宏运行权限，关闭不必要的受保护视图限制。

内容的提问来源于stack exchange，提问作者libertyexcel