VBA Project: Generate PDF Invoices From Excel and Email Them Automatically

📎 This article includes 1 downloadable practice file ↓

Many small businesses type invoices one by one. With a template and 60 lines of VBA, Excel produces every invoice as a PDF and emails it to the customer. This project pulls together ranges, loops, error handling and Outlook automation.

The workbook

• Invoices sheet (data): Invoice No, Date, Customer, Email, Item, Qty, Rate, GST %, Sent (Y/blank).
• Template sheet: a nicely formatted invoice with named cells inv_no, inv_date, cust, item, qty, rate, gst; totals are formulas in the template itself.
• Log sheet: what was sent and when.

The macro

Option Explicit

Sub CreateAndSendInvoices()
    Dim wsD As Worksheet, wsT As Worksheet, wsL As Worksheet
    Dim r As Long, lastRow As Long, folder As String, pdf As String
    Dim ol As Object, mail As Object, sent As Long

    Set wsD = Sheets("Invoices"): Set wsT = Sheets("Template"): Set wsL = Sheets("Log")
    folder = ThisWorkbook.Path & "\Invoices\"
    If Dir(folder, vbDirectory) = "" Then MkDir folder

    On Error GoTo Fail
    Application.ScreenUpdating = False
    Set ol = CreateObject("Outlook.Application")

    lastRow = wsD.Cells(wsD.Rows.Count, 1).End(xlUp).Row
    For r = 2 To lastRow
        If wsD.Cells(r, 10).Value <> "Y" And wsD.Cells(r, 1).Value <> "" Then
            ' 1. fill the template
            wsT.Range("inv_no").Value = wsD.Cells(r, 1).Value
            wsT.Range("inv_date").Value = wsD.Cells(r, 2).Value
            wsT.Range("cust").Value = wsD.Cells(r, 3).Value
            wsT.Range("item").Value = wsD.Cells(r, 5).Value
            wsT.Range("qty").Value = wsD.Cells(r, 6).Value
            wsT.Range("rate").Value = wsD.Cells(r, 7).Value
            wsT.Range("gst").Value = wsD.Cells(r, 8).Value
            wsT.Calculate

            ' 2. export to PDF with a clean file name
            pdf = folder & CleanName(wsD.Cells(r, 1).Value & "_" & wsD.Cells(r, 3).Value) & ".pdf"
            wsT.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdf, Quality:=xlQualityStandard, OpenAfterPublish:=False

            ' 3. email it
            Set mail = ol.CreateItem(0)
            With mail
                .To = wsD.Cells(r, 4).Value
                .Subject = "Invoice " & wsD.Cells(r, 1).Value & " from " & ThisWorkbook.Sheets("Template").Range("company").Value
                .HTMLBody = "<p>Dear " & wsD.Cells(r, 3).Value & ",</p><p>Please find attached invoice <b>" & _
                            wsD.Cells(r, 1).Value & "</b>. Thank you for your business.</p><p>Regards,<br>Accounts</p>"
                .Attachments.Add pdf
                .Display          ' change to .Send once you have tested
            End With

            ' 4. mark and log
            wsD.Cells(r, 10).Value = "Y"
            wsL.Cells(wsL.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 3).Value = _
                Array(Now, wsD.Cells(r, 1).Value, wsD.Cells(r, 4).Value)
            sent = sent + 1
        End If
    Next r

Done:
    Application.ScreenUpdating = True
    MsgBox sent & " invoice(s) created" & IIf(sent > 0, " and emailed.", "."), vbInformation
    Exit Sub
Fail:
    Application.ScreenUpdating = True
    MsgBox "Stopped at row " & r & ": " & Err.Description, vbExclamation
End Sub

Private Function CleanName(ByVal s As String) As String
    Dim ch As Variant
    For Each ch In Array("\", "/", ":", "*", "?", """", "<", ">", "|")
        s = Replace(s, ch, "-")
    Next ch
    CleanName = Left(Trim(s), 120)
End Function

How it stays safe

• The Sent column means running the macro twice never double-sends.
• .Display first: you see each email before anything goes out. Switch to .Send after a test run.
• Error handling restores screen updating and tells you exactly which row failed.
• CleanName removes characters Windows does not allow in file names.

Upgrades

• Multiple line items: store lines on a second sheet and filter them per invoice into the template.
• Amount in words on the invoice with a small LAMBDA or VBA function.
• Run it from a button on the Invoices sheet.

New to the pieces used here? See loops, error handling and sending email from Excel.