Automate sending emails from Excel with Python

#python #tutorial #showdev #productivity

The other day, my wife asked me if I could help her with a way to send a notification to clients who haven't paid a certain amount. Anyway, my wife runs a small business and she does a lot of things through Excel. She asked me if there was any possibility that she could automatically send e-mails to clients who didn't pay, so that she wouldn't have to send an individual e-mail to everyone.

So I wrote a script for her that automated the process. 😃

The Excel file contains a lot of information but we will focus on the most important ones -- name, e-mail. town, paid (yes/no), and the amount. The script works quite simply, we have the cell “paid” and if the client hasn't paid we'll send him a personalized email to warn him that he owes a certain amount. The script sends e-mails to all clients who haven't paid.

Let's go to code.

We'll install 'xlrd' first. 'Xlrd' is a library to extract data from Microsoft Excel (tm) spreadsheet files. After that we will import 'smtplib'. The smtplib module defines an SMTP client session object that can be used to send mail to any Internet machine with an SMTP or ESMTP listener daemon.

Let's import all stuff that we need.

import xlrd
import time
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

After that, we need a path of our Excel file as well as all information that we need from that file (name, email, city, paid, amount).

path =  "clients.xlsx"
openFile = xlrd.open_workbook(path)
sheet = openFile.sheet_by_name('clients')

I put the email, amount, and the name of clients that owe money in three different lists. And from that I check if cllient is paid or not.

mail_list = []
amount = []
name = []
for k in  range(sheet.nrows-1):
    client = sheet.cell_value(k+1,0)
    email = sheet.cell_value(k+1,1)
    paid = sheet.cell_value(k+1,3)
    count_amount = sheet.cell_value(k+1,4)
    if paid ==  'No':
         mail_list.append(email)
         amount.append(count_amount)
         name.append(client)

After that, we need to focus on sending emails.

email =  'some@gmail.com'
password =  'pass'
server = smtplib.SMTP('smtp.gmail.com',  587)
server.starttls()
server.login(email, password)

We need to get the index so then we can find the name of the person.

for mail_to in mail_list:
    send_to_email = mail_to
    find_des = mail_list.index(send_to_email)
    clientName = name[find_des]
    subject =  f'{clientName} you have a new email'
    message =  f'Dear {clientName}, \n'  \ 
               f'we inform you that you owe ${amount[find_des]}.  \n'\
               '\n'  \
               'Best Regards'
msg = MIMEMultipart()
msg['From ']  = send_to_email
msg['Subject']  = subject
msg.attach(MIMEText(message,  'plain'))
text = msg.as_string()
print(f'Sending email to {clientName}... ')
server.sendmail(email, send_to_email, text)

And last we need to be sure that be sure everything it's ok.

server.quit()
print('Process is finished!')
time.sleep(10)

And let's put all together.

import xlrd
import time
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

path = "clients.xlsx"
openFile = xlrd.open_workbook(path)
sheet = openFile.sheet_by_name('clients')


mail_list = []
amount = []
name = []
for k in range(sheet.nrows-1):
    client = sheet.cell_value(k+1,0)
    email = sheet.cell_value(k+1,1)
    paid = sheet.cell_value(k+1,3)
    count_amount = sheet.cell_value(k+1,4)
    if paid == 'No':
        mail_list.append(email) 
        amount.append(count_amount)
        name.append(client)


email = 'some@gmail.com' 
password = 'pass' 
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(email, password)

for mail_to in mail_list:
    send_to_email = mail_to
    find_des = mail_list.index(send_to_email) 
    clientName = name[find_des] 
    subject = f'{clientName} you have a new email'
    message = f'Dear {clientName}, \n' \
              f'we inform you that you owe ${amount[find_des]}. \n'\
              '\n' \
              'Best Regards' 

    msg = MIMEMultipart()
    msg['From '] = send_to_email
    msg['Subject'] = subject
    msg.attach(MIMEText(message, 'plain'))
    text = msg.as_string()
    print(f'Sending email to {clientName}... ') 
    server.sendmail(email, send_to_email, text)

server.quit()
print('Process is finished!')
time.sleep(10)

Thank you all.

Top comments (7)

Mahmoud Ibn Samy • Oct 6 '20

Perfect, I wrote a simple script to do the same task but used openpyxl library instead of xlrd as I wanted to write in the xlsx files not just read their data.
But why you used time.sleep() at the end?