メールの本文の指定された行の文字列をもとに Excel で VLookup を実行し、見つかった値をヘッダーに追記して印刷するマクロ


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


初めまして。Outlook VBA初心者です。

以下のようなことは可能でしょうか。

①メール本文内のn行目のキーワードを取得
②キーワードを検索値として指定のエクセルファイルからVLOOKUPで情報を取得
③VLOOKUPで取得した情報をメールのヘッダーに設定し、印刷

エクセルのVBAは少し経験があるのですが、outlookはまだまだ勉強中でなかなか作業が進まず困っております。こういった操作が可能かどうかだけでもご教示いただければ幸いです。


可能です。
順を追って説明しましょう。

まず、①についてですが、メールの本文を含む一般的なテキストにおける「行」とは CRLF (改行コード) で終わる一連の文字列を指します。
そのため、本文の文字列を CRLF で分割し、分割された文字列の n 番目の文字列が n 行目、ということになります。
VBA では Split という関数を使って文字列を分割できます。

次に、②についてですが、Excel のファイルから VLookup で情報を取得するには、Excel の Application オブジェクトの VLookup を使用します。
(厳密にいうと Application の WorksheetFunction プロパティの VLookup メソッドなのですが、WorksheetFunction は省略できるようです。)
ここで、VLookup を使う際には VLookup( Value, “A1:B2”, 2 ) というようにしたくなるのですが、VLookup の 2 番目の引数は範囲指定のオブジェクトを指定する必要があるため、Worksheet オブジェクトの Range プロパティで取得します。

最後に、③についてですが、メールのヘッダーに設定して印刷をするには、メールのユーザー定義プロパティとして取得した情報を追加する必要があります。
ユーザー定義プロパティの追加は MailItemUserProperties プロパティの Add メソッドを使用し、取得した UserProperty オブジェクトの Value プロパティに値を設定します。
なお、同じメールに 2 回実行すると、Add メソッドが失敗するため、Add の前に Find メソッドで既存のプロパティがあるか確認し、存在する場合はそれを再利用するようにしています。

上記の処理をマクロで実装すると以下のようになります。

' ここをトリプルクリックでマクロ全体を選択できます。

Public Sub PrintWithValueByVLookup()
     ' 読み込む Excel ファイルを指定
     Const EXCEL_FILE = "c:\temp\table.xlsx"
     ' VLookup で検索するシートの番号を指定
     Const VLOOKUP_SHEET = 1
     ' VLookup で検索する範囲を指定
     Const VLOOKUP_RANGE = "A2:B10"
     ' VLookup で値を返す列番号を指定
     Const VLOOKUP_VALUE = 2
     ' メールでキーワードを取得する行数を指定
     Const LOOKUP_LINE = 5
     ' 印刷する際に Excel で取得した値の表題を指定
     Const VALUE_NAME = "ExcelValue"
     '
     Dim objItem As MailItem
     Dim strKey As String
     ' アクティブなウィンドウのアイテムを取得
     If TypeName(ActiveWindow) = "Inspector" Then
         Set objItem = ActiveInspector.CurrentItem
     Else
         Set objItem = ActiveExplorer.Selection(1)
     End If
     ' 指定行からキーワードを取得
     strKey = GetLineByNumber(objItem.Body, LOOKUP_LINE)
     ' 値が取得できたら検索
     If strKey <> "" Then
         Dim excBook As Object
         Dim rgLookup As Object
         Dim varValue As Variant
         ' Excel ファイルを取得
         Set excBook = GetObject(EXCEL_FILE)
         ' VLookup の検索範囲を取得
         Set rgLookup = excBook.Worksheets(VLOOKUP_SHEET).Range(VLOOKUP_RANGE)
         ' VLookup を実行
         varValue = excBook.Application.VLookup(strKey, rgLookup, VLOOKUP_VALUE)
         ' 値が取得できたら処理
         If varValue <> "" And Not varValue Like "エラー*" Then
             Dim usrProp As UserProperty
             ' 取得した値をユーザー定義フィールドに設定
             Set usrProp = objItem.UserProperties.Find(VALUE_NAME)
             If usrProp Is Nothing Then
                 Set usrProp = objItem.UserProperties.Add(VALUE_NAME, olText)
             End If
             usrProp.Value = varValue
             objItem.Save
             ' メールを印刷
             objItem.PrintOut
         Else
             MsgBox "VLookup の検索でエラーが発生しました。"
         End If
     Else
         MsgBox "メッセージにキーワードを見つけられませんでした。"
     End If
End Sub
' 指定された行番号の行を取得
Private Function GetLineByNumber(strBody As String, iLine As Integer)
     Dim arrLines As Variant
     ' 改行コード (CRLF) で本文を分割
     arrLines = Split(strBody, vbCrLf)
     If UBound(arrLines) >= iLine - 1 Then
         ' 指定された行番号の行を返す
         GetLineByNumber = arrLines(iLine - 1)
     Else
         GetLineByNumber = ""
     End If
End Function

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

広告

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中