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

PythonでExcelブックのリストからメール送信。

当然といえば当然なのですが、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]シートを新たに作成します。

expy-mailer1.png

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

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

mail1.png

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

使用方法

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

1.ページ下部のダウンロードボタンからアプリとExcelブックを入手してください。

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

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

expy_mailer4.png

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

expy_mailer5.png

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

expy_mailer2.png

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

expy_mailer3.png

5.Enterキーで終了します。

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

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

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

応用例

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

アプリを入手する

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

excel_mailer.zip

DOWN LOADボタンが押下された時点で注意事項に同意したものとみなします。

ソースコードから実行する

得体の知れないサイトで拾った実行ファイルなんて使えねーよというかたは、ソースコードから実行できます。ダウンロードボタンからソースコード(.pyファイル)を入手してください。

実行には次の外部モジュールが必要です。pipでインストールしてください。

  • openpyxl

対象のExcelブックをコマンドラインから引数でわたすか.pyファイルにドラッグ&ドロップすると実行されます。詳細は次の記事を参考にしてください。

ソースコードをダウンロード

excel_mailer_source.zip

DOWN LOADボタンが押下された時点で注意事項に同意したものとみなします。

参考サイト

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

メール全般について

添付ファイルの扱い

おわり。

related pages
PCのカメラでISBNコードを読み取りExcelに書籍リストを作る
ISBNでAPIを使って書籍情報取得。

国立国会図書館が運用する国立国会図書館サーチAPIは一般的なREST APIでエンドポイントにパラメーター付きのHTTPリクエストを送信するとパラメーターに応じた処理をした結果が返ってきます。今回はバーコードリーダーで読み取ったISBNでこれを使って書籍情報を取得します。

Read More ...
Excelファイルから文字列を取り出す
神エクセルから文字列だけを頂戴します。

Excelから文字列だけを取り出したい!という需要はあまりないと思いますが、以前、画像を取り出すツールを作りましたので、どうせならと作ってみました。原理は画像取り出しツールと基本同じですが、文字列はXMLファイルに埋め込まれており単純にファイルをごそっと抜き取るだけでは使い物になりません。

Read More ...
コーディングを加速するキーボード
PCに付属してきたキーボードは窓から投げ捨てろ!

昨今はスマホのフリック入力の方が速いなんて人も多いと思いますが、オフィスワーカーであればキーボードを全くさわらないというわけにはいかないと思います。事務所のキーボードはPC本体にくっついてきたものをそのまま使っているケースが大多数でしょう。ノートPCであれば、もうキーボードを外付けしてまで使うなんてことは皆無だと思います。

Read More ...
この記事の
作成日

2019-12-31

更新日

2020-07-18

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園