Sending Bulk SMS using Africas Talking, Python and Excel
Tags: python, sms, tutorial, africastalking
Published: 2021-03-10 16:49:55

After my [previous article](https://dev.to/ken_mwaura1/sending-bulk-sms-using-africas-talking-python-and-csv-5bf5), 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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zv3pm6geyu4ushhpdr6a.gif)

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](https://account.africastalking.com/auth/register/).
    - 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.
          ```shell
          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 
           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](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html),
[xlrd](https://github.com/python-excel/xlrd) , [openpyxl](https://openpyxl.readthedocs.io/en/stable/) 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 {% github KenMwaura1/Africas-Talking-Multiple-SMS %}

## The first alert
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vmq4z8wxuar7td7wehfe.jpg)
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.
  ```environment
  # 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.
```python
# 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](https://www.twilio.com/docs/glossary/what-e164)
```python
# 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!

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qykre43f74u2ahu37zb7.gif)

## 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
```python
# 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:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/np87a1sid5ufo5uzofgu.png)

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
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/38jm6fbzpwed7mryzd9j.gif)
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.
``` python
# 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](https://twitter.com/Ken_Mwaura1)


Buy me a [coffee](https://ko-fi.com/kenmwaura1)