【Outlook VBA】Outlookで受信したメールをExcelに自動記録する方法

Outlookで受信したメールをExcelに自動記録できないか調べたところ、Outlook VBAで実装することができました。今回はその方法をシェアします！

VBAとは

Visual Basic for Applicationsの略で、Microsoft Officeに拡張機能として搭載されているプログラミング言語です。マクロを作成する言語の一つがVBAというイメージです。

マクロというとExcelのイメージがありますが、今回は「メール受信時に」という条件があるため、あらかじめイベントが用意されているOutlook VBAを利用します。

開発環境構築

リボンに開発タブを表示させる

Outlookを起動させたら、リボン（上部のファイル、ホーム…と並んでいるところ）に開発タブを表示させます。

「ファイル」→「オプション」→「リボンのユーザー設定」に進み、右側の開発のチェックボックスにチェックを入れます。

VBEを起動する

開発タブ一番左の「Visual Basic」をクリックします。
VBAのエディタ、VBE（Visual Basic Editor）が起動します。

参照設定をする

Outlook VBAでExcelのオブジェクトを使用できるようにします。
VBE内、「ツール」→「参照設定」でExcelのチェックボックスにチェックを入れます。

ThisOutlookSessionを開く

左に表示されている「ThisOutlookSession」をダブルクリックすると、右にウインドウが開きます。ここにマクロを書いていきます。

※今回使用するApplication_NewMailExイベントは標準モジュールに記載しても動作せず、ThisOutlookSessionに記載する必要があります。

実装条件

今回は、以下の条件で実装しました。
●Outlookでメール受信時に、Excelに自動記録する
●ExcelファイルはExcel Online上にあり、複数人で共有している
●自分もしくは他の誰かがExcelを開いていても記録できるようにする

サンプルコード

//Sleep関数を利用するためのAPI宣言
#If Win64 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

//イベントプロシージャの開始宣言
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
//変数の定義
Dim myNameSpace As NameSpace
Dim objId As Object

//Namespaceオブジェクトの取得
Set myNameSpace = GetNamespace("MAPI")
//Outlookアイテムの取得
Set objId = myNameSpace.GetItemFromID(EntryIDCollection)

    //変数の定義
    Dim strFile As String
    Dim objExcel As Excel.Application
        
    strFile = "Excelファイルのパス"

    //Excelオブジェクトの作成
    Set objExcel = New Excel.Application
    //Excelブックを開き情報を取得
    Set wb = objExcel.Workbooks.Open(strFile)
    //Excelシートの情報を取得
    Set ws = wb.Worksheets("シート名")

    //オブジェクト名を宣言
    With objId
        //最終行＋1行（入力行）を取得
        i = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
        
        //入力列と入力内容を指定
        ws.Cells(i, 1).Value = .SentOn　//送信日
            If InStr(.SenderEmailAddress, "@aaa.jp") > 0 Then　//送信者メールアドレス
              ws.Cells(i, 2).Value = "A社"
            ElseIf InStr(.SenderEmailAddress, "@bbb.jp") > 0 Then
              ws.Cells(i, 2).Value = "B社"
            Else
              ws.Cells(i, 2).Value = ""
            End If
        ws.Cells(i, 3).Value = .SenderName　//送信者名
        ws.Cells(i, 4).Value = .Subject　//件名
        ws.Cells(i, 5).Value = .Body　//本文
    End With

wb.Save   //変更を保存
Sleep 700   //指定した秒数だけ処理を止める   
wb.Close   //ブックを閉じる
objExcel.Quit   //アプリケーションを終了する

End Sub

コード解説

Application_NewMailExイベント

Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)

End Sub

Outlookでメール受信時にExcelを操作したい場合、Application_NewMailExイベントを使用します。

Application_NewMailExイベントの引数にEntryIDCollectionがあり、Outlookのアイテムフォルダに保存される際付与されるアイテムID（＝EntryID）を渡してくれます。

NameSpace.GetItemFromIDメソッド

Set objId = myNameSpace.GetItemFromID(EntryIDCollection)

EntryIDで表されるOutlookのアイテムを返してくれます。

Withステートメント

With objId

End With

ステートメント内で、指定したオブジェクト名を省略することができます。
「objId.Subject」→「.Subject」のようにドットで表記します。

入力列と入力内容の指定

ws.Cells(i, 1).Value = .SentOn　//送信日
  If InStr(.SenderEmailAddress, "@aaa.jp") > 0 Then　//送信者メールアドレス
     ws.Cells(i, 2).Value = "A社"
  ElseIf InStr(.SenderEmailAddress, "@bbb.jp") > 0 Then
     ws.Cells(i, 2).Value = "B社"
  Else
     ws.Cells(i, 2).Value = ""
  End If
ws.Cells(i, 3).Value = .SenderName　//送信者名
ws.Cells(i, 4).Value = .Subject　//件名
ws.Cells(i, 5).Value = .Body　//本文

入力列は、Cells(i, 1) の部分で数字で指定しています。

入力内容は、
●SentOn　…送信日
●SenderEmailAddress　…送信者メールアドレス
●SenderName　…送信者名
●Subject　…件名
●Body　…本文
など用意されたプロパティで設定します。

サンプルコードのようにif文での記述や、” “で任意の内容を設定することも可能です。

Sleep関数

Sleep 700

ミリ秒単位で、指定した秒数だけ処理を止めることができます。

これがないと、ファイルを開いている状態で記述ができませんでした。 反映に時間がかかるため、ブックを閉める処理を遅らせることで反映できるようになったようです。

尚、Sleep関数を利用するにはAPI宣言が必要となります。
宣言はプロシージャの前に記述する必要があり、32Bit版/64Bit版で記述内容が異なるため条件分岐させています。

#If Win64 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

補足

Excelファイルのパスは、Excel Onlineのものでも問題ありませんでした。
Sleep関数は環境に応じて、秒数を変えたり取り除いたりしてください。

さいごに

自動入力されることで入力忘れ防止にもなりますし、とても便利になりました。
条件に合わせてカスタマイズし、ぜひ使ってみてください！