how to use Visual Basic to select a cell based on criteria in another cell in same line-CodePudding

I am currently using Visual Basic to write a macro in excel that will send an email based upon a certain date being met in a column. I have attached a screen shot of an example spread sheet to try to help clarify what I am trying to do.

So far in the macro written, once the date is met then the program sends an email telling me its due.

The part I need help with is in the subject of the email I would like it to send the "Name" and "Serial number" that corresponds to the row the date is in. For example (from my attached screenshot) on "January 2, 2022" (column C, row 3) when the email is sent in the subject it would add in the information "Gauge" (Column A, row 3) and "23456" (column B, row 3).

I would need it to do that for each date that meets the criteria.

The email subject would look something like this:

"Gauge" with serial number "23456" is due.

I have tried everything I can think of to add into the emails subject line the entire row that returns as True in R. I am hitting a brick wall and am looking for a new direction that I must be missing.

For Each cell In r

    If cell.Value = Date   7 Then

        Dim Email_Subject, Email_Send_From, Email_Send_To, _
        Email_Cc, Email_Bcc, Email_Body As String
        Dim Mail_Object, Mail_Single As Variant

        Email_Subject = "Gauge Calibration Due"
        Email_Send_From = "[email protected]"
        Email_Send_To = "[email protected]"
        Email_Cc = ""
        Email_Body = f
        
        
        On Error GoTo debugs
        Set Mail_Object = CreateObject("Outlook.Application")
        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


    End If

   
Next


Exit Sub

CodePudding user response：

If "r" is already your range of dates, and it's successfully iterating through them and popping up the email, so all you need is the subject- try this?

For Each cell In r

If cell.Value = Date   7 Then

    Dim Email_Subject As String
    Dim Email_Send_From, Email_Send_To, _
    Email_Cc, Email_Bcc, Email_Body As String
    Dim Mail_Object, Mail_Single As Variant

    Email_Subject = cell.Offset(0,-2) & " with Serial Number " & cell.Offset(0,-1) & " is due."
    Email_Send_From = "[email protected]"
    Email_Send_To = "[email protected]"
    Email_Cc = ""
    Email_Body = f
    
    
    On Error GoTo debugs
    Set Mail_Object = CreateObject("Outlook.Application")
    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


End If


Next cell