In this article, I will show you how to integrate the functionality of WhatsApp within Excel. You will be able to use Excel data from the worksheet to send personalized WhatsApp messages directly from Excel. Each message is customized with different values from your saved data.
I will do that first by using a simple HYPERLINK function. Then, I will seek the help of ChatGPT and Open AI to generate a VBA code that can do the same job.
The work situation
I have a worksheet that stores patients’ information in a table.
In this worksheet, I have a First name, a Last name, a Phone number, an Appointment Date and Time in columns A to E. Phone numbers have the country code and area code followed by the number, this is needed to send WhatsApp messages
Building a Personalized Message
I start by building the custom message for each record to send a reminder with the upcoming appointment. I will do that in column G where I will combine some data from the table with some text (in double quotation), using the joining operator of Excel the ‘&” symbol, as follows:
The function in G2 reads:
=”Hello “&A2&” Your appointment is on “& D2&” at “&E2
And when I hit enter it looks like this:
As you can see, I lost the Date and Time formatting, and I will fix that with 2 TEXT functions. I also want to split my message on 2 lines, which requires a CHAR(10) function.
So after editing my function in cell G2, it reads:
=”Hello “&A2&CHAR(10)&”Your appointment is on “&TEXT(D2,”dd-mmm-yy”)&” at “&TEXT(E2,”hh:mm AM/PM”)
And when I hit enter, it looks like this:
Now I can copy my function down by dragging to create the custom message for all the records in the table.
Send Messages to WhatsApp
My next step is to create the clickable link that will send the message directly to WhatsApp using the Phone number stored in Column C of the table. I will do that in column H.
The function that I use (in cell H2) is a HYPERLINK function and reads:
=HYPERLINK(“https://api.whatsapp.com/send?phone=”&C2&”&text=”&G2,”Send To WhatsApp”)
Where:
C2 ► Is the Phone number from the table.
G2 ► is the Custom message we built in the previous step.
And here is how it looks like in H2 when you hit Enter
You may copy the 2 previous functions down to all records in the table.
The HYPERLINK function calls the WhatsApp application that you should have Downloaded to your desktop from the AppStore.
When you click on “Send to WhatsApp” in H2, it will open in your default browser. This window also allows you to Download WhatsApp for desktop, in case you do not already have it.
Click Open to proceed
Setting up WhatsApp
WhatsApp now opens with all your chat history and contacts.
If your cell phone is not already connected to the desktop app ► When you hit “Open” a WhatsApp window opens. Click on Get Started.
You will see a QR code in the WhatsApp window.
Open WhatsApp on your Cell phone ► Go to Settings ► select “Linked Devices” ► Click on Link Device.
Scan the QR code on your desktop with your cell phone ► it loads your WhatsApp account to your desktop App where you see your contacts and all your chat history.
Back to Excel, click on the same link one more time “Send To WhatsApp”. You will see the message coming from cell G2 in Excel in the message Box in WhatsApp.
Click the Send button in WhatsApp (like any message) and Boom ► The message is sent to the recipient.
To send other messages you do not need to repeat all the previous preparation steps, it’s just a single click on “Send To WhatsApp” in the corresponding cell in column H in Excel
Find an alternate solution on ChatGPT.
ChatGPT is “an AI-powered chatbot developed by OpenAI, based on the GPT (Generative Pretrained Transformer) language model. It uses deep learning techniques to generate human-like responses to text inputs in a conversational manner.” ChatGPT was released on 30 November 2022.
I logged to my account on ChatGPT to ask a question searching for an alternate solution to the previous work situation based on artificial intelligence.
To the left Side ► Click on New Chat.
I typed my question in the lower box and hit enter.
And here is the answer returned by ChatGPT:
To create a hyperlink function in column H that sends personalized WhatsApp messages to the phone numbers in column C with the wording in column G, you can use the following formula:
=HYPERLINK(“https://wa.me/”&C2&”?text=”&G2,”Send WhatsApp message”)
This formula concatenates the WhatsApp URL (https://wa.me/) with the phone number in column C (&C2) and the message in column G (&”?text=”&G2). The resulting URL will open the WhatsApp app with a new chat window containing the phone number and message.
You can copy this formula to all cells in column H to create a hyperlink for each phone number in column C.
Note that this formula assumes that the phone numbers in column C are in international format (including the country code), and that the WhatsApp app is installed on the device where the hyperlink is clicked.
This formula is different than the one I used for my HYPERLINK function. Mine calls a WhatsApp API.
So now I will ask to generate a VBA code by typing:
Can I get a VBA code instead of the hyperlink function?
When I hit enter, the VBA code is generated as follows:
It also gives me further instructions for looping over the different records:
To test the code, I need to copy it (by clicking on “Copy Code” in the upper right corner and then go to excel ► Open the Visual Basic editor (ALT + F11) ►Create a module (ALT + I + M) and paste the code.
Here is the VBA code by ChatGPT
Sub SendWhatsAppMessage()
Dim i As Long
Dim phone As String
Dim message As String
For i = 2 To Range("C" & Rows.Count).End(xlUp).Row
phone = Range("C" & i).Value
message = Range("G" & i).Value
If phone <> "" And message <> "" Then
ActiveWorkbook.FollowHyperlink ("https://wa.me/" & phone & "?text=" & message)
End If
Next i
End Sub
I did not test the code, and may be it needs some further fine tuning. Let me know in a comment how it works.
But, do you agree that we are lucky to live this revolutionary functionality?
