仅当指定单元格非空时发送邮件的VBA代码需求

以下是添加了Sheet1的B20、B21单元格空值校验的完整代码，当这两个单元格同时为空时，会禁止邮件发送并给出提示：

Sub Send_Range_Or_Whole_Worksheet_with_MailEnvelope()
    'Working in Excel 2002-2016
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Fill in the Worksheet/range you want to mail
    'Note: if you use one cell it will send the whole worksheet
    Set Sendrng = Worksheets("Sheet1").Range("A1:B21")

    ' --- 添加校验逻辑 ---
    ' 检查Sheet1的B20和B21是否同时为空
    With Worksheets("Sheet1")
        If Trim(.Range("B20").Value) = "" And Trim(.Range("B21").Value) = "" Then
            MsgBox "Sheet1的B20和B21单元格不能为空，无法发送邮件！", vbExclamation
            GoTo StopMacro ' 跳转到终止流程
        End If
    End With
    ' --- 校验逻辑结束 ---

    'Remember the activesheet
    Set AWorksheet = ActiveSheet

    With Sendrng

        ' Select the worksheet with the range you want to send
        .Parent.Select

        'Remember the ActiveCell on that worksheet
        Set rng = ActiveCell

        'Select the range you want to mail
        .Select

        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope

            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "Please see Quality Review Sev 1 details below."

            With .Item
                .To = "danielle.a.ext@razer.com"
                .CC = ""
                .BCC = ""
                .Subject = Range("E2").Value
                .Send

            End With

        End With
        
        MsgBox "Email Sent"

        'select the original ActiveCell
        rng.Select
    End With

    'Activate the sheet that was active before you run the macro
    AWorksheet.Select

StopMacro:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

End Sub

关键说明 #

• 校验逻辑放在邮件发送流程最前端，避免执行不必要的界面操作
• 使用Trim()函数过滤单元格中的空格，确保真正的空值才会触发校验
• 校验不通过时，通过GoTo StopMacro直接跳转到流程终止部分，恢复Excel默认设置并退出

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