メールの内容を Excel ファイルにかき出すマクロ


コメントにて以下のようなご要望をいただきました。


いつもお世話になってます。
早速ですが、以下の件についてお教え願えれば幸いです。

使用しているアウトルックのバージョン
Outlook2010(仕事場)、自宅Outlook2013

●メールで調査などの依頼があった場合、備忘録として
①差出人と②タイトル、③メール本文をエクセルに書き出して
行程管理をしたいのですが、そういったことを可能にするマクロはネットで探したのですが、
これといったものがありませんのでよろしくお願いいたします。

※いくつかは似たようなものがありましたが、初心者なのでよく理解できず、加工できませんでした。
  namespaceが何故必要なのか、dim宣言が何故必要かもできてません。

【具体例】
具体的には調査依頼のメールを開いてその開いているメールに対してマクロを使い
既存のエクセルファイル「調査管理.xlsx」のデータが入力されている次の行に
①送信者、②メールタイトル、③メール本文を書き出したいのです。

○書き出し先のエクセル表のイメージ 調査管理.xlsx

  A B C
1 ①差出人 ②メールタイトル ③本文
2 abc@yaho.co.jp 忘年会参加者調べ 忘年会の参加者の名前を教えて下さい。
3 def@yaho.co.jp 新年会参加者調べ 新年会の参加者の最年長の人の名前を教えて下さい。
4      

◆ 受け取った新たな調査依頼メールのイメージ
①差出人 xyz@yaho.co.jp
②メールタイトル 【依頼】冬休みの出勤者について
③メール本文 冬休みに出勤される方の、氏名を教えて下さい。
このメールを開いた状態で、マクロを使い、調査管理.xlsxを開きすでに記入されている末尾の行の
次にこのメールのデータを自動的に入れたいというものです。

具体的には「調査管理.xlsx」の
(1) A4セルに xyz@yaho.co.jp
(2) B4セルに 【依頼】冬休みの出勤者について
(3) C4セルに 冬休みに出勤される方の、氏名を教えて下さい。
を自動で記入できるマクロです。


Outlook の NameSpace オブジェクトは、通常は Application.Session として Outlook のさまざまな処理で使用します。
詳細はこちらの記事を参照してみてください。
また、Dim は変数を宣言するもので、オブジェクト型の変数を宣言しておけばプロパティやメソッドが自動補完できるようになります。
実際のところ Dim で宣言しなくても変数は使えますが、宣言しておいた方が便利です。

ご要望のマクロは以下のようになります。

' ここをトリプルクリックでマクロ全体を選択できます。
Public Sub ExportToExcel()
    ' EXCEL ファイルをフルパスで指定します
    Const EXCEL_FILE = "c:\temp\調査管理.xlsx"
    Dim objItem As MailItem
    Dim objBook 'As Excel.Workbook
    Dim objSheet 'As Excel.Worksheet
    Dim r As Integer
    ' メールをどのように開いているか確認
    If TypeName(Application.ActiveWindow) = "Inspector" Then
        Set objItem = ActiveInspector.CurrentItem
    Else
        Set objItem = ActiveExplorer.Selection(1)
    End If
    ' Excel ファイルを開く
    Set objBook = GetObject(EXCEL_FILE)
    objBook.Windows(1).Activate
    Set objSheet = objBook.Sheets(1)
    ' データがない行まで移動
    r = 2
    While objSheet.Cells(r, 1) <> ""
        r = r + 1
    Wend
    ' メールの情報を Excel ファイルに追記
    With objSheet
        .Cells(r, 1) = objItem.SenderEmailAddress
        .Cells(r, 2) = objItem.Subject
        .Cells(r, 3) = objItem.Body
    End With
    ' Excel ファイルを閉じる
    objBook.Close True
End Sub

マクロの登録方法やメニューへの追加について

