Excel VBAマクロとOutlookを使ってメールを一括自動送信する方法

はじめに

Excelのスプレッドシートに記載されたユーザーへ、一人ひとり異なる個別情報を含んだメールを一括送信する必要がある場面は少なくありません。本記事では、ExcelのVBAマクロを使い、パソコンに設定済みのOutlookプロファイルから自動的にメールを送信する方法を解説します。

前提となるデータ形式

たとえば、次のような列構成のExcelファイルを想定します。

メールアドレス | 氏名 | 最終パスワード変更日 | アカウント状態

送信するメールのテンプレート

リスト内の各ユーザーに対して、以下のテンプレートでメールを送信します。

件名: woshub.comドメインのアカウント状態について
本文:
Dear %FullUsername%,
Your account in woshub.com domain is in %status% state
The date and time of the last password change is %pwdchange%

%FullUsername%(氏名)、%status%(アカウント状態)、%pwdchange%(最終パスワード変更日)の部分には、各行のセルの値が自動的に挿入されます。

マクロの作成手順

まず新しいマクロを作成します。表示 → マクロ を選択し、マクロ名(send_email)を入力して作成をクリックしてください。

表示されたVBAエディターに、以下のコードをコピー&ペーストします(コメントも参考にしてください)。メール送信の自動化には、VBAスクリプト内でOutlookオブジェクトを作成・操作できる CreateObject("Outlook.Application") 関数を使用します。

重要: 送信元のパソコンにはOutlookプロファイルが設定されている必要があります。このメールボックス(およびメールアドレス)が差出人として使用されます。

Sub send_email()
    Dim olApp As Object
    Dim olMailItm As Object
    Dim iCounter As Integer
    Dim Dest As Variant
    Dim SDest As String
    ' 件名
    strSubj = "Your account status on woshub.com domain"
    On Error GoTo dbg
    ' 新しいOutlookオブジェクトを作成
    Set olApp = CreateObject("Outlook.Application")
    For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
        ' Outlookに新しいメールアイテムを作成
        Set olMailItm = olApp.CreateItem(0)
        strBody = ""
        useremail = Cells(iCounter, 1).Value
        FullUsername = Cells(iCounter, 2).Value
        Status = Cells(iCounter, 4).Value
        pwdchange = Cells(iCounter, 3).Value
        ' メール本文を作成
        strBody = "Dear " & FullUsername & vbCrLf
        strBody = strBody & " Your account in woshub.com domain is in " & Status & " state" & vbCrLf
        strBody = strBody & "The date and time of the last password change is " & pwdchange & vbCrLf
        olMailItm.To = useremail
        olMailItm.Subject = strSubj
        olMailItm.BodyFormat = 1
        ' 1 = テキスト形式、2 = HTML形式
        olMailItm.Body = strBody
        olMailItm.Send
        Set olMailItm = Nothing
    Next iCounter
    Set olApp = Nothing
dbg:
    ' エラーがあれば表示
    If Err.Description <> "" Then MsgBox Err.Description
End Sub

マクロの保存と実行

コードを貼り付けたら、このExcelファイルを .xlsm(マクロ有効ブック形式)として保存します。メールを送信する際は、作成したプロシージャ(マクロ)を選択し、実行をクリックしてください。

マクロはワークシートの全行を先頭から順に処理し、リスト内の各受信者宛てにメールを生成して送信していきます。

実行時の注意点

• 本番環境で実行する前に、少数のテストデータで動作確認を行うことをおすすめします。
• セキュリティソフトやグループポリシーの設定によっては、プログラムからのメール送信時に警告ダイアログが表示される場合があります。
• 送信前に内容を確認したい場合は、コード内の olMailItm.Send を olMailItm.Display に変更すると、送信せずにメール作成画面を表示できます。