时间戳

首页 > 解决方案 > 自动从 excel 文件发送批量电子邮件

python - 自动从 excel 文件发送批量电子邮件

问题描述

电子邮件通知列表

我想自动向我列表中的学校家长发送自定义/个性化电子邮件，如上图所示，提供学生课程的详细信息和其他相关信息。为此，我正在使用openpyxl和smtplib模块。

但是，我在包含学生信息和家长信息的字典中的行和项目的循环运行不佳。每次我运行脚本时，我都会收到这种类型的错误

Traceback (most recent call last):
  File "class_email_notification.py", line 42, in <module>
    for course, account, link, clasS_day, session in class_info.items():
ValueError: not enough values to unpack (expected 5, got 2)

我的 xlsx 和 .py 文件都位于同一目录中。.py 文件的片段是这样的：

import openpyxl, smtplib, sys, datetime
from time import sleep

wb = openpyxl.load_workbook('course_notification.xlsx')
sheet = wb['student info']

parents = {}
class_info = {}
students = {}
day = datetime.datetime(2020, 6, 1, 8, 0 ,0).strftime('%A, %B, %d, %Y %I:%M:%S %p')

for r in range(2, sheet.max_row + 1):
    parent_name = sheet.cell(row = r, column = 8).value
    parent_email = sheet.cell(row = r, column = 9).value
    parents[parent_name] = parent_email

    course = sheet.cell(row = r, column = 3).value
    account = sheet.cell(row = r, column = 4).value
    link = sheet.cell(row = r, column = 5).value
    clasS_day = sheet.cell(row = r, column = 6).value
    session = sheet.cell(row = r, column = 7).value
    class_info[course] = account, link, clasS_day, session

    gender = sheet.cell(row = r, column = 2).value
    student_name = sheet.cell(row = r, column = 1).value

smtpObj = smtplib.SMTP('smtp.gmail.com')
smtpObj.ehlo()
smtpObj.starttls()

for parent_name, parent_email in parents.items():
    for course, account, link, clasS_day, session in class_info.items():
        smtpObj.login('example@gmail.com', sys.argv[1])
        if gender.lower() == 'male':
            gender = 'his'
            body = 'Subject: Tinker Education Online Classes\n\nDear %s,\n\nHope you and your family are doing well and keeping safe while at home. Tinker Education would like to bring to your attention that our online classes will commence  on ', day,' .\n\nOur teachers would like to have an insanely great start of the term with our students. Hence, you are requested to notify your child of ',gender, 'class time. Class details are as follows:\n\n%s\n\nDay: %s\nSession: %s\nCLP:\n%s\nVideo link: %s\n\n%s is requested to keep time of ', gender, ' %s %s\n\nKind regards,\nTinker Desk'%(parent_name, course, clasS_day, session, account, link, student_name, course, session)
        else:
            gender = 'her'
            body = 'Subject: Tinker Education Online Classes\n\nDear %s,\n\nHope you and your family are doing well and keeping safe while at home. Tinker Education would like to bring to your attention that our online classes will commence  on ', day,' .\n\nOur teachers would like to have an insanely great start of the term with our students. Hence, you are requested to notify your child of ',gender, 'class time. Class details are as follows:\n\n%s\n\nDay: %s\nSession: %s\nCLP:\n%s\nVideo link: %s\n\n%s is requested to keep time of ', gender, ' %s %s\n\nKind regards,\nTinker Desk'%(parent_name, course, clasS_day, account, session, link, student_name, course, session)
        print('Sending emails to %s through %s'%(parent_name, parent_email))
        send_email_status = smtpObj.sendmail('my_email@gmail.com', parent_email, body)

if send_email_status != {}:
    print('There was an error sending an email to %s through %s'(parent_name, parent_email))
smtpObj.quit()

我for在遍历字典中的键和值时的循环没有提供足够的值来解包。我错过了什么？

我试图理解为什么无法获取字典中的所有项目。我的假设是我的逻辑不是那么正确。

我已经参考了自动化无聊的东西：发送电子邮件，我可以理解如何去做。但真正了解为什么我的脚本上的 dicts 没有完全解包的确切逻辑是我的问题。

标签： pythonexcelopenpyxlsmtplib

解决方案

迭代返回的结构class_info.items()在这里是一个嵌套的元组，其中第一个元素是键，第二个元素是关联的值。在这种情况下，它是(account, link, clasS_day, session).

您可以使用以下方法使其以当前形式工作： for course, (account, link, clasS_day, session) in class_info.items():

即复制返回的元组结构class_info.items()

推荐阅读