【Excelをメール検索GUIとして使う】PowerShellでOutlookのメールアイテム一覧をcsvで吐いてPowerQueryに食わせる

## 前置き：Outlookのメール検索機能がとにかくイケてない
Outlookのメール検索機能がイケてない（検索ワードと無関係なものまでヒットする）ので以前こんなものを作ろうと画策していました。

https://qiita.com/taleau/items/73d2c086c58644803975

が、VBA回してる間Outlookが止まるのも嫌だし何よりコードが複雑でやってられない。。。

そこでPowerShellに手を出してみたら見様見真似で何となくで書いたレベルでもあっという間にできた。


## 構成
1. PowerShellでcsv出力
1. csvをPowerQueryで読み込む
1. PowerQueryの出力テーブルをUIとしてExcelの普通の機能でメール検索する
1. ピボットテーブルやスライサー、簡単なマクロでサポートもできる


## PowerShellのコード

``` powershell
# Outlookのフォルダからメール一覧を作成、CSVに吐き出す

# 参考（Outlook取得）：https://www.itlab51.com/?p=6171
# 参考（CSV出力）：https://use-computer.net/powershell/powershell-course-5

$olFolderInbox = 6
$oOutlook = New-Object -ComObject Outlook.Application
$oNamespace = $oOutlook.GetNamespace("MAPI")
$oFolder = $oNamespace.GetDefaultFolder($olFolderInbox)

# ループ回数の上限を設定（CSVに書き出すメール数）
$LimLoopCnt=10000

# 取得する文字数に制限をかける
$LimLenStr=10000                

# 変数の初期化
$i=0
$CsvData = @()

foreach($oItem in $oFolder.Items){
        # 取得する文字数に制限をかける
        $Str = $oItem.Body
        $LenStr = ($oItem.Body).Length
        if ( $LenStr -gt $LimLenStr)
            {$Str = $Str.Substring(0,$LimLenStr)}


        # 1行ずつCSV用の変数に足していく
        $CsvData +=[PSCustomObject]@{
            No = $i
            件名 = $oItem.Subject
            本文 = $Str.Replace("`r`n","") #改行殺し
            受信日時 = $oItem.ReceivedTime
            受信者名  =$oItem.ReceivedByName
            送信者名  =$oItem.SenderName
            CC = $oItem.CC
            ConversationID = $oItem.ConversationID
            添付ファイル数 = $oItem.Attachments.Count
        }

        # ループ脱出
        $i = $i + 1
        if( $i -gt $LimLoopCnt){break}
    }

# CSVに吐き出す
$CsvData | ft -AutoSize
$CsvData | Export-Csv "ol_受信BOX_Itmes.csv" -Encoding Default

```

実行するとこんなcsvが出力される。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/ff327419-d67c-4269-36c6-215e835e4a5a.png)


## PowerQueryへの給餌手順

### CSVデータの読み込み
Excelの`データ`タブから
`データの取得と変換`＞`テキストまたはCSVから`を押す

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/83d40fe9-a495-b6c4-b384-79feb62e792c.png)

### `データの変換`を押す

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/c48cea10-038b-f2d5-be55-72c76f271db4.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/0504b6cd-c4d8-dc25-c869-fed01105448e.png)

### 1行目の余計なものを消す
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/e83e8a73-2f9b-8299-78d6-fae47b13ca84.png)

### 残ったテーブルの1行目をヘッダとして使用する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/310bb13e-c8d2-3d82-4c9f-69559b1e6455.png)

### あとは閉じて読み込むだけ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/d11e6bbb-d798-9f68-9b8f-845d24dfcec4.png)

### データ更新時はPowerShell実行後にPowerQueryのデータ更新するだけ
リボンからでも
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/87731e0c-5dde-7169-aa7c-f5ff2e8237fc.png)

右クリックメニューからでも
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/6ad4f001-f2ff-c551-9fe6-14bf2bc85515.png)
どちらでもOK

## Excelの普通の機能でメール検索する
GUIを作っていくのが面倒なのと、ExcelそのものがGUIとして超優秀なのでそのまま使います。
### PowerQueryの出力がテーブルなのでそのままスライサーも入れられます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/198377b5-2158-b594-ffac-abde9a660987.png)

### ピボットテーブルで`ConversationID`ごとにグループ化しても良さげ
`ConversationID`というのは返信や転送などで繋がったメールアイテムのグループIDなので、これを親にしておくとやりとりの流れが拾いやすいかと思います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281933/94de8ecc-a168-f7da-9183-fc20e49ff963.png)

## 残課題
### 添付ファイル名一覧も取り込みたい

`$oItem.Attachments.Items`の中身を`foreach`で回せば良さげなのでまたやりたい

### 送信BOXやローカルに移動させたデータファイルも一括読み込みしたい

### 選択レコードをフォームに表示したい
テーブルそのままを読むのは結構読みづらいのでユーザーフォームに表示するくらいはやってやろうじゃないの


## 参考ページ
### Outlook取得
https://www.itlab51.com/?p=6171

### CSV出力
https://use-computer.net/powershell/powershell-course-5