Work Automation – Bulk email send out via Python

As part of this project we will be creating a Python script that will make use of an Excel spreadsheet containing a list of recipients i.e. their names and emails. The script will fetch this data and use it to carry out a bulk email send out.

Before we start ensure that you have a list of recipients saved to an xlsx file containing the names and email addresses of our recipients.

we begin by opening the Excel spreadsheet via Openpyxl and define the sheet name. With the sheet now open we will iterate through all entries and define the name and email data. With each set of data we make a call to the sendEmail function which sends the email.

def Main():
    wb = openpyxl.load_workbook('C:\Desktop\Spreadsheet.xlsx')
    sheet = wb["Sheet1"]
    for i in range(2,sheet.max_row+1):
        Name = str(sheet.cell(row = i, column = 1).value)
        Email = str(sheet.cell(row = i, column = 2).value)
        sendEmail(Name,Email)
       

The emails are sent via a mail server, if you do not have one of these you could always resort to using a third party mailing server i.e. Gmail. I am hoping to cover this in the next post. We would also define the contents of the email in this function which is a plain text mail.

def sendEmail(Name,Email):
    msg = MIMEMultipart() 
    msg['Subject'] = " Account" 
    msg["From"] = "Scriptopia <Info@Scriptopia.co.uk>"
    msg["To"] = Email
    body = "Dear " + Name + ", \n\n"
    body += "This is a test email\n\n"
    body += "Thank you!"
    msg.attach(MIMEText(body,'plain'))
    s = smtplib.SMTP('YouMailServer.co.uk')
    save_msg = str(msg)
    s.send_message(msg)
    s.quit()

The source code in full can be found below:

from openpyxl import Workbook
import openpyxl
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

def sendEmail(Name,Email):
    msg = MIMEMultipart() 
    msg['Subject'] = " Account" 
    msg["From"] = "Scriptopia <Info@Scriptopia.co.uk>"
    msg["To"] = Email
    body = "Dear " + Name + ", \n\n"
    body += "This is a test email\n\n"
    body += "Thank you!"
    msg.attach(MIMEText(body,'plain'))
    s = smtplib.SMTP('YouMailServer.co.uk')
    save_msg = str(msg)
    s.send_message(msg)
    s.quit()

def Main():
    wb = openpyxl.load_workbook('C:\Desktop\Spreadsheet.xlsx')
    sheet = wb["Sheet1"]
    for i in range(2,sheet.max_row+1):
        Name = str(sheet.cell(row = i, column = 1).value)
        Email = str(sheet.cell(row = i, column = 2).value)
        sendEmail(Name,Email)

def Main()

Leave a Reply