How to answer emails using a ChatGPT macro in Outlook

In this blog post, we provide a detailed and easy-to-follow guide for users who want to connect Outlook with ChatGPT (OpenAI) using VBA Macros. The guide will walk you through the installation process and any necessary configurations, so you can start using ChatGPT to create replies for your email messages in Outlook automatically. Although not a normal topic for this blog, we aim to help users save time and improve their productivity by leveraging the capabilities of ChatGPT within Outlook.

What is ChatGPT and How Does it Work in Microsoft Outlook?

ChatGPT is an AI tool that predicts text based on the context of your message. In Outlook, you can use VBA code to integrate ChatGPT into your email client to create suggested replies to emails or write new ones based on simple prompts you give it.

ChatGPT can analyze an email’s tone, style, and subject matter to predict the most likely reply. It uses language models such as GPT-3, which have been trained on large amounts of text data and can predict the most likely words or phrases to follow a given context, and, together with extra prompts from yourself, come up with an appropriate reply.

You can customize ChatGPTs responses by adding prompts to your code, choosing the language model that suits you best, and adjusting settings to fit your writing style.

Using ChatGPT to create answers for you in Outlook saves time, increases productivity, and maintains consistency in your emails. Its customizable settings and language support make it a powerful tool for users in various settings.

How to Connect ChatGPT API with Outlook Using VBA

If you want to use ChatGPT in Outlook, you’ll need to connect to the OpenAI API using VBA. Here are the steps to follow:

Sign up for an OpenAI API key

To use the OpenAI API, you’ll need to sign up for an API key. Go to the OpenAI website and create an account to get started. OpenAI provides a free trial of their API.

Install the JSON parser library

Outlook doesn’t natively support JSON, so you’ll need to install a JSON parser library to parse the responses from the OpenAI API.

1. Download the VBA-JSON library from GitHub by clicking the “Code” button and selecting “Download ZIP.”
2. Extract the ZIP file to a folder on your computer.
3. Open Outlook and go to the VBA Editor by pressing Alt + F11.
4. In the VBA Editor, click on “File” from the menu bar, and then select “Import File”.
5. In the Import File window, click “Browse” and navigate to the folder where you extracted the VBA-JSON library.
6. Select the “JsonConverter” BAS file and click “Open”.

You have now successfully installed the VBA-JSON library.

Insert a new Module into Outlook Visual Editor

1. Open Outlook and press “Alt + F11” to open the Visual Basic Editor.
2. In the Project Explorer window on the left-hand side, select the project to which you want to add the module. By default, the project is named “VbaProject.OTM”.
3. Right-click on the project name and select “Insert > Module” from the context menu. This will insert a new module into the project.

In the Code Editor window, you can write your VBA code in the module. You can also rename the module by right-clicking on it in the Project Explorer window and selecting “Rename”.

Once you have written your VBA code, save the project by pressing “Ctrl + S” or by selecting “File > Save” from the menu.

Add VBA References

To run this macro in Outlook, you need to add a reference to the Microsoft Scripting Runtime library. Here are the steps to add the reference:

1. Open the Visual Basic Editor in Outlook by pressing “Alt + F11”.
2. In the Visual Basic Editor, select “Tools” from the menu bar and click on “References”.
3. In the References dialog box, scroll down and check the box next to “Microsoft Scripting Runtime”.
4. Click “OK” to close the References dialog box.
5. Save your VBA code and close the Visual Basic Editor.

Create the ChatGPT Macro for Outlook using VBA

In Outlook, VBA (Visual Basic for Applications) is a programming language used to automate tasks and customize the behavior of the Outlook application. VBA can be used to create new emails, modify existing emails, automate sending and receiving of emails, and perform other actions within the Outlook environment. In this case, VBA will create replies and insert the text responses from ChatGPT. Copy the VBA code shown at the bottom of this page and paste it into the module created in the previous step.

How to customize the ChatGPT VBA macro for Outlook

The most important parts of the ChatGPT Outlook code to modify are:

OpenAPIKey = “Bearer paste_api_key_here” – for the VBA code to connect to ChatGPT, you need to replace the text paste_api_key_here with you actual API key.

Write a reply for this email I received, don’t include any greetings, don’t include any names, tell them that: This part of the text is your way to give ChatGPT special instructions which will impact the answer ChatGPT provides. This prompt works well for me. The don’t include any greetings and don’t include any names part is important since the VBA code will do this using the name from the senders email address. The last part, “tell them that” is also important as it prepares ChatGPT for the instructions you type into the pop up message box.

Pro Tip: You can use ChatGPT to help you modify the VBA code provided below! Copy a section of the code, or all of it, and tell ChatGPT what changes you want it to make to the code, and ChatGPT will provide you with updated code! Obviously you will need to use the online version of ChatGPT for this. If you get an error when running the VBA Macro, click the Debug option and it will show you which row of code is generating the error. You can also copy that row and paste it into ChatGPTs online chat and tell it you are getting an error with that line of code. ChatGPT may be able to suggest an immediate solution.

How to use the ChatGPT VBA Macro for Outlook to answer emails

There are two ways to trigger the Macro. The first thing to do is to select the email you want to reply to using ChatGPT. To trigger the macro, you can run it from Macro box by pressing the ALT and F8 buttons in Outlook. Select the ChatGPT macro and click the Run button.

