Claude Codeに頼んだら顧客500人への個別メール送信が5分で自動化された話

# Claude Codeが生成したコード: Excelリストからメール一括送信
import os
import smtplib
import pandas as pd
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime
import time

# ── 設定（環境変数から安全に読み込む）────────────────
GMAIL_USER = os.environ["GMAIL_USER"]       # 送信元アドレス
GMAIL_PASS = os.environ["GMAIL_APP_PASS"]   # Gmailアプリパスワード
EXCEL_PATH = "customers.xlsx"

# メール本文テンプレート
SUBJECT_TMPL = "【重要】{customer_name}様への月次ご報告"
BODY_TMPL    = """
{customer_name} 様

いつも大変お世話になっております。
担当の{staff_name}でございます。

今月の月次レポートをお送りいたします。
ご不明点がございましたら、お気軽にご連絡ください。

何卒よろしくお願い申し上げます。
"""

def send_email(to_addr: str, subject: str, body: str) -> bool:
    """1通のメールを送信して成否を返す"""
    msg = MIMEMultipart()
    msg["From"]    = GMAIL_USER
    msg["To"]      = to_addr
    msg["Subject"] = subject
    msg.attach(MIMEText(body, "plain", "utf-8"))

    try:
        with smtplib.SMTP_SSL("smtp.gmail.com", 465) as server:
            server.login(GMAIL_USER, GMAIL_PASS)
            server.send_message(msg)
        return True
    except Exception as e:
        print(f"  [ERROR] {to_addr}: {e}")
        return False

def main():
    # Excelを読み込む
    df = pd.read_excel(EXCEL_PATH)
    print(f"顧客リスト読み込み完了: {len(df)}件")

    sent_count = 0
    fail_count = 0

    for idx, row in df.iterrows():
        # 送信フラグが「済」の行はスキップ
        if row["送信フラグ"] == "済":
            continue

        customer_name = row["顧客名"]
        to_addr       = row["メールアドレス"]
        staff_name    = row["担当者名"]

        subject = SUBJECT_TMPL.format(customer_name=customer_name)
        body    = BODY_TMPL.format(customer_name=customer_name, staff_name=staff_name)

        print(f"送信中: {customer_name} ({to_addr})")
        success = send_email(to_addr, subject, body)

        # 送信結果をExcelに記録
        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        df.at[idx, "送信フラグ"]   = "済" if success else "失敗"
        df.at[idx, "送信日時"]   = now

        if success:
            sent_count += 1
        else:
            fail_count += 1

        time.sleep(0.5)  # レート制限対策

    # 結果をExcelに保存
    df.to_excel(EXCEL_PATH, index=False)
    print(f"\n完了: 送信成功 {sent_count}件 / 失敗 {fail_count}件")

if __name__ == "__main__":
    main()

import os
import smtplib
import pandas as pd
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
from datetime import datetime
import time

GMAIL_USER = os.environ["GMAIL_USER"]
GMAIL_PASS = os.environ["GMAIL_APP_PASS"]
EXCEL_PATH = "customers.xlsx"
PDF_DIR    = "./invoices/"      # PDFが置かれているフォルダ
YEAR_MONTH = "202604"           # 請求月

def send_email_with_attachment(to_addr: str, subject: str, body: str, pdf_path: str) -> tuple:
    """メールを送信（PDFがあれば添付する）"""
    msg = MIMEMultipart()
    msg["From"]    = GMAIL_USER
    msg["To"]      = to_addr
    msg["Subject"] = subject
    msg.attach(MIMEText(body, "plain", "utf-8"))

    pdf_attached = False
    if pdf_path and os.path.exists(pdf_path):
        with open(pdf_path, "rb") as f:
            part = MIMEBase("application", "octet-stream")
            part.set_payload(f.read())
        encoders.encode_base64(part)
        part.add_header("Content-Disposition", f"attachment; filename={os.path.basename(pdf_path)}")
        msg.attach(part)
        pdf_attached = True

    try:
        with smtplib.SMTP_SSL("smtp.gmail.com", 465) as server:
            server.login(GMAIL_USER, GMAIL_PASS)
            server.send_message(msg)
        return True, pdf_attached
    except Exception as e:
        print(f"  [ERROR] {to_addr}: {e}")
        return False, False

def main():
    df = pd.read_excel(EXCEL_PATH)

    for idx, row in df.iterrows():
        if row["送信フラグ"] == "済":
            continue

        customer_name = row["顧客名"]
        to_addr       = row["メールアドレス"]
        staff_name    = row["担当者名"]

        # PDFファイルのパスを組み立てる
        pdf_name = f"請求書_{customer_name}_{YEAR_MONTH}.pdf"
        pdf_path = os.path.join(PDF_DIR, pdf_name)

        subject = f"【重要】{customer_name}様への月次ご報告"
        body    = f"{customer_name} 様\n\nお世話になっております。担当の{staff_name}です。\n月次レポートを添付いたします。"

        success, pdf_ok = send_email_with_attachment(to_addr, subject, body, pdf_path)

        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        df.at[idx, "送信フラグ"] = "済" if success else "失敗"
        df.at[idx, "送信日時"]   = now
        df.at[idx, "PDF添付"]    = "済" if pdf_ok else "PDF未作成"

        time.sleep(0.5)

    df.to_excel(EXCEL_PATH, index=False)
    print("送信完了。ログをExcelに保存しました。")

