Send Bulk Messages to Whatsapp Contacts using Excel


👤 Diwas Poudel    🕒 17 Dec 2022    📁 TECH

Introduction 

Do you enjoy texting your friends using WhatsApp messenger? Or Do you have a WhatsApp group of your friends or colleagues? If yes, then you know what WhatsApp is. Have you ever wondered how are messages sent to many people in your WhatsApp contact at once with the same or different message with a single click?

The process of sending messages to many people at once with a single click is known as bulk messaging. Let's find out why and how it is done.

Why do bulk messaging?

Lets us consider a scenario, where you need to remind your class group about our class assignment. So, you send the notice in the group chat. What do you think will it happen? That won’t be effective as it will be buried inside tons of messages. Some introverted friends might have muted the group and won’t be notified. Don’t you think it would be better to send each and everyone message regarding the assignment?

In another situation, Suppose you are a merchant who sells utensils and other products. You have the contacts of all your customer saved in your mobile and they all have WhatsApp. Now, this time you have a new product available in your market. You want to notify your customers about the product. So what do you do?

The answer in both cases is bulk messaging using WhatsApp. Here are a few reasons why it is used:

  • Helps in the marketing of the product
  • An easy approach to customers
  • Creating alerts
  • Easier to use 
  • Cost-effective

How to send bulk messages?

Bulk messages can be sent in many different ways. One of the ways is using Microsoft Excel. 

Microsoft Excel is a spreadsheet software available in Windows, macOS, Android, and iOS. It mainly features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA).

In this article, we will step by step learn about how to use MS Excel to send bulk messages to Whatsapp contacts. In this tutorial, we will be using a macro programming language called VBA.

Steps for Sending bulk messages to Whatsapp using Excel

Steps are as follows:

1 Enabling the developer option in the MS-Excel

No Developer Option
fig. No Developer Option in Excel
  • Open Ms-Excel 
  • Go to the file menu in the topmost part of Excel. Click on it.
  • In the end, you will see the Options.
Excel Options
fig. Excel Options
  • Click on options. Then a dialog box will open up.
  • On the left side of the dialog box, you will see Customize Ribbon.
Customize Ribbon Excel
fig. Customize Ribbon 
  • Then put a check on the Developer.
Checkin Developer Option Checkbox
fig. Checkin Developer Option Checkbox

If everything goes well, the topmost tab will look like this

Developer Option enabled in Excel
fig. Developer Option enabled in Excel

Congratulations on completing the first step.

2 Write the Phone number and messages on the MS Excel sheet to whom you want to send messages and what you want to say.

Right Click on Phone Number and Messages Column
fig. Right Click on Phone Number and Messages Column

Be sure to keep the country code in front of the actual phone number. Being the large numbers Excel converts it to the power of 10.

You can format your phone number sothat you willnot have exponential.

4 Right click on Phone Number Column and then click on Format Cells.

Select Format Cells Option
fig. Select Format Cells Option

5 Select Number under Category, then Decimal Places to 0 and Negative numbers to 1234 before clicking the Ok button.

Remove Decimal Part in Excel Number
fig. Remove Decimal Part if any 

6 Open Visual Basic for Applications (VBA) from the developer tab.

Check FAQ for more on VBA.

Select Visual Basic
fig. Select Visual Basic 

7 Use the following code

  • First, open the code as follows 
View Code Visual basic
fig. View Code Visual basic 
  • Use the following code to program your code

(copy and paste the following)

Sub WhatsAppMsg()
Dim LastRow As Long
Dim i As Integer
Dim strip As String
Dim strPhoneNumber As String
Dim strmessage As String
Dim strPostData As String
Dim IE As Object

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
Application.Wait Now() + TimeValue("00:00:01")
strPhoneNumber = Sheets("Sheet1").Cells(i, 1).Value
Application.Wait Now() + TimeValue("00:00:01")
strmessage = Sheets("Sheet1").Cells(i, 2).Value
Application.Wait Now() + TimeValue("00:00:01")
ActiveSheet.Shapes(1).Copy
Application.Wait Now() + TimeValue("00:00:01")
strPostData = "whatsapp://send?phone=" & strPhoneNumber
Application.Wait Now() + TimeValue("00:00:01")
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate strPostData
Application.Wait Now() + TimeValue("00:00:05")
SendKeys strmessage
Application.Wait Now() + TimeValue("00:00:10")
SendKeys “~"
Set IE = Nothing
Next i
End Sub

Note: To execute the code correctly.Open whatsapp and be ready to send the message to first contact. 

What this script is doing ?

This script takes data from the first sheets of Excel, where the second row, first column contains phoneNumber and the second row, second column contains messages, then uses a foreach loop to load those information into strPostData one by one and then message is send.
Here you are sending messages without adding to your whatsapp contact list.

  • Click on save
Paste above script and click on Save Icon at top
fig. Paste above script and click on Save Icon at top
  • If any dialog box opens up click Yes or Ok.

8 Minimize the VBA window and open the Whatsapp application

9 Insert a button from the developer tab. Rename the button whatever you like Send .

10 Click on send or the button you just made.

11 Click on Allow button if any dialog box opens up.

You may need to click allow as many as the number of contacts you have.

Congrats you have completed the process of sending bulk messages.

Messages Send Successfully
fig. Whatsapp messages

 

Conclusion

This method may seem out of the hand at first. When this system is managed properly and understood well. This method proves to be a good and easy but not efficient way to send bulk messages. However, as the saying goes something is better than nothing, if you have no other resources give it a try.  

FAQ 

What is VBA?

Visual Basic for Applications (VBA) enables users to build user-defined functions (UDFs). Functions are the small part of the codes that performs a specific task.

Article by: Prashant Raj Bista