Last week, my team was asked to provide all invoices in PDF from the last eight months. While we have no trouble accessing them on our cloud accounting software and Outlook, we don’t currently have them neatly saved in a centralized folder. On top of that, they wanted the PDF files named in a way that allows for easy reference and search (e.g., invoice number & customer name). The straightforward approach? Manually sifting through countless emails to download each invoice individually. This method would work but it would take hours. We will not waste valuable time this way on my watch, so I was determined to leverage ChatGPT to find a way to accomplish this in minutes: search through the Outlook inbox with thousands of emails, download hundreds of invoices, and name them in a meaningful way.
Spoiler alert? I did it with VBA (Visual Basic for Applications) even though I only have a basic understanding of the coding language designed by Microsoft. It saved my company hours, and they have ChatGPT, the most popular generative AI tool, to thank.
By sharing this example I encountered as a CPA working for a tech company, I want to encourage you to experiment with AI tools to improve your efficiency, productivity, and accuracy. However, I should always remind you to be extremely careful with your company data. Never share confidential company data with external tools.
Want to learn more ways to leverage AI in Finance? Read more posts like this here.
Sample ChatGPT Prompts for VBA & Outlook
- “Please provide a VBA script to download all attachments from Outlook emails with the title ‘Invoice INV’.”
- “Modify the VBA script to work with a shared inbox named ‘AR’ and target emails with a specific subject.”
- “Adjust the script to select a subfolder named ‘Actioned’ within the ‘Inbox’ of the ‘AR’ shared mailbox.”
- “The script is yielding a ‘Type Mismatch’ error. How can I correct this?”
- “I need the VBA script to save only PDF attachments and rename them according to the email subject, ensuring that all special characters are removed and the filename length is managed.”
- “Update the VBA script to process only emails received after January 1, 2024.”
- “I encountered a ‘Path does not exist’ error when running the script. How do I resolve this?”
How I Wrote VBA Code for Outlook With ChatGPT
Problem: search through the Outlook inbox with thousands of emails, download hundreds of invoices and name them in a meaningful way
Step #1. Give ChatGPT Your Prompt
Since I did not actually know what the solution to my problem was, I gave ChatGPT a very simple prompt as shown in my screen cap. It suggested VBA and since I have experience with the coding language before, I decided to go ahead with its first suggestion.
- If you have not heard of VBA before, here is a great YouTube tutorial for beginners
- Alternatively, you can ask ChatGPT for detailed step-by-step instructions if you want to proceed without any prior knowledge (e.g., how to open the VBA editor on Outlook).
Step #2. Tweak the VBA Code
In its response to my first prompt, ChatGPT also gave me the VBA code I could use. Under the code, it provided me with instructions on what fields to update. For example, since I did not provide the directory of the folder I wanted this code to save the invoice PDFs to, it left a placeholder for me (SaveFolder = “C:\Your\Save\Directory\”). This allowed me to avoid sharing any company data, such as our file directory, without affecting the quality of the results.
Step #3. Troubleshoot with ChatGPT
I then encountered issues with identifying the name of the shared mailbox (it turned out to be the exact name as shown on my Outlook), so I asked ChatGPT for assistance on how to identify the name I needed to tweak the VBA code successfully.
Step #4. Execute the VBA Code
I executed the code and it ran successfully without a hitch. Once I confirmed the basic version worked, I went ahead with modifying it further so it is tailored to our needs.
Below is the list of parameters I added to further enhance the VBA code:
- Name the files after the title of the email, and if the title of the email contains invalid characters such as ‘?’ or ‘”‘, replace it with an empty string. This way, I will not encounter issues with the file name, as file names do not allow invalid characters.
- Limit the search to emails sent on or after a certain date (e.g., when I run this code in 3 months, I only want it to search through emails sent after Jan 1, 2024).
