ExcelブックからPythonでメールを送る

当然といえば当然なのですが、Pythonでメールの送受信をすることができます。それもBattery Includedの理念があらわすように、標準モジュールのみの構成で実現できます。

ここに神ツールOpenPyXLを組み合わせると、Excelシートにあるメールアドレス一覧へ片っ端からメールを送信するスクリプトができあがります。

Pythonでメール送信

Pythonでのメール作成にはemailモジュールを、送信にはsmtplibモジュールを使います。使い方はPythonの公式リファレンスを読むべきでしょうが、というより読んだんですが、ハッキリ言って「わかりずらい」のでググりました。

どうでもいい情報かと思われますが、プログラミング言語公式リファレンスのわかりやすさ俺脳内ランキング2019を発表しておきますと

1位:Java
2位:VBA
超えられない壁
3位以下 その他諸々

となっております。

Javaは本当に洗練されていて見事です。ISOあたりで全部Javaの方式に標準化して欲しいところです。


さて、ググっていくつかのサイトで得た情報を総合すると次のようなコードでメール送信ができました。

sender = smtplib.SMTP(mail_server, port)
sender.login(mail_id, mail_pass)
message = MIMEText(mail_body)
message['Subject'] = mail_subject
message['From'] = mail_from
message['To'] = mail_to
sender.sendmail(mail_from, mail_to, message.as_string())
sender.quit()

相変わらず、Pythonのソースコードのシンプルっぷりは驚異的です。

もちろん関連モジュールのimportや変数への代入等の処理が外野にあるわけですが、実際のメール作成送信に関わるのはたったこれだけです。

以前にExcel-VBAでOutlookを操作してメール送信するプログラムを作成しましたが(別記事参照)、あちらは送信処理をOutlookに丸投げしていますが、それと大差ないコード量で、しかもOutlookというシバリ要素もなくなります。あえて言うなら今度はPythonがシバリと言えなくもないですが…。

MacにはPythonが標準で入っているようですので、Windowsもさっさとバンドルにすべきですね。あっ、頼むから余計なカスタムをしないでね。真っさらのPythonでいいんですよ。

Excelシートからメール作成

では、ここから外堀を埋めていきましょう。

大枠はOpenPyXLでExcelからデータを読み取り、それを使ってメールを作成、送信するという処理になります。メールの元データを提供するExcelブックは上でリンクしている記事で作成したファイルをそのまま流用します。

まずは各種メール設定値を記録しておくために次のような[setting]シートを新たに作成します。

項目名そのままなので、設定値を入力します。この値をPythonで取得してメール送信に使います。

メールデータは[main]シートで次のような構成になっています。

この値をPythonで取得してメール作成に使います。行を上から見ていって空になるまでメール作成、送信を繰り返すという処理にします。

次にPythonスクリプトを書いていきます。importは次のようになります。

from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from os.path import basename
import smtplib
import openpyxl as excel
import sys

続いて定数を定義します。が、Pythonには定数という概念がないので、すべて大文字のスネークケース記法で定義した変数は定数として扱ってね、という慣例があります。

言語の仕様ではないので、このお約束は破れますが何のメリットもないのでやめておきましょう。

ROW_PAYLOAD_START = 2
COL_MAIL_TO = 1
COL_MAIL_SUBJECT = 2
COL_MAIL_BODY = 3
COL_MAIL_ATTACHMENT = 4

何の定数を定義したかというと、Excelシートのどこにどのデータがあるかという座標です。行方向は可変になるので、実データの開始行数と列のインデックスを定義しています。

実データは2行目からだよ、mail_to(宛先アドレス)は1列目だよ、という具合です。この方法、私はVBAでも多用します。

OpenPyXLでExcelブックを読み込んで、[setting]シートからメール送信に必要な設定値を取得します。

file = sys.argv[1]
wb = excel.load_workbook(file, data_only=True)
ws_setting = wb['setting']
ws_main = wb['main']
mail_from = ws_setting['b1'].value
mail_server = ws_setting['b2'].value
port = ws_setting['b3'].value
mail_id = ws_setting['b4'].value
mail_pass = ws_setting['b5'].value

