The goal of the project is to automate sending the same e-mail to a list of people with the same text (except the salutation "Dear Name of Person") with an HTML signature and a PDF attachment.

For this purpose, I have:

  1. An Excel list with Name, E-Mail, Subject and a "sent?" field (that has to be marked once the e-mail is sent) as seen on this picture: https://imgur.com/pxYiRvx

  2. an HTML file with the e-mail template. To avoid having too much code in the python file, I saved the HTML e-mail separately. The text was written very simple between p tags and the signature was created with the help of the following tool: https://www.hubspot.com/email-signature-generator?utm_source=create-signature

  3. The send_email.py file, which code is shown here:


import smtplib
import pandas as pd
from email.message import EmailMessage
import openpyxl

#E-Mail Credentials
EMAIL_ADDRESS = 'example@gmail.com'
EMAIL_PASSWORD = 'Password1!'
#/// ----- ///

#Read HTML E-Mail Template
with open('template.html', 'r') as f:
        html_string = f.read()
#/// ----- ///

#Load XLS Workbook for writing
file = "EmailList.xlsx"
wb = openpyxl.load_workbook(file)
ws = wb.active
#/// ----- ///

#1.1 Loading E-Mail Recipient List with Pandas for reading
email_list = pd.read_excel("EmailList.xlsx")

#1.2 Get all the Names, Email Addreses, Subjects and Messages
xls_names = email_list['Name']
xls_emails = email_list['Email']
xls_subjects = email_list['Subject']

#1.3 Loop through the emails
for index in range(len(xls_emails)):
    name = xls_names[index]
    email = xls_emails[index]
    subject = xls_subjects[index]

#1.4 Create E-Mail (loop)
    msg = EmailMessage()
    msg['Subject'] = subject
    msg['From'] = EMAIL_ADDRESS
    msg['To'] = email
    msg.set_content(html_string.format(name=name), subtype='html')

    #Load Attachments
    files = ['halle.pdf']

    for file in files:
        with open(file, 'rb') as f:
            file_data = f.read()
            file_name = f.name

        msg.add_attachment(file_data, maintype='application', subtype='octet-stream', filename=file_name)

    #try to send mail
    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as smtp:

            print('\n Email to {} successfully sent!\n'.format(email))

            #loop for check feedback in XLS
            for column in ws.iter_cols(1):
                for cell in column:
                    if cell.value == email:
                        ws.cell(row=cell.row, column=4).value = 'check!'

        except Exception as e:
            print('\n\n Email to {} could not be sent :( because {}'.format(email, str(e)))

The questions I have are:

  1. Is the code ok?
  2. Do I need to have a plain text alternative for the HTML e-mail? Or is this overrated?
  3. I saw many many posts importing and using MIMEMultipart, MIMETextimport, MIMEBase****t and what not. I did not use it, but of course I am wondering if I have something wrong?
  • \$\begingroup\$ Is this code OK? Sure, however there are lots of things you could improve upon. First comes to mind is using argparse so that your variables are not hardcoded. Do you NEED to have a plain text alt message? Best practice says so: litmus.com/blog/…. Finally, you may as well use the built in MIME functions. Perhaps take a look at my SMTP program, which has functionality for what you are trying to do in addition to many other things (like email spoofing). Hope this helps... github.com/darkerego/SmtpRelaySpoof \$\endgroup\$
    – Chev_603
    Nov 12 '19 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.