如何使用Visual Basic在Excel中按同行单元格条件取值填充邮件主题

解决方法 #

核心逻辑是利用当前触发条件的日期单元格的行号，读取同一行的名称和序列号字段，拼入邮件主题即可。

• 循环中触发条件的cell对象自带Row属性，可直接获取当前符合条件的行号
• 为了提升运行效率，建议把Outlook应用对象的创建放到循环外，避免每次发邮件都重复初始化对象
• 按你要求的主题格式直接拼接A、B列的取值即可

修改后代码 #

' 把Outlook对象声明放到循环外，只初始化一次
Dim Mail_Object As Object
Set Mail_Object = CreateObject("Outlook.Application")

For Each cell In r
    If cell.Value = Date + 7 Then
        Dim Email_Subject As String, Email_Send_From As String, Email_Send_To As String
        Dim Email_Cc As String, Email_Bcc As String, Email_Body As String
        Dim Mail_Single As Object
        Dim curRow As Long
        ' 获取当前符合条件的行号
        curRow = cell.Row
        ' 拼接主题，A列是名称、B列是序列号
        Email_Subject = """" & Cells(curRow, "A").Value & """ with serial number """ & Cells(curRow, "B").Value & """ is due"
        Email_Send_From = "Quality@hotmail.com"
        Email_Send_To = "mikenorth@hotmail.com"
        Email_Cc = ""
        Email_Body = f
        
        On Error GoTo debugs
        Set Mail_Single = Mail_Object.CreateItem(0)
        With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
            .CC = Email_Cc
            .Body = Email_Body
            .Send
        End With
        Set Mail_Single = Nothing
    End If
Next

' 释放Outlook对象
Set Mail_Object = Nothing
Exit Sub

debugs:
' 可自行补充错误处理逻辑
If Err.Description <> "" Then MsgBox Err.Description

补充说明 #

如果你定义的范围r不是从工作表第一行开始的，你也可以用cell.Offset(0, -2)取同一行往前数2列的A列值、cell.Offset(0, -1)取B列值，效果和按行号读取是一致的。

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