メールの内容を Excel ファイルにかき出すマクロ」への13件のフィードバック

  1. ご親切に回答いただき、大変ありがとうございます。
    その後、気づいたので申し訳ないのですが、追加質問させていただきます。
    実は、メールを何時受信したということも記録したいのですが、
    .Cells(r, 3) = objItem.Body の後に.Cells(r, 4) = objItem.ReceivedTimeを挿入すれば良いのでしょうか。

  2. 小出しに追加質問で申し訳無いですが、人から調査を頼まれる場合もありますが、
    自分(仮にE五郎とします)から複数の人に調査を頼んで、合わせてCCで自分にもメールを送ったとき、

    頼んだ相手の人の名前を書きだそう思い
    Cells(r, 5) = objItem.ReceivedByName

    とマクロに加えたところ、
    .Cells(r, 1) = objItem.SenderEmailAddress では、頼んだ人の名前(送信元) である「E五郎」は書きだされますが、

    送信メールの送信先(TOやCC欄に表示されている送信した相手先、受信者)例えばTO欄に「A太郎、B二郎」CC欄に「C三郎、D四郎、E五郎」で送り先があるとき
    最後の送信者かつ受信者「E五郎」だけしか、エクセルファイルに書き出せませんでした。

    (1) TOで送付している「A太郎、B二郎」だけ書き出したり、
    (2) 「A太郎、B二郎、C三郎、D四郎、E五郎」 5人受信者全員を書き出すにはどうしたら良いのでしょうか?

    • 宛先はobjItem.To、CcはobjItem.Ccで取得することができます。
      宛先とCCをまとめて一つのセルで出力するなら、.Cells(r,5) = objItem.To & “;” & objItem.Cc とします。

  3. 質問なのですが指定フォルダ(下書きフォルダ)にあるすべてのメールを上記のようにデータ化はできないのでしょうか?いろいろやってみましたがわかりません
    可能であればデータ取得後にmgsファイルをファイルネームの頭に連番を付けたうえでcドライブ下のフォルダに入れたいです
    よろしくお願いします

  4. このマクロを試してみましたが、上手くいきました!
    ありがとうございました。

    一つお伺いしてもよろしいでしょうか?
    他のところですでに説明があれば申し訳ございませんが、
    最初の変数の宣言でなぜ、(’)をつけるのでしょうか? 

    Dim objBook ‘As Excel.Workbook
    Dim objSheet ‘As Excel.Worksheet

    • Excelのオブジェクトを参照設定している場合はExcel.WorkbookやExcel.Worksheetが使用できますが、既定ではこれらをAsで指定するとエラーとなるためです。

  5. はじめまして。
    横からの質問で申し訳ありません。
    どうしても自分では解決できずなんとかお力をお借りしたいと思います。

    メールの本文中、

    【 ご予定日 】 12月
    【 日 】 31日
    【 泊数 】 1泊
    【 名前 】 山田 太郎
    【 郵便番号 】 4562215
    【 ご住所 】 愛知県豊明市西町5丁目111-111
    【 マンション名等 】豊明マンション101
    【 Email 】 taroyamada@yahoo.co.jp
    【 tel1 】 0902200000
    【 ご予約人数 】 2人
    【 小学生以下人数 】 1人

    のように項目ごとのフォーム送信がある場合、エクセルの2行目以降のセルに

    (A1)   (B1) …
    ご予定日 日  泊数  名前      郵便番号 …
    (A2)   (B2) …
    12    31  1   山田 太郎  4562215 … 

    のように①、メール本文内の項目の後の文字列を抽出し、エクセルの対象項目に対して個別にエクスポートすることは可能なのでしょうか?
    またその折②、日にち、泊数などは数字のみ抽出できればうれしいです。
    outlookのエクスポート機能はwordの差し込みフィールドのように使えて便利そうなのですが2003以降のバージョンには対応していないようですし、本文中の項目までは当然読み込めませんのでなんとかマクロで解決できればと思っております。
    ただ、マクロはネットで引っ張りながらさわるぐらいしかできません。
    こういった投稿、コメントに不慣れで甚だ不躾ではございますが是非ご教示頂ければ幸いです。
    宜しくお願い申し上げます。

  6. 追記です。
    出来れば既存のエクセルデータsheet内のセルに挿入できればと考えています。

  7. はじめまして。
    マクロ初心者です。
    やりたいことがほぼ同じなので、
    こちらのマクロをベースにさせていただいてます。
    一点質問なのですが、書き出し先のエクセルにパスワードがかかっている場合どうしたらいいのでしょうか。

本文から取得したデータを項目別に Excel のシートに書き出すマクロ | Outlook 研究所 への返信 コメントをキャンセル