Attaching Specific files to corresponding recipients

Question

I have a long list of cost reports to be sent to different recipients.

I was thinking I could have one Excel file with addresses and corresponding Location i.e A1 John.smith@com.com A2 0001 B1 Jeff.smith@com.com B1 0002

Then using VBA cycle through each row (1) and search a folder for the corresponding (A2) named file and attach it to mail out to cell (A1).

You are on the right track. Column A has emails. Column B has file location. Loop through column A to get email and Column B to attach. This link is a great start. http://stackoverflow.com/questions/17883088/excel-vba-sending-mail-using-outlook-send-method-fails. (Not sure why user had issue with `.Send` but it always work for me.) So, set up a loop in your workbook and apply the `To` from Column A and `Attachment` from Column B and fill the rest out as needed. — Scott Holtzman, Oct 07 '15 at 16:06

score 1 · Accepted Answer · answered Oct 07 '15 at 16:13

I assume you have headers in the first row. Untested.

Sub AntMan()

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim lastRow As Long
Dim MailDest As String
Dim subj As String

lastRow = ThisWorkbook.WorkSheets("Sheet6").Cells(Rows.Count, "A").End(xlUp).Row 'change worksheet

For i = 2 To lastRow

    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    Set Attach = OutLookMailItem.Attachments

    With OutLookMailItem
        .To = Cells(i, 1).Value
        .SUBJECT = "Put your subject here"
        .Body = "Put your body here"
        Attach.Add "C:\your\file\path\here\" & Cells(i, 2).Value & ".xlsx"
        .Display 'for debugging
        .Send
    End With

Next

End Sub

What it you don't have outlook? Can other email clients be used like "Windows live 2012" or "Thunderbird". — HarveyFrench, Oct 07 '15 at 16:59

Attaching Specific files to corresponding recipients

1 Answers1

Linked