An easier way is to add a button to the Quick Access bar so you can run it with a click of a button.

To add a macro button to the Outlook quick access toolbar, follow these steps:

1. Open Outlook and click on the down arrow at the end of the Quick Access Toolbar (QAT), located above the Ribbon.
2. Select “More Commands” from the drop-down menu. This will open the Outlook Options dialog box.
3. Select “Customize Ribbon” from the left in the Outlook Options dialog box.
4. Click on the “Quick Access Toolbar” tab.
5. Under “Choose commands from:”, select “Macros”.
6. Select the macro you want to add to the QAT from the list of available macros.
7. Click on the “Add >>” button to move the selected macro to the “Customize Quick Access Toolbar” column on the right.
8. If desired, use the up and down arrows to change the position of the macro button in the QAT.
9. Click “OK” to close the Outlook Options dialog box.

That’s it! The macro button should now appear in the Quick Access Toolbar, and you can click it to run the macro with a single click.

The VBA Code to reply with ChatGPT in Outlook

Sub ReplyWithGPT()

Dim msg As Outlook.MailItem
Dim olReply As Outlook.MailItem
Dim MsgReply As Outlook.MailItem
Dim lGreetType As Long
Dim myItems As Outlook.Items
Dim myItem As Object
Dim intEnd As Long
Dim strInput As String
Dim strOutput As String
Dim i As Long
Dim chr As String

' Set API URL and key
Dim OpenAPIURL As String
OpenAPIURL = "https://api.openai.com/v1/completions"
Dim OpenAPIKey As String
'Change API key here, insert after the word "Bearer "
OpenAPIKey = "Bearer paste_api_key_here"

'Check if message opened or in list view
Select Case TypeName(Application.ActiveWindow)
    Case "Explorer"
        Set msg = ActiveExplorer.Selection.Item(1)
    Case "Inspector"
        Set msg = ActiveInspector.CurrentItem
    Case Else
End Select

On Error GoTo 0

If msg Is Nothing Then
    GoTo ExitProc
End If

'Get most recent response

strHTML = msg.Body

' Find the end position of the text before "From:" or "De:"

intEnd = InStr(strHTML, "From:")
'If from not found, try spanish (you can change this to another language by replacing "De:" in the code below with another language for the From field.
If intEnd = 0 Then
intEnd = InStr(strHTML, "De:")
End If
'If email is not a reply
If intEnd = 0 Then
    strText = msg.Body
Else
 ' Extract the text before "From:" or "De:" from the message body
    strText = Left(strHTML, intEnd - 1)
End If

'Convert it to a string

' Input string with special characters
strInput = strText

' Remove special characters
For i = 1 To Len(strInput)
    chr = Mid(strInput, i, 1)
    If chr Like "[a-zA-Z0-9 ,.]" Then
        strOutput = strOutput & chr
    End If
Next i

'Create Reply
Set olReply = msg.ReplyAll

'Put reply into email
myReply = "creating_reply..."

With olReply
  .HTMLBody = Replace(olReply.HTMLBody, "<o:p>", myReply & "<o:p>", , 1)
End With

olReply.Display

xCommand = InputBox("Enter additional commands or clarifications")

' Create XMLHTTP object
Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")

' Set API request data
Dim OpenAIData As String
OpenAIData = "{""model"": ""text-davinci-003"", ""prompt"": ""Write a reply for this email I received, don't include any greetings, don't include any names, tell them that " & xCommand & ": " & strOutput & """, ""temperature"": 0.3, ""max_tokens"": 400, ""top_p"": 1, ""frequency_penalty"": 0, ""presence_penalty"": 0}"

' Send API request
xmlhttp.Open "POST", OpenAPIURL, False
xmlhttp.setRequestHeader "Content-Type", "application/json"
xmlhttp.setRequestHeader "Authorization", OpenAPIKey
xmlhttp.Send OpenAIData

' Get API response
Dim responseText As String
responseText = xmlhttp.responseText
      
'Parse the response data to extract the text
Dim parsedResponse As Object
Set parsedResponse = JsonConverter.ParseJson(responseText)
    
responseText = parsedResponse("choices")(1)("text")
    
'Format the response text with HTML new lines
responseText = Replace(responseText, "\n", "<br>")

' Get sender's first name
Dim SendersName As String
SendersName = Split(olReply.To)(0)

If Right(SendersName, 1) = "," Then
    SendersName = Left(Split(olReply.To)(1), Len(SendersName) - 1)
End If

If InStr(SendersName, "@") > 0 Then
    SendersName = Left(SendersName, InStr(SendersName, "@") - 1)
    SendersName = StrConv(SendersName, vbProperCase)
End If

'Create greeting
FirstLine = "Hello " & StrConv(SendersName, vbProperCase) & " <br><br>" & responseText & "<br> <br>Many thanks, " & "<br>"
        
xBody = olReply.HTMLBody
xBody = Replace(xBody, "creating_reply...", FirstLine)

With olReply
    .HTMLBody = xBody
End With

ExitProc:
' Clean up variables
Set msg = Nothing
Set olReply = Nothing
Set MsgReply = Nothing
Set myItems = Nothing
Set myItem = Nothing
Set xmlhttp = Nothing
Set parsedResponse = Nothing
End Sub

Are you interested in other ways to streamline admin tasks like answering emails? Read our introduction to creating online procedures to deskill and improve the systems that run your business!