Excel VBA | Outlook メール通知付きのバッチ処理の設計

Excel VBA VBA
スポンサーリンク

バッチ処理の完了・失敗を自動でメール通知する仕組みを、Excel VBA＋Outlookで作る例です。構成は「処理→結果集計→メール送信→ログ記録」。安全に運用できるよう、リトライ・例外処理・スケジューリングまで含めます。

アーキテクチャと処理の流れ

  • 構成:
    • バッチ本体: データ処理（例: シート行の更新、ファイル入出力）。
    • 通知モジュール: Outlookでメール送信（成功/失敗/サマリ）。
    • ロギング: シート/CSVに開始時刻、終了時刻、件数、失敗理由を記録。
    • スケジュール: 指定時刻に自動実行（ExcelのOnTimeまたはWindows タスクスケジューラ）。
  • フロー:
    • 1. 初期化: ログ開始、設定読み込み（宛先・件名テンプレ）。
    • 2. 実行: バッチ処理ループ（行単位・ファイル単位）。
    • 3. 集計: 成功/失敗件数、失敗一覧。
    • 4. 通知: 成功メール＋失敗詳細、致命的エラーの場合は即時失敗通知。
    • 5. 記録: ログ出力、再実行のための失敗行マーキング。

コード例（Excel VBA＋Outlook）

モジュール構成

  • Module1: エントリーポイントとスケジュール
  • ModuleMail: Outlook送信用の汎用関数
  • ModuleBatch: バッチ本体、集計、ロギング

ModuleMail（Outlook 送信）

' 参照設定なしでも動く「Late Binding」推奨
' 参考: Outlook立ち上げ済みでなくても起動される

Public Function SendMail( _
    ByVal toAddr As String, _
    ByVal subjectText As String, _
    ByVal bodyText As String, _
    Optional ByVal ccAddr As String = "", _
    Optional ByVal bccAddr As String = "", _
    Optional ByVal attachPath As String = "" _
) As Boolean
    On Error GoTo ErrHandler
    Dim olApp As Object, olMail As Object
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0) ' olMailItem = 0

    With olMail
        .To = toAddr
        If Len(ccAddr) > 0 Then .CC = ccAddr
        If Len(bccAddr) > 0 Then .BCC = bccAddr
        .Subject = subjectText
        .Body = bodyText
        If Len(attachPath) > 0 Then
            If Dir(attachPath) <> "" Then .Attachments.Add attachPath
        End If
        .Send
    End With

    SendMail = True
    Exit Function
ErrHandler:
    SendMail = False
End Function
VB

ModuleBatch（処理・集計・ロギング）

Public Sub RunBatchWithNotify()
    Dim startTs As Date, endTs As Date
    Dim okCount As Long, ngCount As Long
    Dim failList As String
    startTs = Now

    On Error GoTo FatalErr

    ' 例: シート「Data」のA2:Aに処理対象ID、B列にステータス
    Dim ws As Worksheet, lastRow As Long, r As Long
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For r = 2 To lastRow
        On Error GoTo ItemErr
        ' 処理対象の例: IDでAPIやファイル更新などを想定
        Dim id As String: id = CStr(ws.Cells(r, "A").Value)
        If Len(id) = 0 Then GoTo SkipItem

        ' 実処理（ダミー）
        Call ProcessItem(id)

        ws.Cells(r, "B").Value = "OK"
        okCount = okCount + 1
        On Error GoTo 0
        GoTo NextItem

ItemErr:
        ws.Cells(r, "B").Value = "NG"
        ngCount = ngCount + 1
        failList = failList & vbCrLf & "Row " & r & ": " & id & " — " & Err.Number & " / " & Err.Description
        Err.Clear
        Resume Next

SkipItem:
        ' 空行はスキップ
NextItem:
    Next r

    endTs = Now

    ' 成功/失敗サマリ通知
    Dim subjectText As String, bodyText As String
    subjectText = "[在庫バッチ] 実行結果: OK=" & okCount & " NG=" & ngCount
    bodyText = "開始: " & Format(startTs, "yyyy-mm-dd HH:NN:SS") & vbCrLf & _
               "終了: " & Format(endTs, "yyyy-mm-dd HH:NN:SS") & vbCrLf & _
               "成功件数: " & okCount & vbCrLf & _
               "失敗件数: " & ngCount & vbCrLf & _
               IIf(ngCount > 0, "失敗詳細:" & vbCrLf & failList, "失敗なし")

    Dim sent As Boolean
    sent = SendMail(GetSettingToAddr, subjectText, bodyText, GetSettingCcAddr())

    ' ログ記録
    LogResult startTs, endTs, okCount, ngCount, sent

    Exit Sub

