読者です 読者をやめる 読者になる 読者になる

t-hom’s diary

主にVBAネタを扱っているブログです。

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に入れておく。


そして、ExcelVBエディタで「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

セル指定には過去記事でとりあげた列挙型(Enum)を使用しているので、分からなければそちらも併せて確認してほしい。
thom.hateblo.jp

とりあえずこれで3通のメールが仕上がった。
あとは開いて問題ないことを確認しながら、送付ボタンを押していくだけである。

今回取り上げた方法以外にOutlook側にマクロを書いてExcelシートを読み込ませる方法もあるが、Outlookマクロは配布に難があるのでExcelからのOutlook操作をオススメする。

OutlookVBAに関する書籍

今回の記事のようにOutlook連携をしようと思うと、どちらかというとOutlookVBAの知識が必要になる。
残念ながら、日本語書籍でOutlookVBAを扱っているものは皆無だ。発売中の書籍はおろか、絶版書にすら存在しない。一度も発売されたことが無いようだ。

悔しいことに、英語の書籍は充実している。以下はそのうちの一つで、Kindle版も購入できる。

Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators

Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators

Outlook2002対応の書籍でよければ、以下のペーパーバックで中古が安い。

Microsoft Outlook Programming: Jumpstart for Administrators, Developers, and Power Users

Microsoft Outlook Programming: Jumpstart for Administrators, Developers, and Power Users

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。