Filter data by name and email |VBA & Macros|Excel Forum|My Online Training Hub

Hi,

I have made a script to email multiple users however, I am struggling filtering the data and pasting that data into an email for that particular user.

Sheet 1 has "Name", "Email" and "Yes/No"

Sheet 2 will have project name, whos accountable "name"

I want the program to filter the data and tally up how many actions that user has then email it to them using a lookup from sheet 1.

Code below works perfectly for email multiple users, but doesn't contain the filtered data.

Any help/advise?

Sub Test1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Reminder"
                .Body = "Dear " & Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                        "Please contact us to discuss bringing " & _
                        "your actions up to date"
                '.Attachments.Add ("C:\test.txt")
                .Send  'Or use Display
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub