Public Sub GenerateEmails()
Dim cl As Range
Dim sTempPath As String
Dim sFileName As String
Dim sReportSheet As String
Dim sEmailSheet As String
'Define the names of your worksheets here
sEmailSheet = "Emails" '<-- The worksheet that holds your email table
sReportSheet = "Reports" '<-- The workshee that holds your reports
sTempPath = "C:\Users\" & Environ("Username") & "\Desktop\"
For Each cl In Worksheets(sEmailSheet).Range("tblEmails[Page Number]")
'Record the output name for the file
sFileName = cl.Offset(0, 1)
'Remove any existing instance of the file
On Error Resume Next
Kill sTempPath & sFileName & ".pdf"
On Error GoTo 0
'Export a temporary copy of the file
Worksheets(sReportSheet).ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=sTempPath & sFileName & ".pdf", _
From:=cl.Value, To:=cl.Value
'Email the file
Call EmailViaOutlook(cl.Offset(0, 2), sTempPath & sFileName & ".pdf")
'Remove the temp file
On Error Resume Next
Kill sTempPath & sFileName & ".pdf"
On Error GoTo 0
Next cl
End Sub
[INDENT]