if __name__ == "__main__":
    main()

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

def build_html_body(customer_name: str, staff_name: str) -> str:
    """HTMLメール本文を生成する"""
    return f"""
<!DOCTYPE html>
<html lang="ja">
<head><meta charset="UTF-8"></head>
<body style="font-family: sans-serif; background: #f9f9f9; margin:0; padding:20px;">
  <div style="max-width:600px; margin:0 auto; background:white; border-radius:12px; overflow:hidden; box-shadow:0 2px 12px rgba(0,0,0,0.08);">
    <div style="background:#107C41; padding:24px 32px;">
      <h1 style="color:white; font-size:20px; margin:0;">Aurant Technologies</h1>
    </div>
    <div style="padding:32px;">
      <p style="font-size:16px; color:#333;"><strong>{customer_name} 様</strong></p>
      <p style="color:#555; line-height:1.8;">
        いつも大変お世話になっております。<br>
        担当の<strong>{staff_name}</strong>でございます。<br><br>
        今月の月次レポートをお送りいたします。<br>
        ご不明点はお気軽にご連絡ください。
      </p>
    </div>
    <div style="background:#f0faf4; padding:16px 32px; font-size:12px; color:#888;">
      © 2026 Aurant Technologies
    </div>
  </div>
</body>
</html>"""

def send_html_email(to_addr: str, subject: str, customer_name: str, staff_name: str) -> bool:
    msg = MIMEMultipart("alternative")
    msg["From"]    = GMAIL_USER
    msg["To"]      = to_addr
    msg["Subject"] = subject

    # テキスト版（受信環境がHTML非対応の場合のフォールバック）
    text_body = f"{customer_name} 様\n\nお世話になっております。{staff_name}です。\n月次レポートをお送りします。"
    html_body = build_html_body(customer_name, staff_name)

    msg.attach(MIMEText(text_body, "plain", "utf-8"))
    msg.attach(MIMEText(html_body, "html", "utf-8"))

    with smtplib.SMTP_SSL("smtp.gmail.com", 465) as server:
        server.login(GMAIL_USER, GMAIL_PASS)
        server.send_message(msg)
    return True

import os
from sendgrid import SendGridAPIClient
from sendgrid.helpers.mail import Mail, Attachment, FileContent, FileName, FileType

SENDGRID_KEY = os.environ["SENDGRID_API_KEY"]
FROM_EMAIL   = os.environ["FROM_EMAIL"]

def send_via_sendgrid(to_addr: str, subject: str, html_body: str, pdf_path: str = None) -> bool:
    """SendGrid経由でHTMLメール（添付ファイルオプション）を送信"""
    message = Mail(
        from_email=FROM_EMAIL,
        to_emails=to_addr,
        subject=subject,
        html_content=html_body
    )

    # PDF添付（存在する場合のみ）
    if pdf_path and os.path.exists(pdf_path):
        with open(pdf_path, "rb") as f:
            import base64
            encoded = base64.b64encode(f.read()).decode()
        attachment = Attachment(
            FileContent(encoded),
            FileName(os.path.basename(pdf_path)),
            FileType("application/pdf")
        )
        message.attachment = attachment

    try:
        sg = SendGridAPIClient(SENDGRID_KEY)
        response = sg.send(message)
        return response.status_code == 202
    except Exception as e:
        print(f"SendGrid エラー: {e}")
        return False

import time
from datetime import datetime
import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill, Font

ADMIN_EMAIL     = os.environ["ADMIN_EMAIL"]
FAIL_THRESHOLD  = 5  # この件数以上失敗でアラート送信

def main():
    df = pd.read_excel(EXCEL_PATH)
    start_time = time.time()

    sent_count = fail_count = 0
    alert_sent = False

    for idx, row in df.iterrows():
        if row["送信フラグ"] == "済":
            continue

        success = send_email_with_attachment(
            row["メールアドレス"], row["顧客名"], row["担当者名"]
        )

        if success:
            sent_count += 1
            df.at[idx, "送信フラグ"] = "済"
        else:
            fail_count += 1
            df.at[idx, "送信フラグ"] = "失敗"

        # 失敗が閾値を超えたら管理者に通知
        if fail_count >= FAIL_THRESHOLD and not alert_sent:
            send_admin_alert(fail_count)
            alert_sent = True

    elapsed = round(time.time() - start_time, 1)

    # ログシートにサマリーを追記
    write_summary_log(sent_count, fail_count, elapsed)

    # 管理者にサマリーメール送信
    summary_body = f"""送信完了サマリー
実行日時: {datetime.now().strftime('%Y-%m-%d %H:%M')}
送信成功: {sent_count}件
送信失敗: {fail_count}件
所要時間: {elapsed}秒
"""
    send_email(ADMIN_EMAIL, "メール一括送信完了レポート", summary_body)
    df.to_excel(EXCEL_PATH, index=False)
    print(summary_body)