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


Commentaires

Posts les plus consultés de ce blog

Installation de glpi 0.9 sur un serveur Windows 2012 R2

Accéder à PhpMyAdmin sans mot de passe

Impossible de joindre un fichier dans OWA