【Excel VBA】Outlook内の受信メールや送信済みメール一覧を取得し、Excelに出力する

今回はExcel VBAを使ったOutlookからの受信メール一覧の取得方法について紹介します。

OutlookでのVBAの理解、新規メールの作成については以下で説明しました。

みなさんは「送信済みアイテムの一覧を簡単に見れれば」「特定の顧客とのやり取りを簡単にまとめられれば」と思ったことはないでしょうか？

私は業務ごとに関係者にメールで報告し、履歴を残しています。そしてその履歴を月度ごとに抜き出して部門の月次報告を作成しています。

多くの業務はメールがメインというところも多いと思います。これができると自分の業務の洗い出しや情報の整理もできると思いますのでできる限り使えるように説明したいと思います。

最終的なゴールは（Power Automate Desktopで説明した時と同様）以下です。

エクセルの表に対してフローを実行すると・・・

画像に alt 属性が指定されていません。ファイル名: image-55.png

受信メールの一覧が表示されます。

これができれば特定のメールについてエクセルで一覧に表示するといったことが可能になるかと思います。

Outlook VBAでメール業務を効率化したい人にはこちらの書籍がお勧めです。

Excel VBAユーザーのためのOutlook VBA入門 (技術の泉シリーズ（NextPublishing）)

Amazonで見る楽天市場で見る Yahoo!ショッピングで見る

返信メールの下書きや日報作成など、煩雑な作業が一瞬でできるようになります。

またオブジェクト構造などの説明もあるので、慣れればOutlook上で行うほぼすべての作業を自動化させることができます。

作業前の準備

Excelにメール一覧を表示させる為、新規Excelを立ち上げて以下のような表を作成します。ここではOutlookメール取得.xlsxというファイル名でC:\Users\Publicフォルダに保存します。

参考ファイルはこちらをどうぞ。

594db2b9b1d6f0311c118b9b00fafa60 ダウンロード

Excel VBAでの基本コード

Outlookアプリケーションの取得(参照設定あり）
GetNameSpaceメソッドによるNameSpaceオブジェクトの取得

NameSpaceオブジェクト内のフォルダの取得

の記事についてはこちら

フォルダ内のメールの取得

'colはcollectionの略　Itemsのコレクションを指す
Dim colItems As Items
Set colItems = myFolderInbox.Items

メール内の検索

Items.Restrict メソッド
Restrict("[プロパティ名]='検索文字列'")

Excel VBAのコード

Sub OutlookInbox条件付き取得()
 
Dim WB As Workbook
Set WB = Workbooks.Open("C:\Users\Public\Outlookメール取得.xlsx")
 
'Outlookアプリケーションの取得(参照設定あり）
Dim objOutlook As Outlook.Application
Set objOutlook = New Outlook.Application
 
'GetNameSpaceメソッドによるNameSpaceオブジェクトの取得
Dim myNameSpace As Outlook.Namespace
Set myNameSpace = objOutlook.GetNamespace("MAPI")
 
'NameSpaceオブジェクト内のフォルダの取得
Dim myFolderInbox As Folder
Set myFolderInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
 
Dim r As Long
r = 3
’フォルダ内のアイテムコレクションの取得と文書内の検索
Dim colItems As Items
Set colItems = myFolderInbox.Items.Restrict("[To] = '****@outlook.jp'")
 
Dim objMail As Object
For Each objMail In colItems
  With WB.Worksheets("Sheet1")
    .Cells(r, 2).Value = objMail.ReceivedTime  '受信日時
    .Cells(r, 3).Value = objMail.SenderEmailAddress '送信者アドレス
    .Cells(r, 4).Value = objMail.cc
    .Cells(r, 5).Value = objMail.bcc
    .Cells(r, 6).Value = objMail.Subject
    .Cells(r, 7).Value = objMail.Body
  End With
  r = r + 1
Next
End Sub

■コードの説明

Dim r As Long
r = 3

今回は3行目から記載したいのでrを変数として最初の行=３と設定します。

’フォルダ内のアイテムコレクションの取得
Dim colItems As Items
Set colItems = myFolderInbox.Items

Folder内にあるItemコレクションを取得するコードです。
colItemsはItemコレクションの略称です。
Folder.Itemsでフォルダ内のItemコレクションを取得しています。

1	`Items.Restrict("[To] = '****@outlook.jp'")`

Items.RestrictメソッドはItemコレクションのメールアイテムプロパティに対し特定のフィルターをかけます。
Restrict(“[プロパティ名]=’検索文字列'”)

取得に使えそうなメールアイテムプロパティと使用例は以下です。

プロパティ名	内容	使用例
SenderEmailAddress	送信メールアドレス	Restrict(“[SenderEmailAddress] = ‘*****@outlook.jp'”)
To	宛先	Restrict(“[To] =’*****@outlook.jp'”)
Cc	Cc	Restrict(“[Cc] =’*****@outlook.jp'”)
Bcc	Bcc	Restrict(“[Bcc] =’*****@outlook.jp'”)
Subject	件名	Restrict(“[Subject] =’出荷量について'”)
ReceivedTime	受信日時	Restrict(“[ReceivedTime] >=’2021/3/1′ AND [ReceivedTime] <= ‘2021/3/31′”)
SentOn	送信日時	Restrict(“[SentOn] >=’2021/3/1′ AND [SentOn] <= ‘2021/3/31′”)

Power Automate Desktopでも設定できますがこちらのほうが多くの種類のフィルターが可能です。

Dim objMail As Object
For Each objMail In colItems

objMailというメールオブジェクト変数を設定し、colItems内の各々のメールに対してFor Each～Nextで繰り返し処理を実施します

With WB.Worksheets("Sheet1")
    .Cells(r, 2).Value = objMail.ReceivedTime  '受信日時
    .Cells(r, 3).Value = objMail.SenderEmailAddress '送信者アドレス
    .Cells(r, 4).Value = objMail.cc
    .Cells(r, 5).Value = objMail.bcc
    .Cells(r, 6).Value = objMail.Subject
    .Cells(r, 7).Value = objMail.Body
  End With
  r = r + 1
Next
End Sub

オブジェクトメール内のそれぞれのプロパティをエクセルの各行に入れます。

メールの処理が終わった後には変数rの数字を1つ大きくします。

■ここでのまとめ

メール取得方法は「NameSpaceオブジェクトの取得」→「フォルダーの取得」→「Itemコレクションの取得」→「各々のメールの取得」となります。

オブジェクト構造が分かりにくいですが以前の記事を見ながら習得していきましょう。

Outlook VBAでメール業務を効率化したい人にはこちらの書籍がお勧めです。

Excel VBAユーザーのためのOutlook VBA入門 (技術の泉シリーズ（NextPublishing）)

Amazonで見る楽天市場で見る Yahoo!ショッピングで見る

返信メールの下書きや日報作成など、煩雑な作業が一瞬でできるようになります。

またオブジェクト構造などの説明もあるので、慣れればOutlook上で行うほぼすべての作業を自動化させることができます。