ブック、シートを変数へ格納して、セル値を変数に代入していきます。

マジックナンバー全開ですが、やむを得ません。プログラムからのセルアクセスはどこかでアドレスを決め打ちせざるを得ません。

メールサーバーへ接続します。

sender = smtplib.SMTP(mail_server, port)
sender.login(mail_id, mail_pass)

メインのロジックを一気にいきます。

for row in range(ROW_PAYLOAD_START, ws_data.max_row + 1):
    mail_to = ws_main.cell(row=row, column=COL_MAIL_TO).value
    mail_body = ws_main.cell(row=row, column=COL_MAIL_BODY).value
    mail_attachment = ws_main.cell(row=row, column=COL_MAIL_ATTACHMENT).value

    if mail_to is not None:
        if mail_attachment is not None:
            message = MIMEMultipart()
            message.attach(MIMEText(mail_body))
            path = mail_attachment

            with open(path, 'rb') as f:
                part = MIMEApplication(f.read(), Name=basename(path))
            
            part['Content-Disposition'] = 'attachment; filename="%s"' % basename(path)
            message.attach(part)
        else:
            message = MIMEText(mail_body)

        message['Subject'] = ws_main.cell(row=row, column=COL_MAIL_SUBJECT).value
        message['From'] = mail_from
        message['To'] = mail_to
        sender.sendmail(mail_from, mail_to, message.as_string())
        print(mail_to + 'へ送信しました。')

sender.quit()

max_rowはExcelシートの使用されている最大行数を保持しているOpenPyXLのWorksheetオブジェクトのプロパティです。

forでrangeオブジェクト(OpenPyXLではなくPythonの)を実データ開始行(=2)からデータ最終行(max_row)までループさせます。

Pythonの仕様でrangeの最大値は引数の値を【含まない】ので、例えばmax_rowが4だとすると、3までカウントアップしたらループを抜けてしまいます。よって、引数の値までやりたいなら+1します。唐突に現れる+1が美しくないので嫌なのですがどうしようもないです。

このmax_rowですが、どうみても空の行をカウントした値を返すことがあります。5行目までしかデータがないのに8を返したりします。

なんでだろう?とググってみると、どうやらセル削除の方法に起因するOpenPyXLのバグのようなので、mail_to is not Noneでアドレスがある場合、つまりセルに値が入っている場合だけ処理するようにしてあります。


ところで、Worksheetオブジェクトにはiter_rowsという、シートを行単位で切り分けて返してくれるメソッドがあります。 これを使うと

for row in ws_main.iter_rows(min_row=ROW_PAYLOAD_START, values_only=True):
    mail_to = row[COL_MAIL_TO]
    mail_subject = row[COL_MAIL_SUBJECT]
    mail_body = row[COL_MAIL_BODY]
    mail_attachment = row[COL_MAIL_ATTACHMENT]

みたいにスッキリ書けます。おっ、これいいじゃんと思っていたら、落とし穴がありました。

このメソッドのリターンはタプル型にセル値が格納されたもので、インデックスが0始まりです。一方のExcelシートのセルインデックスは1始まりなので、何にも考えずにやると1列ずれることになります。上のコードだとCOL_MAIL_TO(=1)で、宛先アドレスを取るつもりなのですが、実際は件名を取ってきてしまいます。

定数定義など、どこかの段階でこのタプルの0始まりのインデックスと、Excelシートの1始まりのインデックスの差を吸収してやらねばなりません。これが何だか気持ち悪いのでコードは長くなりますが、セルインデックスでそのまま扱えるws_main.cell().valueで値を拾っていくことにしました。


mail_attachment is not Noneで添付ファイルが指定されているかを見ています。

添付ファイルはファイルパスの形でセルに記録されるようになっているので(別記事参照)、添付ファイルがあるならば、メールの形式をMIMEMultipart()にして添付ファイルを追加する処理を走らせます。ファイル添付のコードはネットからコピペです。

添付ファイルがない場合は、MIMEText(mail_body)でテキストプレーンのメールにします。

