Code to send an email based on the Excel email list

Why do you like this?

There are quite a few so-called chores, such as creating a star chart for work (such as attendance at a drinking party outside of work) and urging people who have not yet submitted it based on it. So I decided to process it in Python. If you are a company that uses Microsoft Office 365, it seems that you can do it with such no code if you use Automate Flow, but I prepared it because there is no environment that can be used around me.

Referenced

• Let Python do the boring things-Automated processing programming that even non-programmers can do (2017/6/3) Al Sweigart (Author), Aizo Aikawa (Translation)

What to prepare

Prepare a file called "maillist.xlsx" as shown below.

mail_send.py

import openpyxl, smtplib
from email import message

wb = openpyxl.load_workbook('maillist.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
lastCol = sheet.max_column
latest_month = sheet.cell(row=1, column=lastCol).value

#Check Excel file
unpaid_Members = {}
for r in range(2,sheet.max_row + 1):
    payment = sheet.cell(row=r, column=lastCol).value
    if payment != 'paid':
        name = sheet.cell(row=r, column=1).value
        email = sheet.cell(row=r, column=2).value
        unpaid_Members[name] = email

#Log in to your email account

smtp_host = 'smtp-mail.outlook.com'
smtp_port = 587
send_name = 'Master'
from_email ='[email protected]'
username = '[email protected]'
password = 'password'

smtp_obj = smtplib.SMTP(smtp_host, smtp_port)
smtp_obj.ehlo()
smtp_obj.starttls()
smtp_obj.login(username,password)

#Prompt the user for a password on the command line screen
#When you want to enter it, import the sys package and execute the code below.
# smtp_obj.login(username,sys.argv[1])

#send an email
for name, email in unpaid_Members.items():
    msg = message.EmailMessage()
    msg.set_content("""
{}Mr
Good work.
It seems that the participation fee for the welcome party next week has not been paid yet.
Please confirm.
    """.format(name))
    msg['From'] = '{}<{}>'.format(send_name, from_email)
    msg['Subject'] = '{}Regarding the payment amount of'.format(latest_month)    
    print('Sending an email{}...' .format(email))
    sendmail_Status = smtp_obj.sendmail(from_email, email, msg.as_string())
if sendmail_Status != {}:
    print('{}I had a problem sending an email to: {}'.format(email,sendmail_Status))
smtp_obj.quit()

How to use

All you have to do now is run this .py file. By setting and executing a time schedule, it is possible to automatically urge unpaid people to collect the fee for the drinking party every day. Have a good email life.