Loop, send email if cell = "x"

[288enzo]

Good day to all,

Using the following, I would like to incorporate a loop that will go row by row starting at row 3 looking for "x" in column D. If there is an "x" in column D, then send an email using data from the row.

Sub Mail_Selection_Range_Outlook_Body()

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim ToEmail As String
    Dim CCEmail As String
    
    Dim x As Long
    Dim LR As Long

    LR = Cells(Rows.Count, 3).End(xlUp).Row
    For x = 50 To LR
    
'If column D on row x = "x" then
    
' I don't know what to do here
    ToEmail = "" 'based on column E and row x
    
    CCEmail = "" 'based on column F and row x
    
    
    Set rng = Nothing
    Set rng = Sheets("Sheet1").Range("A1:A61").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = ToEmail
        .CC = CCEmail
        .Subject = Sheets("Roster").Range("F1").Value
        .HTMLBody = RangetoHTML(rng)
        .Display
    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[Joe4]

Duplicate to: Exploring how to send emails from excel

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.