脱メールとは言え、企業の情シスが規制しているが為に業務改善を行いたくても、各種REST APIが利用できなくされていたり、また外部のウェブサービスを利用させてもらえず、そこで停滞したまま効率化されずにいる日本企業は非常に多いです。

しかし、メールは原始的なコミュニケーション手段であり、埋もれる、取りこぼす、調べるのに莫大な時間を要する、また個人宛なので過去の経緯などが次の人に引き継がれず、また活用するにしてもノイズが多く使いづらいです。そこでAccessと連携させる事で、必要なメール情報だけを取り込んで、データ化する事で、利便性が向上します。

難易度:

今回使用するファイル類

リンクテーブルを利用する

手動でリンクテーブルにて接続

あまり知られていない機能ですが、Accessは標準でOutlookの受信トレイなどにリンクテーブルを簡単に貼る事が可能です。受信トレイ以外にもリンクテーブルを貼る事はもちろん可能。共有メールボックスや連絡先などにも接続できたりするので、色々と応用が利くと思います。接続手順は以下の通り。

  1. Accessメニューの外部データを開き、新しいデータソース⇒他のソースから⇒Outlookフォルダを選択
  2. ダイアログが出るので、「リンクテーブルを作成してソースデータにリンクする」にチェックをいれて、次へ進む
  3. どのフォルダにリンクするか?を選んで、次へをクリック。今回は受信トレイを選択。
  4. リンクするテーブル名を設定して完了をクリックするとリンクテーブルとしてOutlookのフォルダが表示される
  5. あとは、普通のテーブルと同様に中に表示されたデータを取り出しす事が可能。ただし、追加することはできません

リンクしたテーブルを見てみるとかなり細かくデータが入ってることがわかります。いくつか気になる点としては

  • Microsoft365のExchange Serverを利用してる場合、メールアドレスがなく特殊な形式になってる(外部の人の場合はメールアドレスはしっかり入ってる)
  • 本来あるはずの各メールのユニークなID(EntryIDやMessageID)などは無い。よって、そのメールに返信したかどうかの判定はメールタイトルなどから判断しなければならない(ぶら下がり先がわからない為)
  • 本人宛やCC、TOなどはわかるが、BCCについては表示がない
  • 添付ファイルなどはその有無はフラグでわかるが、データとしてはテーブルから取ることは出来ない
  • 共有メールボックスの一部はリンクできないケースもある
  • メールボックスのメール数が多いとリンクテーブルの場合非常に動作が遅くなる。

図:受信トレイと直接接続してみた

図:Teamsの会話やタスクなども接続可能みたい

VBAで動的にリンクテーブルを作る

プログラムとして配布し利用してもらうにあたっては、上記の手動でリンクテーブル貼ってもらうというのは正直ちょっとスマートな方法ではありません。しかし、リンクテーブルは人によってリンク先のパスが異なる(Outlookの場合、C:Usersユーザ名AppDataLocalTempが接続先なので、ここがネックになる)

そこでVBAにて自動でワンクリックでリンクテーブルを貼れるようにしておくと良いでしょう。ODBCリンクテーブルと同じような要領で貼る事が可能です。

  • 本コードを使うには、参照設定にてMicrosoft Outlook 1x.0 Object Libraryを加えておく必要があります。
  • WScriptを利用してログインユーザ名を取得させています。
  • 今回はtblInboxという名前でリンクテーブルを作成しています。
  • 接続文字列がODBCとは違いちょっと特殊です。この時、ログインユーザ名を利用してパスを組み立て、また予めどのアカウントなのか区別するため、mailmanにメールアドレスを入れておく必要があります。
  • 今回は受信トレイが対象なので、td.SouceTableNameには受信トレイを入れておきます。
  • デフォルトのその人のアカウント名を今回は自動で取得。olNS.Accounts.Item(1).SmtpAddressで実現していますが、複数アカウントがある場合にはItemの引数の番号を変更すれば良い。またはFor Eachで回して取得するのも良いでしょう。
  • 起動時に自動でリンクテーブルを作り直すように、Autoexecマクロを利用して、リンクテーブル削除と組み直しを自動実行しておくと尚良いでしょう。

VBAからOutlookを操作する

VBAとOutlookの参照設定にて、リンクテーブルよりも多くのメール情報をレコードとして取得し格納する事が可能です。ただし、この方法でも「Message-ID」や「In-Reply-To」などの固有のIDや返信先のIDなどは取得が出来ない。EntryIDというIDが各メールに割り振られているものの、このIDは不変ではなく、フォルダを移動したりすると変更されてしまうので、扱いに注意が必要です。

通常のメールボックスメールを取得する

通常の自分自身のメールボックスのメールを取得します。受信フォルダ以下に作成したサブフォルダに対して取得を実行する事も可能です。この手法の場合、共有メールボックスのメールは取得できないので注意が必要です。

  • 6000通のメールでおよそ5分ほど掛かります。
  • リンクテーブルではないので、受信後データの表示や処理は高速に行えます。
  • 日付時刻を見て現在取得済みメールの最大値より大きい値(つまり未来のメール)だけを取得して追加するようにしてあります。DMaxで最大値を取得し、DateDiffの秒での比較で0よりプラスの値がそれになります。
  • 各メールのItemのプロパティは様々なものが用意されています。こちらのページのPropertiesがそれに該当します。
  • 稀にSenderに値のないものがあってコードが止まるケースの為に、on error resume nextを入れてあります。
  • 指定のメールアドレスの取得はmailman変数に格納し、.Session.CreateRecipientにてセットする
  • 共有メールボックスからのメール取得は出来ません
  • .GetDefaultFolderの第二引数では、olFolderInboxを指定すると受信トレイになります。こちらにいろいろなフォルダの指定用の値が一覧で表示されています。
  • 受信トレイ以下に作成したサブフォルダを対象にしたい場合にはsubfolderFolders.Itemにて指定し、それをループで回せばOK
  • 今回のコードは使ってるOutlookでのデフォルトアカウントが1個だけの場合です。

