为什么我的 VBA 使用偏移量不适用于某些列？

最好在每个循环开始时检查值，这样就不会不必要地创建项目。我最终对偏移量进行了硬编码，因为我不确定检查错误是否可以遍历整个单元格的行。

Sub TRAININGCreateAutomatedEmails2()
    
    Sheets("EmailCode").Select
    
    Dim EApp As Object
    Set EApp = CreateObject("Outlook.Application")
    
    Dim EItem As Object
    
    Dim NList As Range, strbody As String
    Set NList = Range("A3", Range("A3").End(xlDown))
    
    Dim N As Range, arr, arrOffset, i As Long
    arrOffset = Array(20, 22, 3, 6, 4, 8, 7, 5, 9) ' these were all the ones used in your code
    'you could check every cell for an error but not sure that's what is intended here
    For Each N In NList
        arr = N.Resize(, 23).Value 'arr is a variant array with dimensions (1 to 1, 1 To 23)
'these are all the values from A to W in the row of the current N
        For i = 0 To UBound(arrOffset) 
'Time to loop the values to see if any of the Offsets would throw an error
            If IsError(arr(1, arrOffset(i) + 1)) Then GoTo Skip '+1 so it's an actual offset
            'arr starts with index 1 (A-column), so .Offset(,22) = arr(1,23)
        Next i
'No error was found so we continue as usual
        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: " & N.Offset(0, 6) & "%." _
                            & "<br>" & "Actual Comp: " & N.Offset(0, 4) & "%." _
                            & "<br>" & "<br>" & "- " & N.Offset(0, 8) _
                            & "<br>" & "<br>" & "<u>""TRAINING 91-180""</u>" & "<br>" _
                            & "Expected Comp: " & N.Offset(0, 7) & "%." & "<br>" _
                            & "Actual Comp: " & N.Offset(0, 5) & "%." _
                            & "<br>" & "<br>" & "- " & 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
        'Debug.Print "Sent email to: " & N.Offset(0, 22) 'used for testing
Skip: 'immediately goes to the next N
    Next N
    
End Sub

如果有什么不清楚的地方，请告诉我。