After my previous article, I got requested to do a follow-up tutorial using an Excel spreadsheet to send bulk messages.

For this one the background story is a simple one. Assuming we are in university/college, and we needed to send our class members, a message to inform/remind them about class. Naturally there are multiple options including Whatsapp/Telegram, calls, email etc. However, we can't assume they all have smartphones, they will be online in time to
receive the message. Furthermore,calling is not only tedious but highly inefficient and not scalable.

Another option is to text the class manually or create a group chat. Depending on the number of people, the frequency of texts and whether you need to customize the message. It may prove more work that it should be.

In this article we'll take an spreadsheet of names and numbers, craft a custom message for each and send a custom message to each one. we'll add checks in case the message fails to send.
This method is not only highly scalable but efficient and flexible as you could potentially have multiple scripts for different classes or customize one script for the specific need.

Preparation

To effectively follow along with this post and subsequent code, you will need the following prerequisites.

• Python and pip (I am currently using 3.9.2 ) Any version above 3.5 should work.

• An Africas Talking account.

  • Api Key and username from your account. Create an app and take note of the api key.
    As per the previous article :

    • Create a new directory and change into it.

      mkdir at-project 
      cd at-project
    

    • Create a new virtual environment for the project or activate the previous one.
    • Using python package manager(pip) install africastalking python sdk, python-dotenv library and openpyxl library.
    • Save the installed libraries in a requirements.txt file

       python -m venv .
       source bin/activate
       pip install africastalking python-dotenv openpyxl 
       pip freeze > requirements.txt
    

There are few alternative libraries for reading and writing excel files: Pandas,
xlrd , openpyxl among others. In the end I settled for openpyxl as I had the most experience
using it and it had support for .xlsx files.

Got all that? Let's send some texts.
Alternatively jump to the completed code on

  `source ./scripts/activate`

The first alert

It all starts with a single message to your first classmate, this is acts an alpha test for your notification service.
I am using the python-dotenv library to retrieve the required credentials for environment variables.
This is adhering to best practice in regard to sensitive information.

• Create a .env file
• Enter the following replacing the placeholders with the proper credentials.

  # Both can be obtained from your account console on Africas Talking
    username=Username-here
    api_key=apikey-here 

Create our main file multiple-sms-excel.py.
Import the required libraries.

# multiple-sms-excel.py
import os
import africastalking as at
from dotenv import load_dotenv

Now lets get our credentials, initialize the africastalking client and send our first test sms.

Make sure your number here, and throughout this post, is in E.164 format

# multiple-sms-excel.py

load_dotenv()

username = os.getenv("username")
api_key = os.getenv("api_key")
at.initialize(username, api_key)
sms = at.SMS
message = f"hey {name} from python using africas talking API"
# add your number below
number = "+2547XXXXXX"
sms.send(response = sms.send(message, [number]))
print(response)

This creates a message and sends it . Test the code by running python multiple-sms-excel.py

You should see the atxid and receive your first message!

Multi Notifications

Now that your test went well, let's update the existing file to send messages to all of them.
Add the following code

# multiple-sms-excel.py
import os
import africastalking as at
from openpyxl import load_workbook
from dotenv import load_dotenv

load_dotenv()

username = os.getenv("username")
api_key = os.getenv("api_key")
at.initialize(username, api_key)
sms = at.SMS

wb = load_workbook('sample.xlsx')
print(wb.sheetnames)
sheet1 = wb['Sheet1']

names_cell_range = sheet1['B2:B4']
number_cell_range = sheet1['C2:C4']

First we import the load_workbook function from the openpyxl module. This allows us
to read an existing workbook. We proceed to assign the variable wb to active workbook.
My spreadsheet is named sample.xlsx pictured below:

However, edit according to your specific use case. We print all the worksheets names in our workbook. After we get the appropriate worksheet, we assign a variable for future reference.
Based on the layout of the worksheet we identify the range of cells which we want.
We assign variables names_cell_range and number_cell_range to the specific range.

99 problems but class ain't one

You've finally got the required details now we send the custom notification/reminder.
Let's create a function send_messages() to hold all of our message logic. This step is optional, but it makes easier in case we want to import the logic to another file or reference it in the code again.

# multiple-sms-excel.py 
def send_messages():
    for row in sheet1.iter_rows(values_only=True):
        name = row[1]
        number = f"+254{row[2]}"
        lesson = row[3]
        lesson_date = "Friday 12 March at 8.00 am "
        print(name,number)
        message = f"hey {name}  Kindly note {lesson} lecture is scheduled on {lesson_date}"
        try:
            response = sms.send(message, [number])
            print(response)
        except Exception as e:
            print(f"Uh oh we have a problem: {e}")

send_messages()

I'll explain what the above block of code does.
The openpyxl library has an iter_rows() method that we use to iterate over each row in the spreadsheet. We further pass on the values_only argument to ensure we only get the value of each row.
The for loop returns 3 values from out Excel file, we only need name and number. We proceed to assign the values to their aptly named variables. I hard-coded the value of the date for the lecture. However, its just easy enough to calculate using the inbuilt datetime module. We construct a custom message using f strings to interpolate the values we need. We go ahead and add a try-catch block which will come in handy to notify us of in case we run into problem when sending the messages. Lastly we call our function send_messages().

Now we can finally run python multiple-sms-excel.py and watch the terminal output. You should receive a custom message if everything went well. This was just a sample use case for bulk sms. You could just easily adapt it to fit a variety of situations.

If you have any question or comments. Let me know in the comments, or on Twitter

Buy me a coffee