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)
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