必要なデータを揃えたら、メールの実体であるmessageへそれぞれを格納していきます。

最後にSMTP.sendmailメソッドで送信します。


完成したソースコードは次の通りです。

from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from os.path import basename
import smtplib
import openpyxl as excel
import sys

ROW_PAYLOAD_START = 2
COL_MAIL_TO = 1
COL_MAIL_SUBJECT = 2
COL_MAIL_BODY = 3
COL_MAIL_ATTACHMENT = 4

try:
    file = sys.argv[1]
    wb = excel.load_workbook(file, data_only=True)
    ws_setting = wb['setting']
    ws_main = wb['main']
    mail_from = ws_setting['b1'].value
    mail_server = ws_setting['b2'].value
    port = ws_setting['b3'].value
    mail_id = ws_setting['b4'].value
    mail_pass = ws_setting['b5'].value

    sender = smtplib.SMTP(mail_server, port)
    sender.login(mail_id, mail_pass)

    for row in range(ROW_PAYLOAD_START, ws_main.max_row + 1):
        mail_to = ws_main.cell(row=row, column=COL_MAIL_TO).value
        mail_body = ws_main.cell(row=row, column=COL_MAIL_BODY).value
        mail_attachment = ws_main.cell(row=row, column=COL_MAIL_ATTACHMENT).value

        if mail_to is not None:
            if mail_attachment is not None:
                message = MIMEMultipart()
                message.attach(MIMEText(mail_body))
                path = mail_attachment

                with open(path, 'rb') as f:
                    part = MIMEApplication(f.read(), Name=basename(path))

                part['Content-Disposition'] = 'attachment; filename="%s"' % basename(path)
                message.attach(part)
            else:
                message = MIMEText(mail_body)

            message['Subject'] = ws_main.cell(row=row, column=COL_MAIL_SUBJECT).value
            message['From'] = mail_from
            message['To'] = mail_to
            sender.sendmail(mail_from, mail_to, message.as_string())
            print(mail_to + 'へ送信しました。')

    sender.quit()

except:
    print('エラーが発生しました。')

finally:
    input('Enterキーで終了します。')

使用方法

1.ページ下部のダウンロードボタンからアプリとExcelブックを入手してください。得体の知れないサイトの実行ファイルなんて使えねーというかたはソースコードをコピペしてご利用ください。

2.Excelブックにメール認証情報、メールメッセージの必要事項を入力して保存終了します。

このブックの操作について詳しくは下部リンクから別記事を参照してください。

3.アプリにExcelブックをドラッグ&ドロップしてください。

送信ログが表示されます。

宛先の受信トレイへメールが届きました。

Enterキーで終了します。

※エラーが発生した場合はログでお知らせしたあと握り潰します。

※Excelブックの構成を少しでも変更するとコケます。

※メールサーバーによっては設定が正しくてもエラーします。ソースコードの変更が必要ですので、あきらめるかご自身で改造してください。

応用例

Excelブックからデータを取得しているので、ワークシート関数などのExcelの機能を駆使して動的にメール本文を作成できます。詳しくは別記事で解説していますので、ぜひご覧ください。

アプリを入手する

このアプリに含まれるOpenPyXLモジュールはデフォルトではXMLの脆弱性を利用した攻撃を防ぐことはできません。信頼できないExcelファイルを処理しないでください。

利用上のご注意

  • ダウンロードしたファイルを利用したことにより生じた結果については、利用者ご自身に責任を負っていただきます。
  • ご利用前に使用方法をご確認ください。
  • 当方は成果物の正確性について最善を尽くしますが保証はいたしません。
  • Windows11 Microsoft365 環境でのみ動作確認済み。

Downloadボタンを押下した時点で注意事項に同意したものとみなします。

excel_mailer.zip

メール周りのコードは主に次のサイトをまるパクり参考にさせていただきました。

メール全般について

Pythonでメールを送信する:email, smtplib
Pythonでemailパッケージとsmtplibモジュールを使い、メールを送信する方法について説明します。 email を使ってメッセージを作成する emai…

添付ファイルの扱い

Pythonでメール送信 | Python学習講座

おわり。