基于字符串规则的Outlook VBA学校邮件收件人验证需求问询

实现逻辑 #

在Outlook发送邮件前自动触发验证，遍历所有收件人邮箱，匹配两类规则后弹窗列出触发的地址，供你确认是否继续发送：

1. 学生邮箱：匹配包含XX@schooldomain.com（XX为指定的两位入学年份，比如26、27、28）的地址
2. 外部域名邮箱：匹配包含@gmail.com、@hotmail.com等外部域名的地址

操作步骤 #

1. 打开Outlook，按下Alt + F11打开VBA编辑器
2. 在左侧导航栏展开Project1 > Microsoft Outlook Objects，双击ThisOutlookSession
3. 粘贴以下代码：

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim recips As Outlook.Recipients
    Dim recip As Outlook.Recipient
    Dim invalidEmails As String
    Dim studentYearPatterns As Variant
    Dim externalDomains As Variant
    Dim i As Integer
    
    ' 配置需要检查的入学年份后缀（格式：两位数字@学校域名）
    studentYearPatterns = Array("26@schooldomain.com", "27@schooldomain.com", "28@schooldomain.com")
    ' 配置需要检查的外部域名
    externalDomains = Array("@gmail.com", "@hotmail.com", "@yahoo.com")
    
    Set recips = Item.Recipients
    invalidEmails = ""
    
    ' 遍历所有收件人（含抄送、密送）
    For Each recip In recips
        Dim emailAddr As String
        emailAddr = recip.Address
        
        ' 检查学生邮箱规则
        For i = LBound(studentYearPatterns) To UBound(studentYearPatterns)
            If InStr(1, emailAddr, studentYearPatterns(i), vbTextCompare) > 0 Then
                invalidEmails = invalidEmails & "- " & emailAddr & vbCrLf
                Exit For ' 匹配到规则后停止检查该邮箱的学生规则
            End If
        Next i
        
        ' 检查外部域名规则
        For i = LBound(externalDomains) To UBound(externalDomains)
            If InStr(1, emailAddr, externalDomains(i), vbTextCompare) > 0 Then
                invalidEmails = invalidEmails & "- " & emailAddr & vbCrLf
                Exit For ' 匹配到规则后停止检查该邮箱的外部域名规则
            End If
        Next i
    Next recip
    
    ' 有触发规则的邮箱时弹窗提示
    If invalidEmails <> "" Then
        Dim msg As String
        msg = "检测到以下邮箱符合提示规则，请确认是否继续发送：" & vbCrLf & vbCrLf & invalidEmails
        If MsgBox(msg, vbYesNo + vbExclamation, "收件人验证提示") = vbNo Then
            Cancel = True ' 用户选择取消，终止发送
        End If
    End If
    
    Set recips = Nothing
    Set recip = Nothing
End Sub

自定义配置 #

• 若要添加/删除监控的入学年份，修改studentYearPatterns数组即可，比如新增29届规则：Array("26@schooldomain.com", "27@schooldomain.com", "28@schooldomain.com", "29@schooldomain.com")
• 若要新增外部域名提示，直接在externalDomains数组里追加，比如添加@outlook.com

注意事项 #

• 保存代码后，需在Outlook启用宏：文件 > 选项 > 信任中心 > 信任中心设置 > 宏设置，选择"启用所有宏"或"通知我启用数字签署的宏"
• 脚本会在点击「发送」时自动触发，覆盖所有收件人类型

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