基于Excel邮件列表批量发邮件的VBA代码故障排查请求

我有一份用作邮件发送列表的Excel文件，需要实现两个核心功能：

• 从N-P列（对应第14-16列）读取最多3个文件路径，作为邮件附件添加
• 将L列（第12列）指定的Word文档内容，复制作为邮件正文

尝试过Ron de Bruin、paran及Tomasz Płociński的相关代码，当前参考Tomasz的代码但完全无法运行，自己缺乏VBA知识排查错误，原代码如下：

Sub MAIL_EN)

On Error Resume Next
Dim OutApp As Object
Dim OutMail As Object
Dim wd As Object
Dim doc As Object
Dim editor As Object
Dim i As Integer
Dim rng As Range

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set wd = CreateObject("Word.Application")

''
Set rng = Nothing
On Error Resume Next
Set rng = Worksheets("ank").Range(".Cells(i, 14).Value:.Cells(i, 16).Value").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
'''

For i = 17 To lastRow

    Set OutMail = OutApp.CreateItem(0)
    
        With OutMail
        .SentOnBehalfOfName = "mail@mail.com"
        .to = .Cells(i, 3).Value
        .CC = .Cells(i, 5).Value
        .BCC = .Cells(i, 6).Value
        .Subject = .Cells(i, 11).Value
        .Attachments.Add = rng
        Set doc = wd.documents.Open.Cells(i, 12).Value
        doc.Content.Copy

        Set editor = .GetInspector.WordEditor
        editor.Content.Paste
      
    
        Set wd = Nothing
       .Display
           
           
 doc.Close 0
 Set OutMail = Nothing
 Set wd = Nothing
 Set OutApp = Nothing
                    
End With

Next i

End Sub

• 过程定义语法错误：Sub MAIL_EN) 末尾多了右括号，正确写法是Sub MAIL_EN()
• 未定义lastRow变量：循环For i = 17 To lastRow没有终止条件，需要先获取数据行的最后一行
• 范围引用逻辑错误：Range(".Cells(i,14).Value:.Cells(i,16).Value") 是无效的字符串拼接写法，附件需要的是单元格里的文件路径值，不是单元格范围
• 附件添加语法错误：.Attachments.Add = rng 写法错误，Add方法需要传入文件路径字符串，且要逐个处理N-P列的单元格
• Word文档打开语法错误：wd.documents.Open.Cells(i,12).Value 应该是wd.Documents.Open(Worksheets("ank").Cells(i,12).Value)，Open方法需要括号包裹参数
• 对象销毁时机错误：在循环内部销毁OutApp、wd对象，导致后续循环无法创建新的邮件和Word实例
• With块上下文错误：With OutMail 内部的.Cells(i,3) 会被解析为OutMail的属性，而非Excel工作表的单元格，需要明确指定工作表
• 错误处理滥用：On Error Resume Next 掩盖了大部分错误，导致无法定位问题

Sub MAIL_EN()
    ' 声明变量
    Dim OutApp As Object
    Dim OutMail As Object
    Dim wd As Object
    Dim doc As Object
    Dim editor As Object
    Dim i As Integer
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim attachPath As Variant
    Dim col As Integer
    
    ' 初始化工作表对象
    Set ws = ThisWorkbook.Worksheets("ank")
    ' 获取数据最后一行（假设A列有连续数据，可根据实际调整列）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 创建Outlook和Word实例
    Set OutApp = CreateObject("Outlook.Application")
    Set wd = CreateObject("Word.Application")
    wd.Visible = False ' 隐藏Word窗口，避免弹窗干扰
    
    ' 从第17行开始循环处理每一行数据
    For i = 17 To lastRow
        ' 创建新邮件
        Set OutMail = OutApp.CreateItem(0)
        
        With OutMail
            ' 设置邮件基础信息
            .SentOnBehalfOfName = "mail@mail.com"
            .To = ws.Cells(i, 3).Value ' C列收件人
            .CC = ws.Cells(i, 5).Value ' E列抄送
            .BCC = ws.Cells(i, 6).Value ' F列密送
            .Subject = ws.Cells(i, 11).Value ' K列主题
            
            ' 添加N-P列（14-16列）的附件
            For col = 14 To 16
                attachPath = ws.Cells(i, col).Value
                ' 仅当单元格不为空且文件存在时添加
                If attachPath <> "" And Dir(attachPath) <> "" Then
                    .Attachments.Add attachPath
                End If
            Next col
            
            ' 读取L列（12列）Word文档内容作为正文
            If ws.Cells(i, 12).Value <> "" And Dir(ws.Cells(i, 12).Value) <> "" Then
                Set doc = wd.Documents.Open(ws.Cells(i, 12).Value)
                doc.Content.Copy
                ' 将内容粘贴到邮件正文
                Set editor = .GetInspector.WordEditor
                editor.Content.Paste
                ' 关闭Word文档，不保存
                doc.Close SaveChanges:=0
                Set doc = Nothing
            End If
            
            ' 显示邮件（如果要直接发送，把.Display改成.Send）
            .Display
        End With
        
        ' 释放当前邮件对象
        Set OutMail = Nothing
    Next i
    
    ' 释放全局对象
    wd.Quit
    Set wd = Nothing
    Set OutApp = Nothing
    Set ws = Nothing
    
    MsgBox "邮件处理完成！", vbInformation
End Sub

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