VBA Case Study #4 – Invoice Generator Macro
Summary: If you work in the accounts department of a business you might not have access to fancy software… but you regularly use and trust Excel because it’s widely used. What if you’re forced to do lots of repetitive Excel processes by hand? It’s a pain and wastes your time.
I’ve come across tons of manual processes in nearly a hundred projects for a variety of clients. And today I’m going to show you how to automate the process of creating invoices. It’s one of the most common tasks I’ve been asked to automate.
I helped a client to save 37 hours a week with this macro. With my macro they process 750 invoices in 12.5 minutes. And you could achieve similar results. Now, you might worry that it will take a lot of Excel VBA code to do this, but that’s simply not the case.
The macro I use in this article has a few complex parts. So I’ll walk through it step by step. I’m sure you’ll soon get it…and be excited to automate your own document-creation processes!
Download the Sample WorkbookDownload the sample file with VBA code
Invoice-Generator-Macro.xlsm (1,181 Kb)
ContentsClick on a link to jump to that section…
#1 Why did I create the Macro?
#2 What does it do?
#3 How is it better than using Excel without VBA?
#4 Explaining how the code works
#5 Summary of key learning points
#6 About the Author
#1 – Why did I create the Macro?
Most small businesses cannot afford to switch from Excel to other software to create and send invoices. This means they are stuck with using manual processes in Excel that are repetitive, boring, and a huge waste of time.
I’ve built invoice generator macros for many such businesses.
Pro PowerWashing couldn’t afford to purchase any software beyond Microsoft Office. That left them with either Word or Excel to generate invoices, and Outlook to email those invoices to clients.
They stored their client and project data in Excel so it was easiest for them to generate the invoices from Excel too. They didn’t want to bounce between applications.
To do so, they created an invoice template sheet in their client and project data workbook. Whenever it was time to invoice a client, they used client data and product data to fill out the invoice template.
They didn’t realize, however, how much of a pain it would be to manually copy all the following from their data sheets to the invoice template:
- Invoice Number
- Client Name
- Client Address
- Client Phone
- Date of Service
- Description of Service
After filling out the invoice template, they saved it as a PDF, attached the PDF to an email, and manually typed the email’s subject line and body.
The process doesn’t sound like it would take too much time… and it doesn’t for just one invoice. Three minutes is about the average.
But since Pro PowerWashing works about 25 days a month, and their crew of 10 washes about 30 houses a day, that’s 750 houses – and 750 invoices – per month! At 3 minutes per invoice, that’s a staggering 2,250 minutes a month…or 37.5 hours!
The company was spending nearly a full work week every month creating and sending invoices!
In just a few hours, I was able to automate this process with VBA, saving Pro PowerWashing more than $500 in labor each month!
You’ll have the ability to do the same if you follow the steps I took to create the “Invoice Generator” macro.
#2 – What does it do?
Pro PowerWashing updates the following columns on the ”Clients” sheet each time they add a client:
- Phone Number
They then add this data to the ”Invoicing” sheet every time they perform a power washing job:
- Invoice Number
- Client Name
I added an extra column – ”Invoice Sent?” – to the ”Invoicing” sheet. I needed a way to tell whether the service in that row had already been invoiced.
The macro takes every row where ”Invoice Sent” is blank and writes the relevant data to the invoice sheet. If multiple rows exist for the same invoice number, it writes all of the rows to the table on the ”Invoice” sheet:
The sheet has only four rows, as Pro PowerWashing never performs more than four services a month for the same client. If they ever choose to perform more than four services per client, they will have to manually add rows to the table.
The macro itself will still work properly even with additional rows in the table.
After writing data from each row in the “Invoicing” sheet to the invoice, the macro writes “Yes” in the “Invoice Sent” column.
The macro then pulls the relevant client data for the invoice from the “Clients” sheet.
At this point, it’s worth mentioning that the same invoice template is used for each invoice.
Each time the macro finishes filling out one invoice, it saves that invoice as a PDF, creates an email, and attaches the PDF to the email. It then clears the client and invoicing information from the invoice template.
#3 – How is it better than using Excel without VBA?
Considering the nearly 40 hours per week that the manual invoice generation process was taking Pro PowerWashing, how could a macro be any worse?
Not only is the macro not worse, though. It’s nearly two-hundred-times faster!
Instead of taking 37.5 hours a week – which translates to 135,000 seconds – the macro creates all 750 invoices in only 750 seconds! That’s one second each for a total of merely 12.5 minutes!
The macro is also built in a way that keeps the flexibility of the manual process. It can be run for 1 invoice, for 200 invoices, or for 750 invoices. And no matter how many invoices it creates, it always saves a huge amount of time!
#4 – How does the code work?
Now let’s walk through the code that saves all that time!
Download the Sample WorkbookDownload the sample file with VBA code so you can study it yourself.
Invoice-Generator-Macro.xlsm (1,181 Kb)
The macro can be broken into four sections:
- Writing data to each invoice
- Saving the invoice as a PDF
- Generating each email
The last three sections occur within the same loop. I’ll step through their details later.
Dim invoiceNumber As Integer
Dim clientName As String
Dim clientAddress As String
Dim clientPhone As String
Dim clientEmail As String
Dim invoicingDate As Date
Dim invoicingService As String
Dim invoicingPrice As Currency
Dim startTableRow As Integer
Dim InvoiceFile As String
Dim x As Integer
Dim y As Integer
Dim t As Integer
Dim mailApp As Object
Dim myMail As Object
I start the macro with Option Explicit to make sure I’ve declared all variables.
I then declare all variables with their proper type (i.e., Integer, String, and Object). The object type variables are needed for the part of the program that creates the email.
Writing Data to Each Invoice
As I mentioned earlier, I use a loop (in this case, a “For” loop) to go through every row in the “Invoicing” sheet to determine what power washing projects I need to include:
'Loop through and find which line items haven't been invoiced yet
For x = 2 To 200
'If Invoice Sent is blank and there is a date in the first column
If Worksheets("Invoicing").Cells(x, 6) = "" And _
Worksheets("Invoicing").Cells(x, 1) <> "" Then
invoiceNumber = Worksheets("Invoicing").Cells(x, 2).Value
'Set first lines on the Invoice
Worksheets("Invoice").Cells(2, 2).Value = "Invoice #: " & invoiceNumber
Worksheets("Invoice").Cells(3, 2).Value = "Date: " & _
Format(Date, "mmmm dd, yyyy")
In the snippet above, I look through the first two hundred lines of the invoice sheet to find rows where:
- There’s a date in the first column.
- The “Invoice Sent” column isn’t set to “Yes”. This means that no invoice has been generated for the project in that row.
For rows that meet those conditions, I set the “invoiceNumber” variable equal to the invoice number. I then write the invoice number and the current date to the “Invoice” tab.
Note that the macro could easily be adjusted to loop through any number of rows.
Next, I find the related client information from the “Clients” tab:
'Loop through client sheet to find address, phone and email
clientName = Worksheets("Invoicing").Cells(x, 3).Value
For y = 2 To 100
If Worksheets("Clients").Cells(y, 1).Value = clientName Then
clientAddress = Worksheets("Clients").Cells(y, 2)
clientPhone = Worksheets("Clients").Cells(y, 4)
clientEmail = Worksheets("Clients").Cells(y, 3)
'Set the corresponding values on the Invoice sheet
Worksheets("Invoice").Cells(5, 2).Value = clientName
Worksheets("Invoice").Cells(6, 2).Value = clientAddress
Worksheets("Invoice").Cells(7, 2).Value = clientPhone
I loop through the rows in the “Clients” tab to find the address and phone number for each client. I match on the client name used in the “Invoicing” sheet. I also save the client’s email for later use.
I then loop through the “Invoicing” sheet to find any other rows that have the same invoice number:
'Loop through the Invoicing sheet and as long as the invoice number doesn't _
'change, write it to the table on the invoice
startTableRow = 9
Do While Worksheets("Invoicing").Cells(x, 2).Value = invoiceNumber
invoicingDate = Worksheets("Invoicing").Cells(x, 1).Value
invoicingService = Worksheets("Invoicing").Cells(x, 4).Value
invoicingPrice = Worksheets("Invoicing").Cells(x, 5).Value
Worksheets("Invoice").Cells(startTableRow, 2) = invoicingDate
Worksheets("Invoice").Cells(startTableRow, 3) = invoicingService
Worksheets("Invoice").Cells(startTableRow, 4) = invoicingPrice
Worksheets("Invoicing").Cells(x, 6).Value = "Yes"
x = x + 1
startTableRow = startTableRow + 1
'Need to subtract one from x to loop through the row again
x = x - 1
I do this by looping through the sheet and grabbing the date, service, and price for every service with the same invoice number. This only works because the business keeps the table in invoice number order.
It’s worth pointing out that, each time I loop through, I add one to the value of the “x” variable. This allows the loop to keep moving from one row to the next.
At the end of the snippet, though, I subtract one from “x”. I do this so that I loop through the row a second time.
The macro exits the loop when it finds a different invoice number. Therefore, I need to start with that new invoice number during the next loop. And that new invoice number is in the same row I just exited.
Saving invoice as a PDF
Prior to saving the email, the macro needs to save the completed “Invoice” sheet as a PDF:
InvoiceFile = ActiveWorkbook.Path & "/" & ActiveSheet.Name & "_" & _
invoiceNumber & ".pdf"
'Export activesheet as PDF
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=InvoiceFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
The first few rows of the above snippet set the value of the “InvoiceFile” variable to the name of the new PDF. You’ll notice that the code references the full path to which it’s going to save the PDF, separated by slashes.
Note that this format works for PCs, but the path when saving files to a Mac is often different. Since I typically write my macros on a PC, I’ve run across a few instances where my macros haven’t worked correctly on a Mac.
The rest of the code above saves the worksheet as a PDF. It’s generic code that you could easily record and modify.
Generate Emails and Wrap Up
I create the Outlook email using the code below:
'Set Outlook information
Set mailApp = CreateObject("Outlook.Application")
Set myMail = mailApp.CreateItem(0)
.To = clientEmail
.CC = ""
.BCC = ""
.Subject = "Invoice " & invoiceNumber
.HTMLBody = "Please see the attached invoice. Thank you for your business."
I use the same basic code whenever I generate Outlook emails using macros. In this example, I set the email address to the “clientEmail” variable, add the invoice number in the subject line, and hard-code the body text.
As you can see, I use “.display” to tell Outlook to display the email instead of send it. Pro PowerWashing wants to review each email before sending. If I change “.display” to “.send”, the macro will automatically send each email.
Now we’re almost done!
'Clear data in table on invoice
For t = 9 To 12
Worksheets("Invoice").Cells(t, 2).Value = ""
Worksheets("Invoice").Cells(t, 3).Value = ""
Worksheets("Invoice").Cells(t, 4).Value = ""
MsgBox ("All emails have been generated.")
The snippet above clears all the data that the macro wrote to the table in the invoice. It then moves to the next value of “x” to keep looping.
If it’s finished looping, though, a message box tells the user it has generated all the emails. It then ends the subroutine.
Not too bad, right? And if you want to see how I improved the workbook to make it more user-friendly, check out “VBA Case Study #5 – Making a User-Friendly Invoice Generator with Input Forms“
#5 – Summary of key learning points
As you can see, automatically creating invoices in Excel is not only possible – it’s realistic for anyone willing to put in a bit of time to learn and write the necessary VBA!
Creating invoices isn’t the only use for this sort of macro, though. You could create and email orders, letters, or any other document that you could manually create in Excel.
The most complex part of this code is the loops. Building loops requires focus more than anything. But if you build the code in small, manageable chunks, I’m sure you can build complicated loops!
Remember never to be satisfied with time-consuming manual processes. If you keep looking for ways to automate Excel processes, you can find ways to save hundreds — or even thousands — of dollars a month, too!
#6 – About the Author
Chris Cox – Two decades ago, Chris taught himself VBA to help him eliminate annoying manual Excel tasks. Twenty years later, Chris has turned that experience into a second career as a highly-rated independent consultant specializing in Excel and Excel automation. He loves exceeding client expectations by making their processes as efficient as possible!
Chris is also passionate about writing, editing, and proofreading, and he’s successfully done all three both in full-time employment and as a consultant.
Chris currently works as a full-time as an IT analyst for a global asset management firm. He has also worked in government, healthcare, and consulting. He writes fiction in his spare time.
Get Free Macro E-Book
Read my FREE quick start guide. It shows my easy
3-stage process to record your first VBA Macro in Excel.
Plus you get the latest Excel and VBA tips and news in a weekly email. You can unsubscribe at any time.