VBA根据变量调用Outlook邮件模板时触发Error 91错误求助

一、Error 91错误原因与修复 #

Error 91的核心原因是：当单元格值为Type C时，两个模板匹配的If语句都不会触发，mailItem对象未被赋值，直接调用mailItem.Display就会触发空对象错误。另外你代码里存在拼写错误："Tyep B"应该是"Type B"，这会导致Type B的行也无法匹配模板，同样触发错误。

修复步骤：

• 修正拼写错误：将If Sheet1.Cells(r, 4) = "Tyep B"改为If Sheet1.Cells(r, 4) = "Type B"
• 在循环内部先判断类型，若为Type C则直接跳过当前行，不执行邮件创建逻辑
• 确保仅Type A/B类型才初始化mailItem，避免空对象调用

二、Type C行无法跳过的修复 #

原代码仅在循环开始前检查了一次Type C，循环过程中遇到Type C的行仍会执行循环体逻辑。需要将Type C的判断放到循环体最开头，一旦检测到就直接跳转到下一行，跳过当前循环的所有操作。

修正后的完整代码 #

Sub Send_email_from_template_my_code_1()

Dim EmpID As String
Dim Lastname As String
Dim Firstname As String
Dim VariableType As String
Dim UserID As String
Dim EmailID As String
Dim Toemail As String
Dim FromEmail As String

Dim mailApp As Object
Dim mailItem As Object
Dim r As Long
r = 2

Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object

Do While Sheet1.Cells(r, 7) <> ""
    VariableType = Sheet1.Cells(r, 4).Value
    ' 遇到Type C直接跳过当前行
    If VariableType = "Type C" Then
        r = r + 1
        GoTo LoopStart ' 跳回循环开头处理下一行
    End If
    
    ' 仅为Type A/B创建邮件模板
    Set mailApp = CreateObject("Outlook.Application")
    If VariableType = "Type A" Then
        Set mailItem = mailApp.CreateItemFromTemplate("filepath\Atemplate.msg")
    ElseIf VariableType = "Type B" Then
        Set mailItem = mailApp.CreateItemFromTemplate("filepath\Btemplate.msg")
    End If
    
    ' 读取当前行变量
    EmpID = Sheet1.Cells(r, 1).Value
    Lastname = Sheet1.Cells(r, 2).Value
    Firstname = Sheet1.Cells(r, 3).Value
    UserID = Sheet1.Cells(r, 5).Value
    EmailID = Sheet1.Cells(r, 6).Value
    Toemail = Sheet1.Cells(r, 7).Value
    
    With mailItem
        .Display
        .To = Toemail ' 已过滤Type C，无需重复判断
        If VariableType = "Type A" Then
            .Subject = "Login Information for " & Firstname & " " & Lastname
        ElseIf VariableType = "Type B" Then
            .Subject = "Your other Information is ready - " & Firstname & " " & Lastname
        End If

        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range
        ' 替换[NAME]
        With oRng.Find
            Do While .Execute(FindText:="[NAME]")
                oRng.Text = Firstname & " " & Lastname
            Loop
        End With
        ' 替换[EmpID]
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[EmpID]")
                oRng.Text = EmpID
            Loop
        End With
        ' 替换[EmailID]
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[EmailID]")
                oRng.Text = EmailID
            Loop
        End With
        ' 替换[UserID]
        Set oRng = wdDoc.Range
        With oRng.Find
            Do While .Execute(FindText:="[UserID]")
                oRng.Text = UserID
            Loop
        End With
    End With
    
    r = r + 1
LoopStart: ' 循环跳转标记
Loop

' 释放对象，避免内存泄漏
Set oRng = Nothing
Set wdDoc = Nothing
Set olInsp = Nothing
Set mailItem = Nothing
Set mailApp = Nothing

End Sub

额外优化点 #

• 用&代替+做字符串拼接，+在遇到数值时易出错，&更稳定
• 循环结束后手动释放所有对象，避免内存泄漏
• 调整变量读取顺序，逻辑更清晰
• 用ElseIf替代独立If，减少不必要的条件判断

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