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キーで終了します。')