VBA - Excel to Outlook - But a loop based on date!!! HELP!!!

[kltinaheart]

Im so close!
well kind of. Im a beginner and im trying to figure out how to Loop through ranges.

So, i have an excel sheet and i want to send a series of emails once a week.
So i currently figured out how to send an email based off of 1 chosen row (below), it takes the email address, it takes the message, and the subject!

what i want is to take the idea and make it depend on a time frame.
So lets say in Cell J, i make an If that results in a Yes No (If Date range is within 7 days of today, give me a yes)
I want the program to loop through the rows and take all of the "YES" cells and email out the associated row info into different emails.
(If J says Yes, email all the below)

Sub Email_PO()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim sTO As String, sSubj As String
    
    sT0 = [M4]
    sSubj = "PM Due - TESTER"
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
     strbody = " Hey " & Range("L4") & "," & vbNewLine & vbNewLine & _
    "You have received a PO Trouble Ticket - " & vbNewLine & Range("K4") & vbNewLine & _
    vbNewLine & "Please Email me immediately when it is finished!" & vbNewLine & vbNewLine & _
    "Thanks again!"


    On Error Resume Next
    With OutMail
        .To = sT0
        .CC = ""
        .BCC = ""
        .Subject = sSubj
        .Body = strbody
        .Send
End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

Help me obi won kenobi, you're my only hope.

 

[shknbk2]

I added 2 Range variables and an IF check to see if col J is "Yes". I don't know where your email addresses start, so you'll need to change the cell address in the "Set addresses" line at the beginning of the code to identify where it is.

Sub Email_PO()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim sTO As String, sSubj As String
    Dim c As Range, addresses As Range
    
    Set addresses = Range("M4", Cells(Rows.Count, 13).End(xlUp))
    sSubj = "PM Due - TESTER"
    Set OutApp = CreateObject("Outlook.Application")
    For Each c In addresses
        'Check to see if column J has Yes
        If LCase(Range("J" & c.Row).Value) = "yes" Then
            Set OutMail = OutApp.CreateItem(0)
            sT0 = c.Value
             strbody = " Hey " & Range("L" & c.Row) & "," & vbNewLine & vbNewLine & _
            "You have received a PO Trouble Ticket - " & vbNewLine & Range("K" & c.Row) & vbNewLine & _
            vbNewLine & "Please Email me immediately when it is finished!" & vbNewLine & vbNewLine & _
            "Thanks again!"
            
            On Error Resume Next
            With OutMail
                .To = sT0
                .CC = ""
                .BCC = ""
                .Subject = sSubj
                .Body = strbody
                .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next
    Set OutApp = Nothing
End Sub

 

[kltinaheart]

I added 2 Range variables and an IF check to see if col J is "Yes". I don't know where your email addresses start, so you'll need to change the cell address in the "Set addresses" line at the beginning of the code to identify where it is.
Amazing.
worked like a charm. You're the best!!!!!