VBA macro to save and email multiple PDF invoices in Excel (via Outlook)

[rob789]

The Macro below is currently set up to create individual invoices and save as PDF's based on a database sheet with invoice data on each row and then an invoice template on another sheet. The macro loops through each row in the database and changes a number on the template with each iteration which in turn changes the result of a Vlookup and therefore the invoice data.



I would like it so that for each invoice not only is it saved but each invoice file is also then emailed to an email address via Outlook that will appear on the invoice template and will change with each iteration as it will also be linked to a vlookup.



Sub SaveInvoices()
Dim i As Integer

For i = 2 To Sheets("InvoiceTemplate").Range("D1").Value
Sheets("InvoiceTemplate").Cells(1, 3) = Sheets("FinalMonthlyInvoices").Cells(i, 2)

Sheets("InvoiceTemplate").Range("A2:K66").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\VSINV\invoice" & Sheets("FinalMonthlyInvoices").Cells(i, 2) & ".pdf", quality:=xlQualityStandard, _
includedocproperties:=True, ignoreprintareas:=True, openafterpublish:=False
Next i
MsgBox "Done"
End Sub

 

[rob789]

I should clarify, each invoice file will be attached to a separate email. For example if 10 invoices are saved then 10 emails with to 10 different email address will be sent.