Auto create certificate and email
Fill name to certificate template, and auto email them
- Import python code
- Read participant list
- Generate e-certificate for each participant
- Sending automatic email
In my organisation, we host webinar from time to time. Participants are getting e-certificate for attending. Our admin usually created custom certificate for each person manually, then emailing them one by one. Well, this is inefficiency, especially considering we are going to host 6 events in November.
I believe various Customer Relationship Manager (CRM) software can do this easily, but I thought, why not help out the admins while sharpening my programming skill.
Original code was found in stack overflow:
# import python library
import smtplib, ssl
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
from string import Template
import pandas as pd
import os
First, we need to read an excel file containing a list of all participants' name to be written for each certificate certificate, as well as their email address for the certificates to be emailed to.
# read the file containing the database with the mail and the corresponding file
path = 'C:/Users/Riyan Aditya/Desktop/ML_learning/Project5_ISED_email'
os.chdir(path)
filename = "lists_trial_for_blog.xlsx"
e = pd.read_excel(filename)
e.head()
Then I will read the certificate template using Python's Pillow library
Note: I removed the bottom half to hide the signature in the certificate. This is the certificate that we gave in one of our previous webinar.
Links:
- ISED: https://ised-id.org/
- Akutuku: https://akutuku.id/c/
# read certificate template and generate certificate for each participant
from PIL import Image, ImageDraw, ImageFont
# load and create image object
path = 'C:/Users/Riyan Aditya/Desktop/ML_learning/Project5_ISED_email/sertifikat'
os.chdir(path)
image_rgba = Image.open('E-CERTIFICATE template nosignature.png')
# convert rgba image to rgb (PDF needs RGB)
image_rgb = Image.new('RGB', image_rgba.size, (255, 255, 255)) # white background
image_rgb.paste(image_rgba, mask=image_rgba.split()[3]) # paste using alpha channel as mask
image_rgb
# get dimension of certificate
width, height = image_rgb.size
width, height
this code is to fill the certificate with each participants' name
# this code is to fill the certificate with each participants' name
# Initialise drawing content with the image object as background
# Go through xlsx file of participants and write the participants name in each certificate
# save certificate on pdf
# load font
font = ImageFont.truetype("times-ro.ttf", 100)
# change path to a new folder
path = 'C:/Users/Riyan Aditya/Desktop/ML_learning/Project5_ISED_email/sertifikat/sertifikat_tiap_peserta'
os.chdir(path)
# create empty column in DF to refer to the filename of each certificate
#e['sertifikal_filename'] = ""
for index, row in e.iterrows():
# copy image_rgb as certificate template
cert_template = image_rgb.copy()
# define image, font, message
draw = ImageDraw.Draw(cert_template)
# use certificate width, but custom height
W, H = width, 1150
msg = e.loc[index]['Nama participan']
# find out the width and height of the text based on the defined font
w, h = draw.textsize(msg, font = font)
# draw text
draw.text(((W-w)/2,(H-h)/2), msg, fill="black", font = font)
# image
# save image
cert_template.save(msg+".pdf", "PDF")
# update dataframe for filename of each participants
e.at[index,'sertifikat filename'] = msg+".pdf"
Print one certificate to check output
# print one certificate to check
cert_template
In this example, we will use gmail. In gmail, you need to enable permission to send email from outside sources.
See here: https://support.google.com/accounts/answer/6010255
First, login to your email. Input email and password
# In this example we will use gmail
context = ssl.create_default_context()
server = smtplib.SMTP_SSL('smtp.gmail.com', 465,context=context)
server.login('xxxxxxxx@gmail.com','YourPassword')
e.head()
Create function to automatically attach certificate and send email
# Create function to auto send email
# change path
import os
path = 'C:/Users/Riyan Aditya/Desktop/ML_learning/Project5_ISED_email/sertifikat/sertifikat_tiap_peserta'
os.chdir(path)
# Email parameter
subject = "E-certificate webinar ISED"
fromaddr='xxxxxxxx@gmail.com'
def auto_email(df):
# iterate over each row of participants data
for index, row in df.iterrows():
# Email body ------------------------------------------------------------------------
body = ("""
Selamat pagi Bapak/Ibu """+str(row["Nama participan"])+""",
Berikut terlampir e-sertifikat atas kehadiran Bapak/Ibu dalam acara webinar kita.
Terima kasih.
Salam,
Riyan (on behalf of team ISED)
""")
# Email body ------------------------------------------------------------------------
print (row["Email participan"]+" "+row["sertifikat filename"])
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['Subject'] = subject
msg.attach(MIMEText(body, 'plain'))
filename = row["sertifikat filename"]
toaddr = row["Email participan"]
attachment = open(row["sertifikat filename"], "rb")
part = MIMEBase('application', 'octet-stream')
part.set_payload((attachment).read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', "attachment; filename= %s" % filename)
msg.attach(part)
text = msg.as_string()
server.sendmail(fromaddr, toaddr, text)
print('')
print("Emails sent successfully")
Then, send the emails
auto_email(e)
# dont forget to quit the server
server.quit()