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オブジェクトのプロパティにセルの値を格納していきます。シートの構成は次のようにします。一部セルが黄色い理由は後述。

さて、ここからセルへ入力された値を使ってメールを作成するわけですが、セルへアクセスして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ファイルをページ下部からダウンロードしてご利用いただけます。