📎 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

“`visual-basic
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 = “

Dear ” & wsD.Cells(r, 3).Value & “,

Please find attached invoice ” & _
wsD.Cells(r, 1).Value & “. Thank you for your business.

Regards,
Accounts

”
.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.