Mailing list with Python

Aim :

Write a Python script to send an email to a list of customers for instance. The list is in a worksheet on Excel.

Procedure :

The recipient list is in an Excel file named Recipients.xlsx and has one sheet (Sheet1). The first column is LastName, The second is FirstName. The third column is the email address.

For this script, we need the openpyxl library.

If the library is not installed, we need to install it first. Open a DOS command and type :

python –m pip install openpyxl

We will also need smtplib to send mails using google servers. For this, manu solutions are possible (See https://support.google.com/a/answer/176600?hl=en)

If we use a Gmail address (or G Suite address), we can send up to 2000 mails per day. The server to use then is smtp.gmail.com.

Let us begin the script :

from openpyxl import load_workbook

import smtplib

We will start by creating some variables :

myMail="MailAddress" # Mail adress on gmail, used for the authentication

myPassword=" " # Password of myMail address

smtpserver='smtp.gmail.com' # Smtp server which will handle the emails sent by our script

port=587 # port used by the server

Then we create a connection to the smtp server Serveur

server=smtplib.SMTP(smtpserver,port) # server is an object

To start communicating with the smtp server, we use the method ehlo()

server.ehlo()

We switch to the TLS mode (Transport Layer Security). All subsequent commands will be encrypted.

server.starttls()

The google server requires user authentication. To do so, we use the login() method which needs two arguments: the Gmail or G Suite mail address and its password.

server.login(myMail,myPassword)

All what we need to do now is to browse the recipient list, copy the first name , the last name and the email address into variables then compose a message and use the sendmail() method to send the email.

We first open the Excel file

wb = load_workbook("Recipients.xlsx")

Then the sheet Sheet1

ws=wb["Sheet1"]

Create a loop

i=2

while ws['C'+str(i)].value !=None: # We break out of the loop when the is no more email address to process

    toaddress=ws['C'+str(i)].value

    LastName=ws['A'+str(i)].value

    FirstName=ws['B'+str(i)].value

    msg="Hi " + LastName + " " + FirstName

    server.sendmail(myMail,toaddress,"From: "+myMail + "\n" + msg)

    print(str(i)+" Mail sent to " + toaddress)

    i=i+1

The complete script is:

from openpyxl import load_workbook

import smtplib

myMail="MailAddress on gmail" # Mail adress on gmail, used for the authentication

myPassword=" " # Password of myMail address

smtpserver='smtp.gmail.com' # Smtp server which will handle the emails sent by our script

port=587 # port used by the server

server=smtplib.SMTP(smtpserver,port)

server.ehlo()

server.starttls()

server.login(myMail,myPassword)

wb = load_workbook("Recipients.xlsx")

ws=wb["Sheet1"]

i=2

while ws['C'+str(i)].value !=None:

    ToAddress=ws['C'+str(i)].value

    LastName=ws['A'+str(i)].value

    FirstName=ws['B'+str(i)].value

    msg="Hi " + LastName + " " + FirstName

    server.sendmail(myMail,ToAddress,"From: "+myMail + "\n" + msg)

    print(str(i-1)+" Mail sent to " + ToAddress)

    i=i+1

The sent email is received by all recipients

Unfortunately, Office 365 considers the received mail as junk mail