VBA代码如何跳过Excel筛选后的隐藏行批量发送邮件？

修改后的完整代码 #

Sub Send_email_fromtemplate()
    Dim edress As String
    Dim cc1 As String, cc2 As String, cc3 As String ' 修正变量声明，确保所有cc变量为String类型
    Dim group As String
    Dim number As String
    Dim outlookapp As Object
    Dim outlookmailitem As Object
    Dim r As Long
    Dim olInsp As Object
    Dim wdDoc As Object
    Dim oRng As Object

    ' 仅创建一次Outlook应用，提升运行效率
    Set outlookapp = CreateObject("Outlook.Application")
    
    r = 3
    Do While Sheet1.Cells(r, 1) <> ""
        ' 跳过被筛选隐藏的行
        If Not Sheet1.Cells(r, 1).EntireRow.Hidden Then
            ' 调用邮件模板
            Set outlookmailitem = outlookapp.CreateItemFromTemplate([location])
            outlookmailitem.Display

            edress = Sheet1.Cells(r, 7)
            cc1 = Sheet1.Cells(r, 8)
            cc2 = Sheet1.Cells(r, 9)
            cc3 = Sheet1.Cells(r, 10)
            group = Sheet1.Cells(r, 4)
            number = Sheet1.Cells(r, 3).Value
            
            With outlookmailitem
                .To = edress
                .cc = cc1 & ";" & cc2 & ";" & cc3
                .bcc = ""
                .Subject = "First invoice reminder " & group
                
                Set olInsp = .GetInspector
                Set wdDoc = olInsp.WordEditor
                Set oRng = wdDoc.Range
                With oRng.Find
                    Do While .Execute(FindText:="{{Number}}")
                        oRng.Text = number
                        Exit Do
                    Loop
                End With
                .Display
                '.send
            End With
            edress = ""
        End If
        ' 无论行是否隐藏，都递增行号
        r = r + 1
    Loop
    
    ' 释放对象资源
    Set outlookapp = Nothing
    Set outlookmailitem = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Set olInsp = Nothing
End Sub

关键改动说明 #

1. 跳过隐藏行的核心逻辑
   新增If Not Sheet1.Cells(r, 1).EntireRow.Hidden Then判断，直接检查当前行是否被筛选隐藏，仅可见行执行邮件发送逻辑。这种方式比SpecialCells(xlCellTypeVisible)更稳定，不会因模板操作导致的Excel区域变化而出错。

2. 优化Outlook实例创建
   将Outlook应用的创建移到循环外部，避免每次循环都生成新实例，大幅提升运行效率。

3. 修正变量声明错误
   原代码中Dim cc1, cc2, cc3 As String仅cc3为String类型，cc1和cc2默认是Variant类型，修正为明确的String类型声明，避免潜在类型错误。

4. 清理冗余代码
   移除重复的Set xInspect = outlookmailitem.GetInspector语句，减少不必要的对象赋值。

关于SpecialCells方法的问题说明 #

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible)返回的是不连续的单元格区域集合，当操作邮件模板切换窗口时，Excel的UsedRange可能发生变化，遍历不连续区域也容易出现索引混乱。而逐行检查Hidden属性的方式逻辑更直接，不受窗口切换或区域变化影响。

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