使用N.Offset调用含错误值列时VBA自动发邮件功能异常

问题原因 #

代码访问第6-9列时出现异常，是因为这些列的IF公式返回了错误值（如#N/A、#VALUE!等），直接将错误值拼接到HTML邮件内容中时，VBA无法将错误值转换为有效字符串，导致邮件内容构建失败。

解决方法 #

通过判断单元格是否为错误值，将错误值替换为友好文本（比如"暂无数据"），避免异常。有两种实现方式：

方式1：直接在拼接时判断 #

在每个涉及第6-9列的N.Offset调用处，用IIf(IsError(...))处理：

IIf(IsError(N.Offset(0, 6)), "暂无数据", N.Offset(0, 6))

方式2：编写辅助函数简化代码 #

定义通用函数专门处理单元格错误值，让代码更整洁：

Function GetSafeCellValue(rng As Range) As String
    If IsError(rng.Value) Then
        GetSafeCellValue = "暂无数据"
    Else
        GetSafeCellValue = CStr(rng.Value)
    End If
End Function

修改后的完整代码 #

方式1实现版本 #

Sub TRAININGCreateAutomatedEmails2()
    
    Sheets("EmailCode").Select
    
    Dim EApp As Object
    Set EApp = CreateObject("Outlook.Application")
    
    Dim EItem As Object
    
    Dim NList As Range
    Set NList = Range("A3", Range("A3").End(xlDown))
    
    Dim N As Range
    
    For Each N In NList
        
        Set EItem = EApp.CreateItem(0)
        
        strbody = "<P STYLE='font-family:Arial;font-size:10pt'>"
        
        If N.Offset(0, 20) = "Send email" Then
            With EItem
                .To = N.Offset(0, 22)
                .Subject = "Your progress in TRAINING"
                .CC = N.Offset(0, 22)
                .HTMLBody = "<P STYLE='font-family:Arial;font-size:10pt;color: rgb(0,0,0)'>Dear " & N & ", " & "<br>" & "<br>" _
                            & "I hope all is going well for you." & "<br>" _
                            & "This email is to update you on the progress you have made so far on the Digital" _
                            & " content of the TRAINING program based on your start date in role of; " & N.Offset(0, 3) _
                            & "<br>" & "<br>" & "<br>" & "<u>""TRAINING 1-90""</u>" & "<br>" _
                            & "Expected Comp: " & IIf(IsError(N.Offset(0, 6)), "暂无数据", N.Offset(0, 6)) & "%." _
                            & "<br>" & "Actual Comp: " & N.Offset(0, 4) & "%." _
                            & "<br>" & "<br>" & "- " & IIf(IsError(N.Offset(0, 8)), "暂无数据", N.Offset(0, 8)) _
                            & "<br>" & "<br>" & "<u>""TRAINING 91-180""</u>" & "<br>" _
                            & "Expected Comp: " & IIf(IsError(N.Offset(0, 7)), "暂无数据", N.Offset(0, 7)) & "%." & "<br>" _
                            & "Actual Comp: " & N.Offset(0, 5) & "%." _
                            & "<br>" & "<br>" & "- " & IIf(IsError(N.Offset(0, 9)), "暂无数据", N.Offset(0, 9)) _
                            & "<br>" & "<br>" & "<br>" & "See you soon, " & "<br>" & "<br>" & "NAME  MInstLM" _
                            & "<br>" & "Training | Leadership Manager" & "<br>" & "<br>" & "COMPANY Ltd" & "<br>" & "No. 1 Circle Square | STREET" & "<br>" & "CITY| POST CODE" _
                                                                                                                
                .Display
                
            End With
        End If
        
    Next N
    
End Sub

方式2实现版本 #

Function GetSafeCellValue(rng As Range) As String
    If IsError(rng.Value) Then
        GetSafeCellValue = "暂无数据"
    Else
        GetSafeCellValue = CStr(rng.Value)
    End If
End Function

Sub TRAININGCreateAutomatedEmails2()
    
    Sheets("EmailCode").Select
    
    Dim EApp As Object
    Set EApp = CreateObject("Outlook.Application")
    
    Dim EItem As Object
    
    Dim NList As Range
    Set NList = Range("A3", Range("A3").End(xlDown))
    
    Dim N As Range
    
    For Each N In NList
        
        Set EItem = EApp.CreateItem(0)
        
        strbody = "<P STYLE='font-family:Arial;font-size:10pt'>"
        
        If N.Offset(0, 20) = "Send email" Then
            With EItem
                .To = N.Offset(0, 22)
                .Subject = "Your progress in TRAINING"
                .CC = N.Offset(0, 22)
                .HTMLBody = "<P STYLE='font-family:Arial;font-size:10pt;color: rgb(0,0,0)'>Dear " & N & ", " & "<br>" & "<br>" _
                            & "I hope all is going well for you." & "<br>" _
                            & "This email is to update you on the progress you have made so far on the Digital" _
                            & " content of the TRAINING program based on your start date in role of; " & N.Offset(0, 3) _
                            & "<br>" & "<br>" & "<br>" & "<u>""TRAINING 1-90""</u>" & "<br>" _
                            & "Expected Comp: " & GetSafeCellValue(N.Offset(0, 6)) & "%." _
                            & "<br>" & "Actual Comp: " & N.Offset(0, 4) & "%." _
                            & "<br>" & "<br>" & "- " & GetSafeCellValue(N.Offset(0, 8)) _
                            & "<br>" & "<br>" & "<u>""TRAINING 91-180""</u>" & "<br>" _
                            & "Expected Comp: " & GetSafeCellValue(N.Offset(0, 7)) & "%." & "<br>" _
                            & "Actual Comp: " & N.Offset(0, 5) & "%." _
                            & "<br>" & "<br>" & "- " & GetSafeCellValue(N.Offset(0, 9)) _
                            & "<br>" & "<br>" & "<br>" & "See you soon, " & "<br>" & "<br>" & "NAME  MInstLM" _
                            & "<br>" & "Training | Leadership Manager" & "<br>" & "<br>" & "COMPANY Ltd" & "<br>" & "No. 1 Circle Square | STREET" & "<br>" & "CITY| POST CODE" _
                                                                                                                
                .Display
                
            End With
        End If
        
    Next N
    
End Sub

内容的提问来源于stack exchange，提问作者Emma Sjöberg