Email PDFs created from a list in Excel

Question

I create pdfs from a list of 100+ items. I want to email those pdfs.

Note: the list is range of unique ID numbers, which have corresponding e-mail addresses.

Create PDFs from list (I save the pdfs to a folder):

Sub SavePDFsFromList()
    
    'Declare the Variables
    Dim ws As Worksheet
    Dim rngID As Range
    Dim rngListStart As Range
    Dim rowsCount As Long
    Dim i As Long
    Dim pdfFilePath As String
    Dim tempPDFFilePath As String
    
    'Stop the screen updating while running
    Application.ScreenUpdating = False
    
    'Reference the tab the pdf will be created from
    Set ws = ActiveWorkbook.Sheets("Statement")
    
    'Cell that generates information on each pdf
    Set rngID = ws.Range("A1")
    
    'Reference the start of the  ID List
    Set rngListStart = ws.Range("M4")
    
    'Count the rows in the ID List
    rowsCount = rngListStart.CurrentRegion.Rows.Count - 1
    
    'Create the PDF File Name
    pdfFilePath = "C:\Test Folder\PDF Export\Example - [ID].pdf"
    
    For i = 1 To rowsCount
    
        'Change the current ID
        rngID.Value = rngListStart.Offset(i - 1, 0).Value
    
        'Replace [ID] with ID Value
        tempPDFFilePath = Replace(pdfFilePath, "[ID]", rngID.Value)
    
        'Create the PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=tempPDFFilePath
    
    Next i
    
    'Restart the screen updating
    Application.ScreenUpdating = True
    
End Sub

Answer 1

try

Sub SavePDFsFromList()

    'Declare the Variables
    Dim ws As Worksheet
    Dim rngID As Range
    Dim rngListStart As Range
    Dim rowsCount As Long
    Dim i As Long
    Dim pdfFilePath As String
    Dim tempPDFFilePath As String

    'Stop the screen updating while running
    Application.ScreenUpdating = False

    'Reference the tab the pdf will be created from
    Set ws = ActiveWorkbook.Sheets("Statement")

    'Cell that generates information on each pdf
    Set rngID = ws.Range("A1")

    'Reference the start of the  ID List
    Set rngListStart = ws.Range("M4")

    'Count the rows in the ID List
    rowsCount = rngListStart.CurrentRegion.Rows.Count - 1

    'Create the PDF File Name
    pdfFilePath = "C:\Test Folder\PDF Export\Example - [ID].pdf"

    For i = 1 To rowsCount

        'Change the current ID
        rngID.Value = rngListStart.Offset(i - 1, 0).Value

        'Replace [ID] with ID Value
        tempPDFFilePath = Replace(pdfFilePath, "[ID]", rngID.Value)

        'Create the PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
                               Filename:=tempPDFFilePath
        
        ' START-EMAIL EACH PDF TO EACH CORRESPONDING RECEPIENT IN COLUMN N
    
        With CreateObject("outlook.application").CreateItem(0)
            .To = ws.Cells(i + 3, "N").Value
            .Subject = "This is the subject"
            .Body = "This is the body"
            .Attachments.Add tempPDFFilePath
            .Display 'COMMENT THIS LINE THEN UNCOMMENT .SEND LINE AFTER TESTING
            '.Send
        End With
    
        'END-EMAIL EACH PDF TO EACH CORRESPONDING RECEPIENT IN COLUMN N

    Next i

    'Restart the screen updating
    Application.ScreenUpdating = True

End Sub