ExcelからOutlookを操作して大量のメールを作成する

業務で複数の方にメール通知をする機会がある方もいると思う。
数件なら良いが、数十件、数百件ともなると一件ずつコピー＆ペーストで作成していては大変だ。

そこで、ExcelからOutlookオブジェクトを利用して一気に作成する方法を紹介する。

まず送付先のアドレスや宛名などの可変項目をリストで用意する。
f:id:t-hom:20150530153153p:plain

それから、送付するメールの本文を用意し、テンプレート(拡張子:oft)として保存する。
f:id:t-hom:20150530152632p:plain

以下、c:\work\test.oftとして保存したものとする。
添付ファイルのtest1～test3.txtもc:\workに入れておく。

そして、ExcelのVBエディタで「Microsoft Outlook XX.X Object Library」にチェックを入れておく。

ここからいよいよコード作成。
ひとまず、メールを1通だけ作成するマクロを書いてみる。
※Outlookではなく、Excelマクロとして送付リストのシートモジュールに書く。

Sub メール作成()
    Dim ol As New Outlook.Application
    Dim m As MailItem
    
    Set m = ol.CreateItemFromTemplate("c:\work\test.oft")
    m.To = "test@thom.jp"
    m.Subject = "TESTさん用テスト"
    m.Attachments.Add "c:\work\test1.txt"
    
    m.HTMLBody = Replace(m.HTMLBody, "●●", "TEST")
    m.SaveAs "c:\work\sample.msg"
End Sub

これを実行すると、c:\workにsample.msgというファイルができる。
開いてみると、宛先・件名・宛名・添付ファイルがコードのとおりに正しく設定されていることが分かる。

セキュリティ設定によって、Outlook側で警告ダイアログが出る場合がある。
これは、外部プログラム(Excel)がOutlookにアクセスしようとしているが許可するかどうかの確認である。
何分間のアクセスを許可するといった選択肢があるはずなので、適宜許可することでマクロは実行できる。

さて、次に実際にエクセルの表に従ってメールを大量作成していくマクロであるが、1通作成できればあとは繰り返すだけなのでさほど難しくはない。
完成したのは以下のコード。

Enum 列
    宛先 = 1
    宛名
    件名
    添付ファイル
End Enum

Sub メール作成()
    Dim ol As New Outlook.Application
    Dim m As MailItem
    
    Dim MaxRow: MaxRow = Range("A1").End(xlDown).Row
    For i = 2 To MaxRow
        Set m = ol.CreateItemFromTemplate("c:\work\test.oft")
        m.To = Cells(i, 列.宛先).Value
        m.Subject = Cells(i, 列.件名).Value
        m.Attachments.Add "c:\work\" & Cells(i, 列.添付ファイル).Value
    
        m.HTMLBody = Replace(m.HTMLBody, "●●", Cells(i, 列.宛名).Value)
        m.SaveAs "c:\work\" & Cells(i, 列.宛名).Value & ".msg"
    Next i
End Sub