Excelシートのリストからメールを送る

Outlookを使ってExcelのリストからメール送信します。

VBAが使えるのはExcelだけではありません。MS-Officeアプリであればどれでも使えます。

VBAとExcelの相性が抜群なので、VBAすなわちExcelみたいになっていますが、Wordだって使えるんです。ただWordでの使い道は相当限られているのか、超マイナーな扱いで(私もまったくさわったことがありません)WEB上の情報量としてはExcel>>Access>>>>その他といった感じでしょうか。需要もこの通りになっていると思います。

VBAでメール作成

Excelシートにメールアドレス一覧ができていれば、それを使ってメールを送りたいと思いますよね。MS-OfficeにもれなくついてくるメールアプリOutlookをあなたがお使いならば、それはVBAで簡単に実現可能です。

VBAではOfficeアプリはそれぞれがオブジェクトとして存在します。Applicationオブジェクトがそれです。Applicationオブジェクトを使うことで、Excelから他のアプリをVBAで容易に制御することができます。

ApplicationオブジェクトによるExcel-Outlook連携の基本となるVBAコードを確認しておきましょう。

Sub mailer()
    Dim outlook As Object
    Set outlook = CreateObject("Outlook.Application")
    
    Dim mailItem As Object
    Set mailItem = outlook.CreateItem(0)
    
    With mailItem
        .To = "aitsu@expy-style.net"  '宛先
        .Subject = "例の件"           '件名
        .Body = "本日はお日柄も良くうんぬん"    '本文
        .Attachments.Add "c:\添付ファイル.xlsx" '添付ファイルパス
        .Display   '表示
        '.Send       '送信
    End With
End Sub

たったこれだけです。あなたがOutlookをお使いならば、このコードをコピペして実行するとメール編集画面が立ち上がるでしょう。宛先を自分のアドレスにしてからDisplayを削除してSendのコメントアウトを外して実行すればメールが送信されるでしょう。通信まわりはすべてOutlookがやってくれますので、驚くほど短いコードでメールが送信できます。

ただ、これができたところで普通にメールを作成、送信しているのと何ら変わらないので面白くもなんともないです。これをもとにExcel-VBAならではの機能を追加していきます。

Excelからメールを作成することで、関数を利用して動的にメール文を作成したり、宛先によって添付ファイルを変えたりといった柔軟な処理が可能になります。

Excelシートからメール作成・送信

基本コードの解説も兼ねてイチからいきます。

VBAでのOutlookアプリのオブジェクトは次のように作成します。

Dim outlook As Object
Set outlook = CreateObject("Outlook.Application")

実行時バインディングでOutlookのライブラリを参照しています。

これ以外に参照設定で「Microsoft Outlook **.* Object Library」を参照する方法もありますが、以前に「Officeのバージョンが違うPCでマクロが走らなくなり、原因が参照設定のライブラリのバージョンが変わっていて参照できなくなっていたことに気がつくのに小一時間かかった事件」を経験して以来、個人的に参照設定が嫌いなのでこうしています。

参照設定していないとインテリセンスが効かないので、コーディングのときは参照設定をつけておき、リリース時に実行時バインディングに書き換えています。

続けてMailItemオブジェクトを作成します。

MailItemはメールメッセージをあらわすオブジェクトです。MailItemのメソッド、プロパティを操作することで、メールを作成していきます。

Dim mailItem As Object
Set mailItem = outlook.CreateItem(0)

実行時バインドのため、Applicationオブジェクトが定義している列挙型を引けないので実数「0」になっていてややわかりにくいですが、Application.CreateItemメソッドに渡す値でリターンされるオブジェクトが決定される仕組みです。0でMailItemがリターンされます。ここらの解説は公式リファレンスに詳しいです。

作成したMailItemオブジェクトのプロパティにセルの値を格納していきます。シートの構成は次のようにします。一部セルが黄色い理由は後述。

mail1.png

さて、ここからセルへ入力された値を使ってメールを作成するわけですが、セルへアクセスしてVBAプログラムへ値を取り込む処理は極めて具象的なコードになってしまうので、シートの列を入れ替えただけで、コードを全般にわたって書き換える羽目になりがちです。

この手間を極力なくすために私がよく使う手が列挙型定数を使って具象的なセルのインデックス値(いわゆるマジックナンバー)を1ヶ所にまとめて定義してしまう方法です。モジュールの先頭で次を定義します。

Enum COL
    MAIL_TO = 1
    MAIL_SUBJECT
    MAIL_BODY
    MAIL_ATTACHMENT
    MAIL_MEMO
End Enum
Public Const PAYLOAD_START_ROW As Long = 2
Const MAIL_ITEM As Long = 0

読んで字のごとく(これが非常に重要)ですが、どの列(のインデックス)がどの項目に対応しているかを列挙型で定義します。また、リストのタイトルを除いたデータのスタート行を定数で定義します。MailItemオブジェクト作成のための定数もあとからわかるように定義しておきます。以降のコードではすべてこれらを使ってセルを指定します。あとから列を入れ替えたとしても、列挙型の順番をちょちょいと変えてあげればよく、他は一切さわる必要がなくなります。

