VBAでOutlookメール本文の一部を抽出しExcelへ出力する方法！

VBAを使用してOutlookから日次で送られてくるメールから一部の情報を抽出しExcelへリスト化したいときはないでしょうか。

ただ、そんな時はどのようにすればよいかわからないものですよね。

今回は、

について説明します。

タカヒロ

日次のメール処理を自動化するのに役立つと思います！

ExcelからOutlookのメールを作成・送信する機能の処理の流れについて

今回の機能の処理は以下の通りExcelからOutlookを起動し、Outlookのメール抽出、Excelへの出力が行われる流れとなります。

では、早速実装をして動かしてみましょう！

Excel VBAからOutlookを操作するための下準備

①まずExcelを起動し、「開発」タブをクリックします。

②VBEの画面が開いたら、メニューから「ツール」>「参照設定」を選択します。

③「Microsoft Outlook XX.X Object Library」を探してチェックボックスにチェックし「OK」をクリックします。

以上です。

Excel VBAでOutlookメール本文の一部を抽出する方法

Excelを用意する

Excelを新規作成し、抽出した情報を出力する先となるシートを作成します。シート名は”Sheet1”です。

できたらマクロ付きファイルとして任意の場所に保存しましょう。

メールを用意する

抽出するメールをいくつか用意し送信します。

サンプルでは以下とし、赤枠内の日付、機種名、状態を対象に、それぞれの値をエクセルのセルへ代入していきます。

VBAコードの作成

次にOutlookの連携を設定し、必要な情報を抽出してExcelに書き込むためのVBAコードを作成します。
以下のVBAコードは、Outlookの受信トレイから特定の件名を持つメールを検索し、
本文の中から日付、機種名、状態を抽出してExcelシートに書き込む内容となっています。

Sub メール本文を抽出しエクセルへ出力する()
    Dim OutlookApp As Outlook.Application
    Dim OutlookNamespace As Outlook.Namespace
    Dim OutlookFolder As Outlook.Folder
    Dim OutlookMail As Outlook.MailItem
    Dim i As Integer
    Dim strMailBody As String
    Dim strSubjectDate As String
    Dim strExtractedDate As String
    Dim strExtractedModel As String
    Dim strExtractedStatus As String
    Dim ws As Worksheet
    Dim longNextRow As Long

    ' Outlookアプリケーションを初期化
    Set OutlookApp = New Outlook.Application
    Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
    Set OutlookFolder = OutlookNamespace.GetDefaultFolder(olFolderInbox)

    ' 出力先のシートを設定
    Set ws = ThisWorkbook.Sheets("Sheet1")
    longNextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    
    ' 項目名をExcelに書き込む
    ws.Cells(1, 1).Value = "件名"
    ws.Cells(1, 2).Value = "日付"
    ws.Cells(1, 3).Value = "機種名"
    ws.Cells(1, 4).Value = "状態"

    ' 受信トレイのメールをループ
    For i = 1 To OutlookFolder.Items.Count
        If TypeName(OutlookFolder.Items(i)) = "MailItem" Then
            Set OutlookMail = OutlookFolder.Items(i)
            If InStr(OutlookMail.Subject, "【重要】処理状況") > 0 Then
                strMailBody = OutlookMail.Body
                strSubjectDate = Mid(OutlookMail.Subject, InStr(OutlookMail.Subject, "状況") + 4, 10)
                
                ' メール本文から情報を抽出
                strExtractedDate = ExtractInfoFromBody(strMailBody, "日付：")
                strExtractedModel = ExtractInfoFromBody(strMailBody, "機種名：")
                strExtractedStatus = ExtractInfoFromBody(strMailBody, "状態：")

                ' 抽出した情報をExcelに書き込む
                ws.Cells(longNextRow, 1).Value = OutlookMail.Subject
                ws.Cells(longNextRow, 2).Value = strExtractedDate
                ws.Cells(longNextRow, 3).Value = strExtractedModel
                ws.Cells(longNextRow, 4).Value = strExtractedStatus
                longNextRow = longNextRow + 1
            End If
        End If
    Next i

    ' オブジェクトのクリーンアップ
    Set OutlookMail = Nothing
    Set OutlookFolder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing
End Sub

Function ExtractInfoFromBody(strMailBody As String, Keyword As String) As String
    Dim Lines As Variant
    Dim Line As Variant
    Dim ExtractedText As String
    
    ' メール本文を行ごとに分割
    Lines = Split(strMailBody, vbCrLf)
    
    ' 各行をループしてキーワードを検索
    For Each Line In Lines
        If InStr(Line, Keyword) > 0 Then
            ExtractedText = Trim(Replace(Line, Keyword, ""))
            Exit For
        End If
    Next Line
    
    ExtractInfoFromBody = ExtractedText
End Function

コードの設定

コードの設定をしていきましょう。

以下は項目名を設定している箇所です。
追加、変更したい場合はこちらを変えてください。

対象メールの条件を設定します。
サンプルでは”【重要】処理状況”が件名に含まれていれば抽出対象として、処理を継続します。
件名を別のパターンにしたい場合はこちらを変えてください。

コードの説明

コードの内容について説明します。

メイン処理

• Outlookアプリケーションと受信トレイを設定し、メールをループして特定の件名を持つメールを検索します。
• 件名の日付を抽出し、本文から「日付」、「機種名」、「状態」を取得します。
• 抽出した情報をExcelの指定シートに書き込みます。

情報抽出関数

• ExtractInfoFromBody関数は、メール本文から特定のキーワードに続く情報を抽出します。

VBAを実装する

VBAを実装します。

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。
①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のマクロを入力します。

こちらで完了です。

VBAを実行する

早速VBAを実行してみましょう。

①「開発」タブの「マクロ」をクリックします。

②「メール本文を抽出しエクセルへ出力する」を選択し、「実行」をクリックします。

はい、抽出された結果がエクセルに反映されましたね！

トラブルシューティング

• Outlookの参照設定がない
  正しい参照設定がされているか確認してください。「ツール」→「参照設定」で「Microsoft Outlook xx.x Object Library」にチェックが入っていることを確認します。
• メールが読み込まれない
  メールのタイプが「MailItem」であることを確認してください。また、Outlookの受信トレイにメールが存在することを確認してください。
• キーワードが見つからない
  “【重要】処理状況”が件名に正しく設定されているか、メール本文に各項目名が含まれているかを確認してください。

まとめ

VBAを使用してOutlookメールの特定情報を抽出し、Excelに自動的に書き込む方法を紹介しました。

この技術を使用することで、日次のメール処理を効率化し、データの整理が容易になります。

業務効率化にぜひ役立ててください。