ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.009

Excelのメールアドレス データを使用し、Outlookのメールを自動送信する 《CreateObject関数》

2003/2007/2010/2013

Excelにメールアドレスを含むデータが多数あり、それらメールアドレス宛にメールを自動送信する、という内容です。
たびたびメンバーにメール配信するような場合に、一度システムを作っておくといいですね。

ExcelよりOutlookのメールを作成、送信するVBAの基本記述については、ヘルプ「ExcelからOutlookでメールを作成・送信する基本のVBA 《CreateObject関数》」をご参照ください。

今回、メールアドレスなどの情報データ、そして本文の内容も1つのブックに作ります。メール本文は簡単な差し込みを行えるようにします。
また、メールは送信されてしまうと手遅れになったり、メールサーバーに負担となる可能性もありますので、件数(番号)を決めて小分けに送信できるようにします。

以下のように2枚のシートで入力、設定して、自動送信していきたいと思います。
下記にご紹介しているサンプルVBAのブックもありますので、次のリンクをクリックしダウンロードしてご利用ください。

サンプルVBAファイル ダウンロード

<基本の設定:Sheet1>

本文以外の宛先をはじめとする設定、差し込みデータがあれば表に記入します。

メール設定シート
  • セルA1 …差出人メールアドレス
    未記入であれば、Outlookで設定されている既定のメール アカウントで送信
  • セルB3 …件名
  • セルB4 …添付1(例:C:¥Sample.xlsx)
  • セルB5 …添付2
  • セルB8~ B列 …TO宛先のメールアドレス
  • セルC8~ C列 …CC宛先のメールアドレス
  • セルD8~ D列 …BCC宛先のメールアドレス
  • セルE8~ E列 …差し込み文章1(今回は会社名など)
  • セルF8~ F列 …差し込み文章2(今回は担当者名など)
  • セルG2 …送信開始No
  • セルI2 …送信終了No

<本文設定:Sheet2>

本文は、同じ内容は1つのセル内に改行して書いていきます。

本文設定シート
  • セルA1 …会社名(宛先によってE列のデータを差し込む)
  • セルA2 …担当者名(宛先によってF列のデータを差し込む)
  • セルA3 …共通本文
    「Alt」+「Enter」で改行しながら入力

よろしいでしょうか。
ではさっそく以下に、VBAのサンプルをご紹介します。

Sub Sample()

  Dim OL As Outlook.Application
  Dim MI As Outlook.MailItem
  Dim R_Start As Integer, R_End As Integer
  Dim Tenp1 As String, Tenp2 As String

  Set OL = CreateObject("Outlook.Application")
  Tenp1 = Range("B4")  '添付1
  Tenp2 = Range("B5")  '添付2
  R_Start = Range("G2") + 7  '開始番号(開始行)
  R_End = Range("I2") + 7  '終了番号(終了行)

  For R_Start = R_Start To R_End
    Set MI = OL.CreateItem(olMailItem)

    MI.SentOnBehalfOfName = Range("B2")  '差出人
    MI.Subject = Range("B3")  '件名
    MI.To = Cells(R_Start, "B")  'To
    MI.Cc = Cells(R_Start, "C")  'CC
    MI.Bcc = Cells(R_Start, "D")  'BCC

    '添付
    If Tenp1 <> "" Then
      MI.Attachments.Add Tenp1
    End If
    If Tenp2 <> "" Then
      MI.Attachments.Add Tenp2
    End If

    '本文
    MI.Body = Cells(R_Start, "E") & vbCr _
      & Cells(R_Start, "F") & vbCr & vbCr _
      & Worksheets("Sheet2").Range("A3")

    MI.Display  'メール表示
   Next

  Set OL = Nothing
  Set MI = Nothing

MsgBox "完了!"
End Sub

上記のコードを実行すると、次のような、指定した分のメールが立ち上がってきますので、1件ずつ確認して[送信]ボタンを押して送信してください。

メール

メール内容の確認をせずに、即送信してよいのであれば、

  MI.Display  'メール表示

となっているところを、

  MI.send  'メール送信

としてください。

以下、少し補足していきます。

今回のサンプルでは、送信件数を決めて実行しますので、For~Next構文のループで書いております。
For~Nextのカウンタ変数は、送信する宛先リストの行番号となっています。

上図のシートSheet1の図では、「メール!!」ボタンがあります。
ボタンクリックでメール送信できるようにするには、ヘルプ「ワークシートにボタンを作成し、マクロを割り当てて実行できるようにしたい」をご参照ください。

twitter hatena line pocket

関連ヘルプ

ブック内のすべてのワークシートで同じ処理を行う 《For Each~Next》
ブックを開くとき、閉じるときにマクロを自動実行したい 《イベント プロシージャ》
データを快速で検索するには 《Find》
アプリケーションを起動し動作させる 《Shell・SendKeys・Wait》
エラーの種類、そしてエラー処理の設置方法について 《On Error GoTo》
InputBox のテキスト ボックス入力時に日本語入力をオンにする 《SendKeys》
Excelのデータをテキスト ファイルに書き出す(出力する) 《Open・Print・Close》
ExcelからOutlookでメールを作成・送信する基本のVBA 《CreateObject関数》
ソートしたグループごとのデータを別ブックに分割するサンプルVBA
右クリック、印刷、上書き保存などブックに規制をかける 《イベント プロシージャ Cancel=True》
フォルダー内のすべてブックを開いて同じ処理を行う 《Do~Loop・Dir》
フォルダー内のすべての画像ファイルをペイントで開きサイズ変更
フォルダー内のフォルダーとファイルの一覧をセルに書き出す1 《Dir》
フォルダー内のフォルダーとファイルの一覧をセルに書き出す2 《FileSystemObject》
フォルダー内のファイル名を変更する 《Do~Loop・Dir・Name~As》
ColorプロパティとRGB関数について、Color値からRGB、RGBからColor値を求める方法 《Color・RGB関数》