Hi everyone,

I have a number of files in a single folder. Each of these files must be emailed to unique recipients.

In my VBA workbook, sheet DataSheet, column P has the file names (without extension), column Q is the To field, and column R is the CC field.

What I'm trying to do is have a macro which will open file explorer, the user will identify the folder the files are located in, and then loop through each file in the folder. If the file name matches a value in column P, then outlook will send an email with the same file attached to the recipients listed in column Q and R. Below is what I have so far, but I keep getting an error on the Attach.add line.

Sub Email_Reps()
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim lastRow As Long
Dim MailDest As String
Dim subj As String


lastRow = ThisWorkbook.Worksheets("DataSheet").Cells(Rows.Count, "P").End(xlUp).Row

With Application.FileDialog(msoFileDialogOpen)
For i = 2 To lastRow

    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    Set Attach = OutLookMailItem.Attachments
    
    With OutLookMailItem
        .To = Cells(i, 2).Value
        .cc = Cells(1, 3).Value
        .Subject = "Blah"
        .Body = "BlahBlah"
        Attach.Add Cells(i, 1).Value & ".xlsx"
        .Send
    End With

Next
End With

End Sub
Any ideas?

Thanks!!!