Outlookに複数のアカウントがある場合には以下のようなアカウントの特定とGetDefaultFolderの指定はアカウントをループして特定してセットすると良いです。以下のようなコードに置き換えます。このとき指定のメアドに自分の2つ目のメアドを入れて判定させます。

また、手動でアカウントと指定のフォルダ内を取得したい場合には以下のような決め打ちも可能です。指定アカウントの受信フォルダ以下に作成した指定のフォルダ内を走査します。

共有メールボックスメールを取得する

  • 指定のメールアドレスの取得はmailman変数に格納し、.Session.CreateRecipientにてセットする
  • 共有メールボックスからのメール取得では、.Session.GetSharedDefaultFolderを使って取得します。
  • .Session.GetSharedDefaultFolderの第二引数では、olFolderInboxを指定すると受信トレイになります。こちらにいろいろなフォルダの指定用の値が一覧で表示されています。ただし、olFolderSentMailなどの送信済みトレイなどの指定が出来ません
  • 日付時刻を見て現在取得済みメールの最大値より大きい値(つまり未来のメール)だけを取得して追加するようにしてあります。DMaxで最大値を取得し、DateDiffの秒での比較で0よりプラスの値がそれになります。
  • 各メールのItemのプロパティは様々なものが用意されています。こちらのページのPropertiesがそれに該当します。
  • 稀にSenderに値のないものがあってコードが止まるケースの為に、on error resume nextを入れてあります。
  • こちらの手法は共有メールボックスも扱えますが、取得の速度がかなり遅いので時間を要します。

共有メールボックスで送信済みトレイデータを取得する

前項の方法では、受信トレイデータは取れても、例えば送信済みトレイを取る事ができません。.GetSharedDefaultFolderの引数にolFolderSentMailを指定することが出来ないためです。そこで、一旦受信トレイを取得し、その親に移動、そこから送信済みトレイと辿る事で取得することが可能になります。

  • 前回のコードに加えて、objOutlook.GetNamespace(“MAPI”)にてNamespaceを取得しておく
  • 取得したNamespaceに対して、olNS.GetSharedDefaultFolder(acc,olFolderInbox).Parent.Folders(“送信済みアイテム”)にて受信トレイ⇒親フォルダ⇒送信済みアイテムと辿ってフォルダを取得させる
  • あとはこれまでと同じようにループで一個ずつ取り出す。
  • この方法だと、どこかにぶら下がっているわけではないフォルダの中身も正確に取り出すことが可能です。

共有メールボックスにてメールを新規送信

通常のメールボックスでももちろんメールを送信できますが、共有メールボックスアカウントを使ってのメール送信はやや面倒です。別途メール作成用のフォームなどは用意しておく必要性があります。

  • CreateItem(0)にて新規のメール作成になります。
  • toやccは、複数宛先がある場合、旧式のカンマを入れてしまった場合に備えて、セミコロンにリプレースしています。
  • 送信するアカウントはデフォルトだと自分のアカウントを使ってしまうので、SentOnBehalfOfNameを使って送信アカウントを指定します。

図:新規メール作成用のサンプルフォームの事例

共有メールボックスにてメールを返信する

返信の場合は新規作成よりもさらにちょっと複雑です。あまりネットにも資料がないのですが、基本はreplyメソッドを使ってメールを作ることになります。

  • 予め、メールの受信用テーブルに受信時に対象のメールの「entryID」を取得しておく必要があります。
  • 共有メールボックスの受信トレイを取得しておく必要があるため、Session.GetSharedDefaultFolderで取得させておきます。
  • GetItemFromIDとentryIDにて返信対象となるメールを取得しておきます。それにたいして、Replyを行う
  • 今回はHTMLメールを送るために、返信元メール本文やフォーム上の内容の改行コードを<br>に置換させています。
  • あとは通常の送信と同じように組み立ててsendするだけ

図:自分が作った返信用フォーム

作っておいた署名を取得する

OutlookメールをVBAで送る場合、署名が自動では入りません。Outlookに自分で設定済みの署名を取得し、メール本文に付け足したいケースはままあると思います。この署名ですが

  1. Outlookの新規メール作成⇒挿入⇒署名(▼クリック)⇒署名とすると出てくる
  2. 複数の署名を作っておくことが可能です。
  3. 作った署名は署名の名前.txtという形で、各ユーザのC:Usersユーザ名AppDataRoamingMicrosoftSignaturesのディレクトリに、署名名で作ったtxtという形でファイルとして保存されています。

これをVBAで取るならば事前に、Windows Script Host Object Modelを参照設定に入れて於いてユーザ名を自動取得させ、所定のディレクトリにアクセスさせましょう。

  • ログインユーザ名を取得し、signnameに署名の名前を入れておく。
  • パスを構築して、fsoにてtxtファイルの中身を取得し、返すだけの関数です

図:署名ファイルはtxtファイルで保存されている

関連リンク

共有してみる: