如何使用Python实现每月通过Outlook向不同客户自动发送对应绩效报告邮件并校验附件匹配？

Absolutely! This is totally doable with Python, and it’ll cut down that 1-2 day manual grind to just a few minutes (once you set it up). Let’s walk through exactly how to make this work while sticking with Outlook and adding that crucial report-client matching check.

Absolutely yes! Python’s win32com.client library lets you directly control Outlook (so you’ll keep using your familiar client), and we can easily build in validation to make sure the right reports go to the right clients. This will eliminate all that manual copy-pasting and attachment work.

Let’s break this into actionable steps with code examples you can adapt to your setup.

1. Prep Your Client-Report Mapping Data #

First, create a structured data file (CSV or Excel) that links each client to their details and reports. Here’s what columns you’ll want:

• Email: Client’s email address
• Name: Client’s name (for personalizing the email)
• Performance_Content: The custom performance text for the email body
• Report_Paths: File paths to the client’s reports (separate multiple paths with a semicolon ;, e.g., C:/Reports/ClientA_Report1.pdf;C:/Reports/ClientA_Report2.pdf)
• Month: The month/year for the report (to use in the email subject)

This file will be the single source of truth for matching clients to their content and reports.

2. Install Required Libraries #

You’ll need two packages:

• pywin32: To interact with Outlook
• pandas: To easily read and process your client data file

Install them via pip:

pip install pywin32 pandas

3. Write the Automation Script #

Here’s a complete, commented script that handles everything—data loading, validation, email creation, and sending:

import win32com.client as win32
import pandas as pd
import os
import time

# Load your client data (swap CSV for Excel with pd.read_excel() if needed)
client_data = pd.read_csv("client_monthly_data.csv")

# Connect to Outlook application
outlook = win32.Dispatch('outlook.application')

# Iterate through each client to send emails
for index, row in client_data.iterrows():
    # Extract client details
    client_email = row['Email']
    client_name = row['Name']
    performance_text = row['Performance_Content']
    report_paths = [path.strip() for path in row['Report_Paths'].split(';')]
    report_month = row['Month']
    
    # --------------------------
    # 🧑🔍 Matching Validation Check
    # --------------------------
    valid_attachments = []
    missing_reports = []
    for path in report_paths:
        if os.path.isfile(path):
            valid_attachments.append(path)
        else:
            missing_reports.append(path)
    
    # Handle validation failures: log and skip this client (or alert yourself)
    if missing_reports:
        error_msg = f"⚠️ Skipping {client_name} ({client_email}) - Missing reports: {', '.join(missing_reports)}"
        print(error_msg)
        # Optional: Write errors to a log file
        with open("email_errors.log", "a") as log_file:
            log_file.write(f"{time.ctime()}: {error_msg}\n")
        continue
    
    # --------------------------
    # 📧 Create and Send Email
    # --------------------------
    mail_item = outlook.CreateItem(0)  # 0 = Email item
    
    # Set email basics
    mail_item.To = client_email
    mail_item.Subject = f"[{report_month}] Your Monthly Performance Report"
    
    # Personalize the email body (use mail_item.HTMLBody if you need formatted text)
    mail_item.Body = f"""
    Hi {client_name},
    
    Here’s your performance summary for {report_month}:
    {performance_text}
    
    Your dedicated reports are attached below. Let us know if you have any questions!
    
    Best regards,
    Your Team
    """
    
    # Attach all valid reports
    for attachment_path in valid_attachments:
        mail_item.Attachments.Add(attachment_path)
    
    # Send the email (use mail_item.Save() to save as draft for testing first!)
    mail_item.Send()
    print(f"✅ Successfully sent to {client_name} ({client_email})")
    
    # Add a small delay to avoid overwhelming Outlook/server (optional but safe)
    time.sleep(1)

4. Test First! #

Before sending to all 100 clients:

• Replace mail_item.Send() with mail_item.Save() to save emails as drafts instead. This lets you review them in Outlook to make sure everything looks right.
• Test with a small subset of clients (add a filter to your client_data like client_data = client_data.head(2)) to iron out any kinks.

Pro Tips #

• Use Absolute File Paths: Always use full paths for reports (e.g., C:/Monthly_Reports/2024_05/ClientX.pdf) instead of relative paths to avoid "file not found" errors.
• Log Everything: The script includes basic logging, but you can expand it to track successful sends too—great for auditing later.
• Handle Duplicates/Errors: If a client has multiple entries in your data file, add a check to avoid sending duplicate emails.
• HTML Emails: If you need formatted text (bold, bullet points), switch to mail_item.HTMLBody and write your body as HTML.

内容的提问来源于stack exchange，提问作者Natacha VM