Send Multiple Email in Excel using Python

Some time ago, Google suddenly change its policy for academic account limit on Google Drive where it used to be unlimited. At that time, I was working at some private university in Indonesia and we need to inform about 18K non-active users that their entire accounts were about to be deleted because some of the accounts has like 1TB of data in their drive. Apparently, some of them use their student google account for storing their full-time work. So we cannot just delete their entire hard work in a snap.

So, my boss hands me an excel file consisting of email, academic state (active or not), and another private field that is irrelevant to mention in this article. What I needed to do was actually simple. I just need to read the excel using pandas, then send a pre-determine text about the situation.

Read and filter Email

Let's pretend we have an excel/CSV file like in the picture named "student_email.xlsx"

I use pandas to read the file

import pandas as pd

df = pd.read_excel("student_email.xlsx")

After reading the file, we only need to get a non-active account. We can do this with pandas!

is_non_active = df['acdm_state'] == "not_active"
non_active_df = df[is_non_active ]

Bear in mind, Google limit email 3000 recipient per day. So I did simple grouping for 20 email.

email = []
t_email = []
count = 0
for i, row in df[is_non_aktif].iterrows():
    t_email.append(row['email'])
    if count % 20 == 0 and count > 1:
        email.append(t_email)
        t_email = []
    count += 1
email.append(t_email)

Send the mail

The good news is python already had a smtplib as their standard library so I don't have to install it.

In my case, every 20 mail I set a sleep timer for 60 seconds (my server is just old).

import smtplib, ssl
from email.mime.text import MIMEText
import time

for i in range(0, len(email)):
    sender = '[e-mail]'
    receiver = email[i]

    user = '[e-mail]'
    password = 'secretpassword'
    msg = MIMEText(
        """
        <h1>Your Email About to DIE!!!</h1>
        """, "html")

    msg['Subject'] = 'Bye Bye'
    msg['From'] = '[e-mail]'
    msg['To'] = ", ".join(email[i])
    context=ssl.create_default_context()

    with smtplib.SMTP('smtp.gmail.com', port=587) as server:
        server.starttls(context=context)
        server.login(user, password)
        server.sendmail(sender, receiver, msg.as_string())
        print("%s mail successfully sent" % i)
    print("waiting")
    # break
    time.sleep(60)

You can see the flaw in my code that after 3000 emails were sent my code would break for the next 24 hours. I'm just lazy so I run the code every day and edited the range with the last index that it's been printed before. You can also set error handling at every time when limit is reached, the code would pause for 24 hours (the smart way).

Conclusion

Python saved my a$$