VBA: Turn Every Excel Row into a PDF and Email It

Hello again, tech enthusiasts! 👋

Imagine you have a list of 40 customers. Each one needs a statement: their name, their invoice number and the amount they owe. Today you fill a template by hand, save it as PDF, open Outlook, attach the file and type the email. Forty times. Your coffee gets cold.

In this post, we will write a small VBA macro to do it for you. VBA (Visual Basic for Applications) is the programming language built into Excel. A macro is a small program that clicks and types for you, much faster than a person.

We will be careful too. The macro will show each email first instead of sending it. You check it, and you click Send.

What you need

  • Excel and Outlook (the desktop apps) on Windows
  • A workbook saved as .xlsm (macro-enabled)
  • A sheet called Data with your list
  • A sheet called Template with your nice design
  • A folder for the PDFs, for example C:\Reports\Statements

Step 1: Prepare the Data sheet

In the Data sheet, put headers in row 1 and one customer per row:

  • Column A: Customer Name
  • Column B: Email
  • Column C: Invoice Number
  • Column D: Amount

Use test data first, like customer1@example.com. Never test with real customers.

Step 2: Prepare the Template sheet

Design the Template sheet the way you want the PDF to look. Leave empty cells for the values. In this example:

  • B3 = customer name
  • B4 = invoice number
  • B5 = amount

Also set the print area: select your design, then go to Page Layout > Print Area > Set Print Area. The PDF will include only this area.

Step 3: Add the macro

Press Alt + F11 to open the VBA editor. Click Insert > Module and paste this code:

Option Explicit

Sub CreatePdfsAndEmails()
    Dim wsData As Worksheet, wsTpl As Worksheet
    Dim outApp As Object, mail As Object
    Dim lastRow As Long, r As Long
    Dim folder As String, pdfPath As String, safeName As String

    Set wsData = ThisWorkbook.Worksheets("Data")
    Set wsTpl = ThisWorkbook.Worksheets("Template")
    folder = "C:\Reports\Statements\"   ' must end with a backslash

    ' Find the last used row in column A
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    ' Start Outlook (or connect to it if it is already open)
    Set outApp = CreateObject("Outlook.Application")

    For r = 2 To lastRow
        ' 1. Fill the template with this row's values
        wsTpl.Range("B3").Value = wsData.Cells(r, "A").Value
        wsTpl.Range("B4").Value = wsData.Cells(r, "C").Value
        wsTpl.Range("B5").Value = wsData.Cells(r, "D").Value

        ' 2. Save the template as a PDF
        safeName = Replace(wsData.Cells(r, "C").Value, "/", "-")
        pdfPath = folder & "Statement_" & safeName & ".pdf"
        wsTpl.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, _
            Quality:=xlQualityStandard, OpenAfterPublish:=False

        ' 3. Create the email and attach the PDF
        Set mail = outApp.CreateItem(0)   ' 0 = a new mail item
        With mail
            .To = wsData.Cells(r, "B").Value
            .Subject = "Your statement - Invoice " & wsData.Cells(r, "C").Value
            .Body = "Dear " & wsData.Cells(r, "A").Value & "," & vbCrLf & vbCrLf & _
                    "Please find your statement attached." & vbCrLf & vbCrLf & _
                    "Best regards"
            .Attachments.Add pdfPath
            .Display   ' show the email; change to .Send only when you are sure
        End With
        Set mail = Nothing
    Next r

    MsgBox "Done! " & (lastRow - 1) & " PDFs created.", vbInformation
End Sub

Step 4: Understand the important lines

  • End(xlUp) is like pressing Ctrl + Up from the bottom of the sheet. It finds the last row with data, so the macro works for 5 rows or 500.
  • ExportAsFixedFormat is the same as File > Save As > PDF, but in code.
  • CreateObject("Outlook.Application") opens a connection to Outlook. We do not need any special references with this method.
  • .Display opens each email in a window. Nothing leaves your computer until you click Send.

Step 5: Run it

Close the editor. In Excel, press Alt + F8, choose CreatePdfsAndEmails and click Run. Check the folder for PDFs, and check each email window.

When you trust the result, you can change .Display to .Send. Do this only after many good test runs. A sent email cannot come back.

Common problems

  • “Run-time error 1004” on ExportAsFixedFormat. → The folder does not exist, or the file name has a bad character like / or :. Create the folder and clean the name, like we did with Replace.
  • “ActiveX component can’t create object.” → Outlook desktop is not installed. This method needs the classic desktop Outlook.
  • The PDF is empty or cut in half. → Set the print area on the Template sheet and check Page Layout > Fit to 1 page.
  • Outlook shows a security warning. → This is Outlook protecting you. Keep your antivirus up to date and do not turn the warning off; just click Allow when you started the macro yourself.

Wrap-up

With about 40 lines of code, a one-hour job becomes a two-minute job. Your template stays pretty, and every email is checked before it goes.

Next, try adding a column called Sent and let the macro write the date there after each email. Then you always know who got their statement. Have fun!

fsaeed

, , , ,