画像のシートのリストから1行ずつメールを作成して送信するコードは次の通りです。

Dim ws As Worksheet
Set ws = wsmain
Dim r As Long
r = PAYLOAD_START_ROW
    
Do Until ws.Cells(r, COL.MAIL_TO).Value = ""
    Dim mailItem As Object
    Set mailItem = outlook.CreateItem(MAIL_ITEM)
    
    With mailItem
        .To = ws.Cells(r, COL.MAIL_TO).Value
        .Subject = ws.Cells(r, COL.MAIL_SUBJECT).Value
        .Body = ws.Cells(r, COL.MAIL_BODY).Value
        
        If Not ws.Cells(r, COL.MAIL_ATTACHMENT).Value = "" Then
            .Attachments.Add ws.Cells(r, COL.MAIL_ATTACHMENT).Value
        End If
        
        .Send
    End With
    r= r + 1
Loop

このようにメインのプロシージャには一切マジックナンバーが出現しません。

MailItemオブジェクトの主要なプロパティ、メソッドは次の通りです。

MailItem.Toプロパティは 宛先アドレス
MailItem.Subjectプロパティは 件名
MailItem.Bodyプロパティはメール 本文
MailItem.Attachmentsプロパティは 添付ファイル
をそれぞれあらわします。

MailItem.Sendメソッドはメール送信を実行します。

送信履歴はOutlookに送信済みメールとして残りますが、Excel側でもログくらいはとっておきたいところなので、別シートに記録するようにします。

送信に使用したデータをそのまま別シートに直接転記するコードをひたすら書いていく方法もありますが、今回はこれまた私が好きでよくやる方法で、1行のデータ(データベースでいうところのレコード)をクラスで表現して、クラスを介してデータを受け渡しするやり方を使います。

まずLogクラスを作成します。

Option Explicit

Private mailTo_ As String
Private mailSubject_ As String
Private mailBody_ As String
Private attachmentFilePath_ As String
Private memo_ As String
Private status_ As String

Property Get mailTo() As String
    mailTo = mailTo_
End Property

Property Get mailSubject() As String
    mailSubject = mailSubject_
End Property

Property Get mailBody() As String
    mailBody = mailBody_
End Property

Property Get attachmentFilePath() As String
    attachmentFilePath = attachmentFilePath_
End Property

Property Get memo() As String
    memo = memo_
End Property

Property Get status() As String
    status = status_
End Property

Property Let mailTo(mt As String)
    mailTo_ = mt
End Property

Property Let mailSubject(ms As String)
    mailSubject_ = ms
End Property

Property Let mailBody(mb As String)
    mailBody_ = mb
End Property

Property Let attachmentFilePath(af As String)
    attachmentFilePath_ = af
End Property

Property Let memo(mm As String)
    memo_ = mm
End Property

Property Let status(st As String)
    status_ = st
End Property

面倒くさかったらプロパティのprivateを外してgetter、setterは作らなくてもいいでしょう。

次にメインのプロシージャでLogをまとめておくためのloggerコレクションをNewします。

どうでもいい話ですが、この「Newする」という言い回しが私の趣味プログラマー人生のバイブル「スッキリわかるJava入門」にちょくちょく登場して好きなのでマネして使っています。要はクラスをインスタンス化するという意味です。VBAではあまり登場しないNewですが、JavaではNewばっかりです。

Dim logger As Collection
Set logger = New Collection

配列でもいいんですが、何にも考えずNewしたらAddでバカスカ要素を突っ込めるCollectionが私のお気に入りです。配列の方が処理は速いようですが、まぁ誤差の範囲でしょう。

メインのプロシージャでLogクラスをNewしてLogオブジェクトとしてプロパティに各データを格納します。ループでメールを作成していきますので、Logオブジェクトもメールと同じ数だけ作成されます。それをコレクションへ追加していきます。

Do Until (略)
    Dim Log As Log
    Set Log = New Log
    Log.mailTo = ws.Cells(i, COL.MAIL_TO).Value
    Log.mailSubject = ws.Cells(i, COL.MAIL_SUBJECT).Value
    Log.mailBody = ws.Cells(i, COL.MAIL_BODY).Value
    Log.attachmentFilePath = ws.Cells(i, COL.MAIL_ATTACHMENT).Value
    Log.memo = ws.Cells(i, COL.MAIL_MEMO).Value
    logger.Add Log
Loop

話は変わりますが、ループの中で変数宣言(Dim)するな派と、してもいいよ派がいて、実際に差異があるのか実験されていた方の記事をどこかで見たのですが、どっちも変わらないという結果だったはずです。

VBAが必要ならメモリ確保するし必要なければしないみたいなうごきをすると説明されていたと思います。ので、私は気にせずループに入れます。宣言と初期化と使用は場所が近ければ近いほどいいです。

