Use ChatGPT to write macros for Outlook
Q: What can I do with ChatGPT and Microsoft macros outside of Excel?
A: ChatGPT has taken parts of the technology and accounting professions by storm since its launch in November 2022. I have held many discussions, sessions, webinars, etc., on ChatGPT, and the JofA has published several articles about the generative AI chatbot. My fellow Tech Q&A writer Kelly Williams, CPA, Ph.D., wrote in January about using ChatGPT with Excel. This issue of JofA features an article from Fidel Dhana, CPA, on “Writing Advanced Excel Macros with ChatGPT-4.”
Both those articles dealt with Excel macros. But how would ChatGPT fare when working on macros for other applications within Microsoft Office? To find out, I built a macro for Outlook. Let’s recap that process and cover some of what to be wary about when using ChatGPT.
My goal was to create a macro to block out the working hours of the next week — which would be great to prevent Calendly from adding more appointments to my calendar when it’s already packed.
This walkthrough, developed in late summer, used the free version of ChatGPT, which uses GPT-3.5. GPT-4 is more advanced and can help build more complex macros but requires a paid ChatGPT account. But the free version was good enough for this task.
To begin, I logged in to my Openai.com account and opened a new chat. I started with a basic prompt: “Make a macro that will block out the week on my calendar.” I got the following output:
There is more to the screenshot, but I truncated it for this article. Can you see the first problem? The response is using Python as the code. ChatGPT has the internet of knowledge available but does not know how to use it to help you. So, I needed to give it more context on my macro request.
I gave it a follow-up with: I need it for Outlook.
At this point, ChatGPT is responding with text about APIs and authentication and is still using Python for the code (not shown in the screenshot). The context I added was still not enough to get what I wanted. Instead, it looked like ChatGPT was assuming that I wanted to use Outlook in the web browser to create appointments to block my calendar.
I tried some more context with: “I need it for Outlook Desktop.”
At this point, ChatGPT was confused, and I only made it worse. ChatGPT based its responses on the context of the previous prompts I had given it. I needed to start with a clean slate.
On my second attempt, I decided to give ChatGPT much more context. It’s important to note that macros for Office desktop applications are written in Visual Basic.
I used the following prompt: “You are an expert macro programmer in Visual Basic for the Microsoft Outlook desktop application. Create code that will create an appointment every weekday for next week. The title of the meeting will be “Block.” Set the time to be from 8 a.m. to 6 p.m. and set the appointment to “Busy.”
The response to that prompt was much improved. As shown in the screenshot below, ChatGPT provided a VBA code snippet and added additional text below the code, giving brief instructions on how to add the macro (not shown in the screenshot). The response also included a warning about how macros can have unintended consequences — something I had experienced firsthand with a macro that generated appointments but created them in the year 2146.
With that in mind, I clicked on Copy Code and went to Outlook, pressing Alt + F11 to open Microsoft Visual Basic. I then right-clicked in a blank area in the Project window and went to Insert → Module (see screenshot below). Next, I pasted in the code.
I tested the macro by going to the menu and clicking on Run → Run Sub/User Form. The macro successfully created the appointments for each weekday.
I added the macro to the Quick Access toolbar to generate those blocking appointments conveniently. Consider these three tips when creating a ChatGPT prompt.
- Start with giving ChatGPT a purpose. I said that, “You are an expert macro programmer in Visual Basic for the Microsoft Outlook desktop application.” This gave it a focus on what it would be doing.
- Write a prompt that sets a baseline for what you want to create. In my case, I established an initial task with “Create code that will create an appointment every weekday for the next week.”
- Round out the prompt with key details. With my macro, I added some details to the task: The title of the meeting will be “Block.” Set the time to be from 8 a.m. to 6 p.m. and set the appointment to “Busy.”
I asked ChatGPT what makes a good prompt, and it responded with a list of tips. The first three tips were “Be Specific,” “Start Broad then Narrow Down,” and “Provide Context.”
I didn’t do those in my first attempt to produce the Outlook calendar block macro, but I kept those ideas in mind when I crafted the prompt that worked.
ChatGPT can also modify the macros it creates. With the Outlook macro, I changed my mind and decided that I wanted to be able to choose the week. I asked ChatGPT, “Can you modify the code so that it prompts me to block out this week or next week?” ChatGPT regenerated the code, and it worked. When I plugged it into Outlook, it allowed me to block out the current week or the next week.
About the author
Wesley Hartman is the founder of Automata Practice Development and director of technology at Kirsch Kohn & Bridge LLP.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
