Send email to email addresses on sheet 1 with data from sheet 2 if they match data

[krazeydla]

Hello all, Thank you for taking the time to assist. It is greatly appreciated.

I am trying to make this as simple as cake.

I have a work book that has office locations with POCs and email addresses on sheet 1 and on sheet 2 is a list of personnel who need certain requirements who belong to the different office locations.

What I want to do is run a code, that will go through sheet 2 and MATCH column (starting at) "A4" (Office Locations) with Sheet1 starting at column "A2".

If there is a match, then excel would to create an attachment which those specific personnel rows from sheet 2 and mail to their office location POCs from sheet 1...

I.e. 5 personnel who have requirements on sheet 2, all there information from their row would be emailed to sheet 1 POC.


The Third sheet would have the verbiage: the subject, salutation, body, with paragraph 1 and paragraph 2, valediction, and signature (this way later can be tailored instead of editing the code again)

The code that I have been trying to tailor I cant seem to work is below...Please help

Right now I have it all in the same module. I have no clue where to put this or what to edit...when I try to run, I get an error "1004" Application-defined or object -defined error...

I know, that's only the beginning of my problems. Someone please help. Thanks.

Sub Trying()


Dim myValue As Variant

'Modify the function to use YOUR arguments
myValue = Application.VLookup(value_to_lookup, range_to_search, column_number, exact_match)

If IsError(myValue) Then
    MsgBox "Not found!", vbInformation
    Exit Sub
End If
End Sub


Sub foo()
Dim c As Range
Dim match As Integer

With Sheets("Info Table")
    For Each c In .Range("C1:C10").Cells ''Modify this as needed
        match = 0
        On Error Resume Next
        match = IsError(Application.match(c.Value, Sheets("Email List").Range("A2:K2"), False))
        On Error GoTo 0
        If Not match = 0 Then Call Send_Email(match)
    Next
End With
End Sub

Sub Send_Email(match As Integer)

Dim Email_Subject As String, Email_Send_From  As String, Email_Body As String, i As Integer
Dim Mail_Object As Object, nameList As String, o As Variant

    Email_Send_From = ""

    If Sheets("Email List").Cells(match, 3).Value <> "" Then
        nameList = Join(Application.Transpose(Sheets("Email List").Range("A3:A").Offset(, match - 1).Value, ";"))
    
    End If

    
    Set Mail_Object = CreateObject("Outlook.Application")
    With Mail_Object.CreateItem(o)
        .Subject = ""
        .To = nameList
        .Cc = ""
        .Body = "I am testing a new VBA, sorry if you received this message in error." & vbNewLine & vbNewLine & _
                "Best Regards," & vbNewLine & _
                ""
        .display
    End With
        Application.DisplayAlerts = False

End Sub

 

[krazeydla]

bump