【ファイル付き】Excel VBAでOutlookメールを送付する方法

VBAでOutlookのメールを送る

※この記事はWindowsを前提に書いております。

今回はVBAを使ってOutlookのメールを送る方法を教えるよ!
確かに、大人数に微妙に内容を変えて送りたいときあるね。
今回はExcelファイル付きでソースコードの解説もやっていくよ!

※本記事はWindowsを前提に書いております。また、ファイル形式が「.xlsm」となっておりますので、この形式ファイルが扱えることが条件です。

以下のファイルより、Excelファイルをダウンロードしてご利用ください。

VBA_send_mail.xlsm

目次目次[閉じる]

Outlook Object Libraryを有効化する。

まずは、Excel VBAでOutlookを使えるように設定をしましょう。

ExcelでVisual Basicを立ち上げます。「開発タブ」の「Visual Basic」を選択するか、ショートカットの「Alt + F11」を押します。

Visual Basicの上のメニューから 「ツール(T)」 → 「参照設定(R)」 と選択し、

「Microsoft Outlook xx.x Object Library」にチェックを入れ、「OK」をクリックします。

vbaでoutlookが使えるように設定する

これでOutlookがExcel VBAで使用できるようになります。

メールの下書きを作成する

次にOutlookで新規メールを作成して下書きを作成します。

下書きメールからVBAでメールを送信する最大のメリットは何かと言うと

太字や色付きの装飾されたメール文を簡単にVBAで送ることができる。

ということです。

HTMLなどの知識が不要なので、いつものようにメールを作成するようにメールを作成してください。

例として以下のようなメールを作成しました。

作成したメールサンプル

今回は、複数の人にメール本文の内容を一部変えながら送ります。

具体的には宛名である「<<名前>>」と、プレゼント内容の「<<贈り物>>」の部分を送る人によって変えていきます。

下書きが完成したらメールを保存をします。

必ず「名前をつけて保存」でファイル形式を「.otf」形式にしてください。

メールファイルの保存画面

メールを保存したら下書きの作成は完了です。

テーブルを準備する

「TABLE」という名前のシートには以下のようなテーブルが作成されています。

excelのテーブルの情報

一番左がメールアドレスで送りたい相手のアドレスで、2列、3列目がメール本文で置換したい情報です。

先ほど作成した下書きの「<<名前>>」と「<<贈り物>>」がテーブルの2、3列のカラム名(列の名前)と一致していることを確認してください。

今回の例では、メール本文中の「<<名前>>」と「<<贈り物>>」を「田中 太郎」、「チョコレート」などと置換して送ることになります。

VBAでメールを送信する

テーブルの値をVBAでデータ配列に格納する

TABLEのシートにあるデータをVBAで取得してみましょう。

Dim ws_table As Worksheet
Dim data As Variant
Set ws = ThisWorkbook.Sheets(" TABLE")
data = ws.Range("A1").CurrentRegion

上記のコードでTABLEのシートにある情報を一気にdataという配列に2次元データとして格納します。

データを配列に入れる方法について詳しく知りたい方は以下の記事をどうぞ。

保存したメール下書きのファイルパスを取得する

先ほど作成したメールの下書きのファイルパスを以下のコードで取得します。

fPath = Application.GetOpenFilename(Filefilter:="メールテンプレート,*.oft", Title:= "メールテンプレートを選択してください。"')

コード実行時に「メールテンプレートを選択してください。」というタイトルのファイル選択画面が立ち上げるので、先ほど作成した下書きを選択します。

選択すると、そのファイルのパスが「fPath」に文字列として代入されます。

ちなみに上記コードのように「Filefilter」を設定することで特定の形式のファイルのみを選択するように制限することができます。

下書きメールをVBAで呼び出す

以下のコードで下書きメールの情報を取得しています。

Set OTL = CreateObject("Outlook.Application")
For i = 2 To UBound(data, 1)
    Set NewMail = OTL.CreateItemFromTemplate(fPath)

1行目でOutlookオブジェクトを作成しています。OutlookをVBAで扱うときのおまじないだと思っていただければOKです。

2行目では、メールをテーブルの2行目から最終行の人まで送る必要があるのでその数だけループさせています。

3行目で、作成した下書きの情報を呼び出して新規のメールとして作成しています。

.CreateItemFromTemplate(ファイルパス)は文字の意味の通り「メールをテンプレートから作成します」という意味ですね。

VBAのReplaceメソッドで本文を置換する

次に、取得したメール本文をそれぞれの送り先の人の情報に置換していきます。

HTML = NewMail.HTMLBody
    For j = 2 To UBound(data, 2)
    HTML = Replace(HTML, data(1, j), data(i, j))
    Next j

まず、1行目のNewMail.HTMLBodyで下書きからメール本文をHTML形式で取得しています。

2行目〜3行目で取得した本文中の「<<名前>>」と「<<贈り物>>」の部分を置換しています。

もう少し詳しく説明します。

まず2行目のFor文ですが、jはテーブルの列番号を示しています。Uboud(data,2)はテーブルの列の数を表しています。

つまり、今回では2行目の<<名前>>の列から<<贈り物>>の列までループさせることになります。

実際に本文を置換しているのは3行目のHTML = Replace(HTML, data(1, j), data(i, j))です。

「Replace」というメソッドで「HTML」の中の「data(1, j)」を「data(i, j)」に置換します。

i=1, j=2のときは以下のような対応関係になります。

HTML メール本文
data(1, j) <<名前>>
data(i, j) 田中 太郎

つまり、メール本文の「<<名前>>」を「田中 太郎」に置換します。

VBAでメールに必要な情報を設定して送信する

ここまで来ればゴールはもうすぐです。

以下のコードでメールに必要な情報を設定してメールを発信します。

NewMail.HTMLBody = HTML
NewMail.To = data(i, 1)
NewMail.SendUsingAccount = Session.Accounts("Your_mail_adress")
NewMail.Send

1行目で置換後のメール本文をメール文としてセットします。

2行目でメールの送り先(TO)のセットをします。

3行目で自分のOutlookアカウントをセットします。自分のOutlookのメールアドレスを入力してください。

当然ですが、自分以外のアカウントをセットした場合エラーになってしまいますのでご注意ください。

4行目で作成したメールを送信します。

最後に以下のコードでOutlookオブジェクトを閉じたら終了です。

Set OTL = Nothing
Set NewMail = Nothing

コードを実行後の送信メールを確認します。

vbaで送信したメール

きちんとテーブルの値がメール本文に挿入されていますね。

終わりに

以上、いかがでしたでしょうか?

今回のマクロのいいところはVBAの知識がない人でも使えることです。

使うときの操作は

  • メールの下書きを作成
  • 置換したい言葉をメール本文とExcelのテーブルに入れておく

の2つだけです。

今回の記事では、メールの下書きをVBAで送る方法の最低限の部分だけ抜粋してご紹介しました。

他にも、「メール送信前に下書きを作成して事前に確認する方法」や「メールタイトルもメールごとに変更する方法」などをオプションとして実装する方法もお伝えしようと思います。

ぜひ、定型のメールをOutlookで大量に送る場合にご活用いただければ幸いです。

それではお疲れ様でした。