VBA代码无法识别含跨表提取公式的邮箱单元格，求解决

你的代码使用SpecialCells(xlCellTypeConstants)筛选C列单元格，该方法仅选中存储为常量的单元格，而公式生成的邮箱属于xlCellTypeFormulas类型，会被直接跳过，这就是公式邮箱列无法触发代码的核心原因。

调整筛选逻辑，同时包含常量和公式单元格，还修正了代码里的冗余循环与未定义变量问题：

Sub Test1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim strsubject As String
    Dim strbody As String
    
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    
    ' 直接获取主题与正文，无需冗余循环
    strsubject = Range("F10").Value
    strbody = Range("F13").Value & vbNewLine
     
    ' 遍历C列所有非空单元格（含常量和公式）
    For Each cell In Columns("C").Cells.SpecialCells(xlCellTypeConstants + xlCellTypeFormulas)
        ' 验证邮箱格式且对应D列值为"y"
        If cell.Value Like "?*@?*.?*" And LCase(Cells(cell.Row, "D").Value) = "y" Then
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .CC = ""
                .BCC = ""
                .Subject = strsubject
                ' 修正未定义的str变量为strbody
                .body = "Dear " & Cells(cell.Row, "B").Value & vbNewLine & vbNewLine & strbody
                .Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

如果公式可能返回空值，可在判断条件前增加If Not IsEmpty(cell.Value)，避免处理空单元格，进一步提升代码稳定性。

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