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

What to prepare

Prepare a file called "maillist.xlsx" as shown below. コメント 2020-09-11 144102.png

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.

Recommended Posts

Code to send an email based on the Excel email list
Send an email to Spushi's address with python
Send an email with Excel attached in Python
To automatically send an email with an attachment using the Gmail API in Python
I tried to make it possible to automatically send an email just by double-clicking the [Python] icon
How to easily create an environment where python code runs on Jupyter without polluting the local environment
Disguise the grass on GitHub and try to become an engineer.
The road to installing Python and Flask on an offline PC
[boto3] Send an email using SES
How to create an email user
Paste the matplotlib figure on excel
Note: Send an email with Django
[Python] Send an email with outlook
Output the report as PDF from DB with Python and automatically attach it to an email and send it
I tried to output the rpm list of SSH login destination to an Excel sheet with Python + openpyxl.
I tried to make it possible to automatically send an email just by double-clicking the [GAS / Python] icon
What to do if you couldn't send an email to Yahoo with Python.
Send an email to a specific email address with python without SMTP settings
HTML email with image to send with python
Is the number equivalent to an integer?
Python amateurs try to summarize the list ①
Send an email with Amazon SES + Python
Get the EDINET code list in Python
I stumbled on the character code when converting CSV to JSON in Python
Send a direct love email to a new user when a new user joins the Slack team