FatalErr:
    endTs = Now
    ' 致命的エラーの即時通知
    Call SendMail(GetSettingToAddr, "[在庫バッチ] 致命的エラー", _
        "開始: " & Format(startTs, "yyyy-mm-dd HH:NN:SS") & vbCrLf & _
        "終了: " & Format(endTs, "yyyy-mm-dd HH:NN:SS") & vbCrLf & _
        "エラー: " & Err.Number & " / " & Err.Description)
    LogResult startTs, endTs, okCount, ngCount, False
End Sub

Private Sub ProcessItem(ByVal id As String)
    ' 実際の更新処理をここに（例: シート更新やファイル操作）
    ' デモ用: ランダムでエラーを発生させる
    If Rnd() < 0.1 Then Err.Raise 1001, , "Demo failure for ID=" & id
    ' 何らかの処理…
End Sub

Private Sub LogResult( _
    ByVal startTs As Date, _
    ByVal endTs As Date, _
    ByVal okCount As Long, _
    ByVal ngCount As Long, _
    ByVal mailSent As Boolean _
)
    Dim ws As Worksheet, nextRow As Long
    Set ws = ThisWorkbook.Worksheets("Log")
    If ws.Cells(1, 1).Value = "" Then
        ws.Range("A1:E1").Value = Array("開始", "終了", "OK", "NG", "メール送信")
    End If
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    ws.Cells(nextRow, "A").Value = startTs
    ws.Cells(nextRow, "B").Value = endTs
    ws.Cells(nextRow, "C").Value = okCount
    ws.Cells(nextRow, "D").Value = ngCount
    ws.Cells(nextRow, "E").Value = IIf(mailSent, "Yes", "No")
End Sub

' 設定値の取得（宛先など）— 実務ではConfigシートや名前定義から読取
Private Function GetSettingToAddr() As String: GetSettingToAddr = "alerts@example.com": End Function
Private Function GetSettingCcAddr() As String: GetSettingCcAddr = "ops@example.com": End Function
VB

Module1（手動実行とスケジュール）

Public Sub RunNow()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Randomize
    Call RunBatchWithNotify
Cleanup:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

' 例: 毎朝7:30に実行（Excelが開いている前提）
Public Sub ScheduleDaily()
    Application.OnTime TimeValue("07:30:00"), "RunNow", , True
End Sub
VB

運用のポイントと安全策

  • メール送信の安定性:
    • 遅延バインディング（CreateObject）でOutlook起動有無に依存しない。
    • CC/BCC／添付はオプションで柔軟に。添付は存在確認してから追加。
  • 失敗時の可観測性:
    • 失敗詳細を本文に列挙（行番号・ID・エラー番号/内容）。
    • Logシートでサマリを時系列に保存。失敗行はB列にNGマーキング。
  • 再実行のしやすさ:
    • NG行のみ再実行するサブルーチンを追加すると効率的。
    • 設定値をConfigシートに切り出す（宛先・スケジュール・しきい値）。
  • スケジュール方法:
    • Excel常駐なら OnTime。
    • PC常時稼働＋Excel不要なら Windowsタスクスケジューラ＋VBScriptでブックを開いてマクロ実行する方式も有効。
  • セキュリティ/組織ポリシー:
    • Outlookのプログラムによる送信警告が出る環境あり。管理ポリシーを確認。
    • メール送信のレート制限を考慮（まとめてサマリ送信にする、一定件数でバッチ化）。

拡張アイデア

  • HTMLメール: .HTMLBodyで表形式のサマリ（成功/失敗テーブル）。
  • 添付ログ: CSVで失敗一覧を吐き、メールに添付。
  • 通知レベル: 成功は日次サマリ、失敗は即時通知＋重要度タグ付け。
  • 障害ループ防止: 同一致命的エラーが連発したら1回のみ通知して停止。
VBA
スポンサーリンク
シェアする
@lifehackerをフォローする
スポンサーリンク
@lifehacker
スポンサーリンク
タイトルとURLをコピーしました