ちなみにプロシージャの先頭ですべての変数をまとめて宣言するのは、レガシー言語の慣習の名残でVBAでは意味がなくナンセンスとおっしゃっている方がおられましたが、私も完全同意です。脱線おわり。

コレクションからFor EachでLogオブジェクトを取り出してシートへ書き出すサブプロシージャloggingを作成します。

Sub logging(logger As Collection)
    Dim r As Long
    r = wslog.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    Dim Log As Log
    For Each Log In logger
        With wslog
            .Cells(r, COL.MAIL_TO).Value = Log.mailTo
            .Cells(r, COL.MAIL_SUBJECT).Value = Log.mailSubject
            .Cells(r, COL.MAIL_BODY).Value = Log.mailBody
            .Cells(r, COL.MAIL_ATTACHMENT).Value = Log.attachmentFilePath
            .Cells(r, COL.MAIL_MEMO).Value = Log.memo
        End With
        r = r + 1
    Next
End Sub

セル指定にも、値の指定にもそれぞれに名前がついているので、対応を間違える可能性が低いというのがおわかりいただけると思います。これが列挙型とクラスを使う醍醐味です。

添付ファイルの指定は、ファイル選択ダイアログでしたいので、その処理を書きます。

まずダイアログの処理を作ります。

Sub filePicker(cellAddress As String)
    Dim fp As String
    fp = Application.GetOpenFilename
    
    If Not fp = "False" Then
        Range(cellAddress).Value = fp
    End If
End Sub

引数cellAddressはワークシートのダブルクリックイベントからもらいます。

シートのイベントに処理を書きます。

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = COL.MAIL_ATTACHMENT Then
        If Target.Row = PAYLOAD_START_ROW Then
            Call filePicker(Target.Address)
        End If
    End If
End Sub

これで、ダブルクリックされたセルが添付ファイルの列かつ実体データの開始行の場合(シート画像の黄色セル)に限りfilePickerプロシージャがコールされます。ファイル選択ダイアログが表示され、選択されたファイルのパスがセルに入力されます。パスをもとにメール作成時に添付ファイルとして指定されます。

以上で必要な部品はそろいましたので、リファクタリングを施して完成です。

完成版のExcelファイルをページ下部からダウンロードしてご利用いただけます。

使用方法

Outlookがインストールされ、アカウントが設定されている必要があります。

1.ページ下部のダウンロードボタンからExcelファイルを入手します。

2.mainシートに必要事項を入力後、mailerプロシージャをコールしてください。

3.送信結果はlogシートへ記録されます。

エラーハンドラはありませんので、ご注意ください。ご自身で追加してください。

応用例

Excel関数を利用すると用意したテンプレートへ動的に文字列をはめ込みメール本文などを作成することができます。

1.相手の名前を入力する列を追加します。

mail2.png

2.テンプレート文を作ります。プレースホルダーを適当に設定して埋め込みます。
画像の場合///name///です。

mail3.png

3.body列へテンプレートのプレースホルダーを名前列の値に置き換える関数を入力します。
画像ではSUBSTITUTEを使用しています。

mail4.png

4.オートフィルで全行へ適用します。

mail5.png

5.mailerプロシージャをコールします。画像ではわかりやすいように送信ではなく表示で止めています。このように相手の名前が入った本文でメールが送信できます。

mail6.png

私が実際に送信しているのは多くて10件程度なので、これが100件、1000件でも正常に送信できるかは不明です。大量の送信リクエストはプロバイダーに怒られるかもしれません。ご注意ください。

ファイルを入手する

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

excel_mailer.xlsm

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

おわり。

related pages
VBAでファイルを開くのが遅すぎるのでPythonで無理矢理に高速化する
VBAとPythonを連携させてファイル読み込み高速化。

我々VBAerにとっては周知の事実ですが、Excel-VBAで時間がかかる処理と言えば、「ワークシートへのアクセス」と「ファイルを開く」です。Pythonでデータ取得してVBAでExcelへ取り込むことでファイルを開く処理を高速化します。

Read More ...
セル関連のコードはVBA自身に書かせる
メタプログラミング on VBA標準モジュール

Excel-VBAを扱う上で避けて通れないのが、シート、セルといったExcelのGUIへのアクセスです。セルアクセスのための情報はシートから取得する必要があり、自分で調べるのは面倒です。いっそのことコードをVBAで作ってしまえば良い。このようなコンセプトで作成されたのが、今回の本題devUtilモジュールです。

Read More ...
Power AutomateでExcelシートのリストからメールを送信する Part1
Power AutomateでExcel行の複数取得とメール送信。

電子メールはビジネスシーンでもっとも幅をきかせているツールだと思われます。メールを送って返信を待つというシチュエーションはよくあります。PowerAutomateを使えば、メール受信をトリガーにフローを開始できることは以前の記事で実証されました。そこで今回はメール送信の自動化に挑戦します。

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

2019-11-18

更新日

2021-07-23

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