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

kltinaheart

New Member
Joined
Apr 20, 2018
Messages
14
:) 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)


Code:
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.

Code:
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
 
Last edited:
Upvote 0
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!!!!! :cool::cool:
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top