【業務自動化】Chapter3-5：メール送信を自動化しよう

import openpyxl
import pathlib

input_dir = pathlib.Path(__file__).parent / "input"
input_dir.mkdir(exist_ok=True)

# 保存ファイル名
file_name = input_dir / "send_mail_list.xlsx"

# 新規ブック作成
book = openpyxl.Workbook()
sheet = book.active
sheet.title = "mail_list"

# ヘッダー行
sheet.append(["To", "Subject", "Body"])

# サンプルデータ
sample_data = [
    ["user1@example.com", "テスト件名1", "これは本文のテスト1です。"],
    ["user2@example.com", "テスト件名2", "これは本文のテスト2です。"],
    ["user3@example.com", "テスト件名3", "これは本文のテスト3です。"]
]

for row in sample_data:
    sheet.append(row)

# 保存
book.save(file_name)
print(f"{file_name} を作成しました。")

pip install aiosmtpd

python -m aiosmtpd -n -l localhost:1025

import openpyxl, pathlib, sys
import smtplib
from email.mime.text import MIMEText

# === 実行環境に応じた基準ディレクトリ ===
if getattr(sys, 'frozen', False):
    BASE_DIR = pathlib.Path(sys.executable).parent
else:
    BASE_DIR = pathlib.Path(__file__).parent

input_dir = BASE_DIR / "input"

# Excelファイルの読み込み
file_name = input_dir / "send_mail_list.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book.active

to_list = []
subject_list = []
body_list = []

# 2行目以降を読み込む（1行目はヘッダー）
for row in sheet.iter_rows(min_row=2, values_only=True):
    to_address, subject, body = row
    if not any(row):          # 完全な空行はスキップ
        continue
    if not to_address:        # 宛先がない行はスキップ
        continue
    to_list.append(to_address)
    subject_list.append(subject or "(件名なし)")
    body_list.append(body or "")

# === ダミーSMTPサーバーに送信 ===
# aiosmtpd を別ターミナルで起動しておく: python -m aiosmtpd -n -l localhost:1025
with smtplib.SMTP("localhost", 1025) as server:
    for i in range(len(to_list)):
        msg = MIMEText(body_list[i], "plain", "utf-8")
        msg["Subject"] = subject_list[i]
        msg["From"] = "noreply@example.com"  # ダミー送信元
        msg["To"] = to_list[i]

        # ★ 学習用: コンソールに内容を表示 ★
        print("=== 送信メール ===")
        print("To:", to_list[i])
        print("Subject:", subject_list[i])
        print("Body:", body_list[i])
        print("------------------")

        server.send_message(msg)

print("送信テスト完了！ターミナルにメール内容が出力されています。")

---------- MESSAGE FOLLOWS ----------
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Subject: =?utf-8?b?44OG44K544OI5Lu25ZCNMQ==?=
From: noreply@example.com
To: user1@example.com
X-Peer: ('::1', 62175, 0, 0)

44GT44KM44Gv5pys5paH44Gu44OG44K544OIMeOBp+OBmeOAgg==
------------ END MESSAGE ------------
---------- MESSAGE FOLLOWS ----------
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Subject: =?utf-8?b?44OG44K544OI5Lu25ZCNMg==?=
From: noreply@example.com
To: user2@example.com
X-Peer: ('::1', 62175, 0, 0)

44GT44KM44Gv5pys5paH44Gu44OG44K544OIMuOBp+OBmeOAgg==
------------ END MESSAGE ------------
---------- MESSAGE FOLLOWS ----------
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: base64
Subject: =?utf-8?b?44OG44K544OI5Lu25ZCNMw==?=
From: noreply@example.com
To: user3@example.com
X-Peer: ('::1', 62175, 0, 0)

44GT44KM44Gv5pys5paH44Gu44OG44K544OIM+OBp+OBmeOAgg==
------------ END MESSAGE ------------

import pathlib
import sys
import smtplib
import openpyxl
from email.mime.text import MIMEText

# PyInstallerでexe化した場合はsys.executableのパスを基準に、
# 通常の.py実行時は__file__のあるフォルダを基準にする
if getattr(sys, "frozen", False):
    BASE_DIR = pathlib.Path(sys.executable).parent
else:
    BASE_DIR = pathlib.Path(__file__).parent

input_dir = BASE_DIR / "input"

# Excelファイルの読み込み
file_name = input_dir / "send_mail_list.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book.active

to_list = []
subject_list = []
body_list = []

# 2行目以降を読み込む（1行目はヘッダー）
for row in sheet.iter_rows(min_row=2, values_only=True):
    to_address, subject, body = row

    if to_address is None:
        break

    to_list.append(to_address)
    subject_list.append(subject)
    body_list.append(body)

# OutlookのSMTPサーバー情報
smtp_server = "smtp-mail.outlook.com"
smtp_port = 587

# 送信元アドレスとパスワード
from_address = "あなたのアドレス@outlook.com"
password = "あなたのパスワード"

# Outlook SMTPサーバーに接続して送信
with smtplib.SMTP(smtp_server, smtp_port) as server:
    server.starttls()
    server.login(from_address, password)

    for i in range(len(to_list)):
        msg = MIMEText(body_list[i], "plain", "utf-8")
        msg["Subject"] = subject_list[i]
        msg["From"] = from_address
        msg["To"] = to_list[i]

        server.send_message(msg)

print("メール送信完了！")

from_address = "あなたのメールアドレス@gmail.com"
app_password = "ここに発行した16桁のアプリパスワード"