DEV Community

loading...
Cover image for Automate sending emails from Excel with Python

Automate sending emails from Excel with Python

stokry profile image Stokry ・3 min read

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
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

server.quit()
print('Process is finished!')
time.sleep(10)
Enter fullscreen mode Exit fullscreen mode

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) 
Enter fullscreen mode Exit fullscreen mode

Thank you all.

Discussion (6)

pic
Editor guide
Collapse
mahmoudibnsamy profile image
Mahmoud Ibn Samy

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?

Collapse
andrewbaisden profile image
Andrew Baisden

Good article I just hope that nobody is using Excel as a database.

Collapse
manishfoodtechs profile image
manish srivastava

nice article :)

Collapse
stokry profile image
Stokry Author

Thank you! :-)

Collapse
abdurrahmaanj profile image
Abdur-Rahmaan Janhangeer

Why not pandas.read_excel? Btw nice read!

Collapse
jlrpuma profile image
Jose Luis Rodriguez

A great way to help your wife .... coding 🚀, great post btw.