Python自动化办公之Excel拆分并自动发邮件

import datetime
import os
import shutil
from pathlib import Path
import pandas as pd
src_file = Path.cwd() / 'data' / 'Example4.xlsx'
df = pd.read_excel(src_file)
df.head()

customer_group = df.groupby('CUSTOMER_ID')
for ID, group_df in customer_group:
print(ID)
>>>Output>>>
A1000
A1001
A1002
A1005
...

attachment_path = Path.cwd() / 'data' / 'attachments'
today_string = datetime.datetime.today().strftime('%m%d%Y_%I%p')
attachments = []
for ID, group_df in customer_group:
attachment = attachment_path / f'{ID}_{today_string}.xlsx'
group_df.to_excel(attachment, index=False)
attachments.append((ID, str(attachment)))

[('A1000',
'c:\Users\luobo\notebooks\2020-10\data\attachments\A1000_01162021_12PM.xlsx'),
('A1001',
'c:\Users\luobo\notebooks\2020-10\data\attachments\A1001_01162021_12PM.xlsx'),
('A1002',
'c:\Users\luobo\notebooks\2020-10\data\attachments\A1002_01162021_12PM.xlsx'),
('A1005',
'c:\Users\luobo\notebooks\2020-10\data\attachments\A1005_01162021_12PM.xlsx')]

email_merge = pd.merge(df, df2, how='left')
combined = email_merge[['CUSTOMER_ID', 'EMAIL', 'FILE']].drop_duplicates()

import win32com.client as win32
today_string2 = datetime.datetime.today().strftime('%b %d, %Y')
class EmailsSender:
def __init__(self):
self.outlook = win32.Dispatch('outlook.application')
def send_email(self, to_email_address, attachment_path):
mail = self.outlook.CreateItem(0)
mail.To = to_email_address
mail.Subject = today_string2 + ' Report'
mail.Body = """Please find today's report attached."""
mail.Attachments.Add(Source=attachment_path)
# Use this to show the email
#mail.Display(True)
# Uncomment to send
#mail.Send()

email_sender = EmailsSender()
for index, row in combined.iterrows():
email_sender.send_email(row['EMAIL'], row['FILE'])

arcHive_dir = Path.cwd() / 'archive'
for f in attachments:
shutil.move(f[1], archive_dir)