メールにハイパーリンク&添付ファイルを用意する
ExcelVBAからOutlook経由でメールを送信したいという質問をいただきました。
続けて会話を進めるとさらに具体的な要件を引き出すことができました。やりたい事は以下3点の様です。
- Excelに入力された宛先を使ってメールを用意したい
- メールの本文にはハイパーリンクを付けたい
- メールにファイルを添付したい
最終的には1～3の作業を1つのまとまりにして1クリックで仕事が終わるようにしたいという事でした。
もちろんExcelVBAを使えば作業を自動化することができます。
ExcelVBAを使えばさらに業務を効率化させることができますよ。
複数の宛先へのメールを用意する中で宛先ごとに異なるファイルを添付させることもできます。
複数の宛先ごとに対応を変えられるのであれば改善効果は大きそうですね。
では今回のExcelVBA×Outlookの記事は2つに分けることにしましょう。基礎編と応用編で2つの記事を用意します。
- 基礎編：冒頭で紹介した「1～3の内容をVBAで再現する」方法を紹介
- 応用編：「複数の宛先へのメールを用意する中で宛先ごとに異なるファイルを添付させる」方法を紹介
本記事を基礎編、次回の記事を応用編として情報を用意していきます。
2つの記事を使ってOutlookに関連する業務を効率化させていきましょう。すぐに事例を確認したい方はこちら。
関連記事
応用編の記事はこちらです。より実務に近い事例を使ってマクロを紹介しています。
今回質問いただいた方の様にやりたい事が決まっている時はこちらの記事をご覧ください。
結論から考える【逆引き】やりたい事から記事を選択できるようにしました
関連書籍
ExcelVBAからOutlookを操作してメールを用意するという行為は書籍でも紹介されています。
ポチップ
こちらは電子書籍です。デュアルモニター（デュアルディスプレイ）で作業している方におすすめです。
モニターで書籍を見ながらコードを書くことで効率よく勉強することができます。
Outlookについて
Outlookについて簡単に説明しておきます。
Outlookはメールを送受信して管理、閲覧するというアプリですが他にも予定表の要素を持ち合わせています。
私のおすすめはMicrosoft365の環境でOutlookをほかのアプリと一緒に使うケースです。
特にTeamsとの連携は抜群でWebミーティングの開催や連絡、予定管理が非常に楽になり業務効率が上がります。
Outlookは個人情報の管理ができるソフトウェア
Wikipediaからの引用文をご覧ください。「Outlook＝メール」だけではないという事が分かります。
Microsoft Outlook（マイクロソフト・アウトルック）は、Windows版のMicrosoft Officeの一部として含まれている個人情報管理（Personal Information Manager）ソフトウェアである。
電子メール機能の他、予定表・連絡先管理・仕事管理・メモ・To Do リストなどの機能が実装されている。
Wikipedia：Microsoft Outlookより抜粋
続いてMicrosoftのドキュメントです。
1分程度でOutlookをかんたんに理解することができる動画が用意されています。リンクを用意しておきます。
Microsoftサポート Outlookとは
本記事では以降Outlookのメール機能に特化して解説を進めます。
OutlookにもVBAが用意されている
ExcelVBAと同じようにOutlookにもVBAが用意されています。
基本的にはExcelVBA同様に対象のオブジェクトを指定して関連するプロパティやメソッドを操作することになります。
よってExcelVBAとOutlookVBAの基本的な構造は同じです。ExcelVBAユーザーであれば学習コストを削減できますよ。
ExcelVBAが使えるとOutlookVBAも使えるのか
ExcelVBAと同じ考え方でコードを書く事ができるという事はOutlookVBAもすぐ使えるのではないかと考えてしまいます。
この件について結論を申し上げますと「少し勉強が必要」です。
理由はOutlookで使うオブジェクトや関連プロパティ、メソッドをある程度把握していないとコードが書けない為です。
この件について具体的にイメージしてもらう為に1つ事例を用意してみます。
ExcelとOutlook の操作画面を思い浮かべてみてください。似ているところはありますが基本的には別物ですよね。
という事は扱う対象（オブジェクト）も違うという事になります。
できること
Outlook内の作業＋αを自動化できます。ここでは＋αの例として転送に関する事例を1つ紹介します。
まずは転送の基本的な機能の確認です。転送ボタンをクリックすると「本文有り、宛先無し」でメールが用意されますね。
機能としてはこれで十分なのですが私は以前「本文無し、宛先有り」の転送ボタンが欲しいというオーダーを受けました。
通常の転送とは取捨選択する要素が逆になります。本文を削除して宛先を残すという状態を作ってほしいという依頼です。
この様な処理に対応したアイコンは用意されてないのでオーダーに応える為に機能（ボタン）を自作しました。
レアケースですがこんなこともできるという事例でした。
基本編：3つのやりたいことをVBAで再現する
ここから実際の作業に移ります。まずは作業内容の確認です。以下3つを実現させます。
- Excelに入力された宛先を使ってメールを用意したい
- メールの本文にはハイパーリンクを付けたい
- メールにファイルを添付したい
アウトプット
最終的には以下のようなメールを作成しOutlookの下書きフォルダに格納しつつディスプレイにメールを表示させます。
作業環境（フォルダやワークブック、シートなど）
作業環境を明確にしておきます。今回はExcelVBAでOutlookをコントロールします。よってOutlookVBAは使いません。
続いて具体的に用意するもの（フォルダ、ファイルなど）を紹介しておきます。今回は大きく分けて５つです。
- 作業用のフォルダ
- 実行用のワークブック（.xlsm）
- 添付ファイル用のワークブック（.xlsx）
- メールのテンプレート（.oft）
- Outlookの下書きフォルダ
用意した要素を使って画像の赤矢印の順番でメールを用意します。
それぞれの内容を順番に説明していきます。
1_作業用のフォルダ
まずは作業をまとめる為に自分のPCのデスクトップにフォルダを1つ用意します。
Outlookの下書きフォルダ以外の要素（画像内②～④）は全てデスクトップ上のフォルダにまとめることにします。
2_実行用のワークブック（.xlsm）
1で用意したフォルダの中にマクロ実行用の.xlsmファイルを1つ用意します。セルには画像の様に情報を用意します。
このワークブックに用意された情報とテンプレートを使ってメールを作成することになります。
表示されているワークシートは「関連情報」というシート名です。画像に無いのでここで説明させていただきました。
| セル番地 | 項目 | 内容 |
|---|---|---|
| B3 | 件名 | メールの件名 |
| B5 | 宛先（名前） | メールの宛先 |
| B6 | 宛先（メルアド） | セルB5のメールアドレス |
| B7 | CC（名前） | メールのCC |
| B8 | CC（メルアド） | セルB7のメールアドレス |
| B10 | ハイパーリンク（表示） | ハイパーリンクに表示される文字 |
| B11 | ハイパーリンク（リンク） | ハイパーリンクとして埋め込むURL |
3_添付ファイル用のワークブック（.xlsx）
添付ファイル用の.xlsxファイルを1つ用意します。添付ファイルには画像を1枚貼り付けておきました。
4_メールのテンプレート（.oft）
テンプレートの中身です。署名のような文字を用意しました。名前とURL（ハイパーリンク付き）が用意されています。
テンプレートの拡張子は.oft
メールのテンプレートの拡張子は.oftです。ご注意ください。
メール作成時にウインドウのファイルタブから名前を付けて保存を選択することでテンプレートとして保存できます。
5_Outlookの下書きフォルダ
メール作成後の格納場所として下書きフォルダを採用します。
メールを作成と同時に送信することも出来ますが一旦確認したいところです。そのために下書きフォルダに格納します。
6_その他
下書きフォルダにアクセスしてメールを見るのは面倒なので作成したメールは画面に表示させることにします。
コード
コードを紹介します。
Sub Outlook_事前バインディング()
Dim Olk As Outlook.Application
Dim Mail As Mailitem
Dim Amt As Attachments
Dim wb As Workbook
Dim ws As Worksheet
Dim a As String
Dim b As String
Dim c As String
Set wb = Workbooks("実行.xlsm")
Set ws = wb.Worksheets("関連情報")
Set Olk = New Outlook.Application
Set Mail = Olk.CreateItemFromTemplate(ThisWorkbook.Path & "\テンプレート.oft")
Set Amt = Mail.Attachments
With Mail
.BodyFormat = 2 '形式
.Subject = ws.Cells(3, 3) '件名
.To = ws.Cells(6, 3) '宛先
.CC = ws.Cells(8, 3) 'CC
a = .HTMLBody 'テンプレートの内容を格納
b = "<span style=""font-size:11pt"";""font-family:游ゴシック"">" '書式設定
'文字とハイパーリンク
c = "このURLは<a href=""& ws.Cells(11, 3) &"">" & ws.Cells(10, 3) & "</a>へのリンクです</span><br>"
'a、b、cを合わせる
.HTMLBody = b & c & a
End With
Amt.Add ThisWorkbook.Path & "\添付.xlsx"
Mail.Display
Mail.Save
Set Olk = Nothing
End Subポイント
実行環境と用意するもの（フォルダ名、ファイル名など）を再現していただければコピペで使えるコードになっています。
まずはこのまま使っていただき使用感を確認ください。その後状況に応じてカスタマイズという流れをおすすめします。
解説
依頼者様がやりたい事を中心に解説していきます。
- Excelに入力された宛先を使ってメールを用意したい
- メールの本文にはハイパーリンクを付けたい
- メールにファイルを添付したい
この3つを中心に解説を進めていきますがまずは参照設定の説明をしておきます。
0_参照設定
デフォルトの機能だけでOutlookを動かす事も可能ですがよりかんたんにコーディングする為に機能を拡張させます。
やることは1つです。必要なライブラリを選択します。（参考：ライブラリ）
ライブラリに用意された各ファイルを選択する為の具体的な方法として参照設定を行います。
VBE上部の「ツール」タブ内の「参照設定」をクリックしましょう。画像のようなダイアログボックスが表示されます。
その中から「Microsoft Outlook 16.0 Object Library」を選択してOKボタンをクリック。
ポイント1_ライブラリを簡単に探す方法
今回指定する「Microsoft Outlook 16.0 Object Library」はダイアログボックス内ではかなり後方に位置しております。
ひとつずつ見て探すのは非常に大変です。できるだけ楽をしたいのでここでは簡単に探す方法を紹介します。
参照可能なライブラリに表示されている項目を適当に1つ選択します。その後「M」をクリックしてください。
すると「M」から始まるライブラリの先頭までジャンプしてくれます。
これは検索対象のライブラリの頭文字を入力するとその文字から始まるライブラリの先頭までジャンプするという仕様です。
そこからは自力でライブラリを探すことになるのですが冒頭から探すよりは簡単です。是非トライしてください。
ポイント2_なぜ参照設定をするとコーディングが楽になるのか
少し前に「よりかんたんにコーディングする為に参照設定をする」と書きました。この件について理由を説明します。
参照設定をしておくとコーディング時に「自動メンバー表示」機能を使うことができる為です。
画像の赤枠の様にコーディング時に次に続く要素を表示してくれる機能を「自動メンバー表示」と言います。
参照設定をしておくとライブラリで選択した要素が抱えるオブジェクト、プロパティ、メソッドも表示される様になります。
具体的に確認してみましょう。Withステートメント内で.（ドット）を記入すると後に続く要素が表示されていますね。
これは自動メンバー表示が有効になっているという事が言えます。もう少し具体的に説明します。
Withステートメントは変数Mailで構成されておりOutlook用に用意されたMailItemオブジェクトが格納されています。
MailItemオブジェクトはExcelVBAには常備されていないので参照設定がないと自動メンバー表示が機能しません。
しかし今回は参照設定によりVBAが事前にオブジェクトを認識してくれます。これで自動メンバー表示が有効になります。
よってコーディング時には後に続く要素が表示されることになり思考面、作業面共に大幅に楽をすることができます。
お知らせ
この様に参照設定でライブラリを選択することを「事前バインディング」と呼ぶこともあります。
「コーディング時は事前バインディングで・・・」と言われたら先に参照設定するんだなと思って準備してください。
1_Excelに入力された宛先を使ってメールを用意したい
コード内のWithステートメントの前段に用意されたコードが対象です。この中から2～4番目のコードをご覧ください。
- Subject ・・・ メールの件名
- To ・・・ メールの宛先
- CC ・・・ メールのCC
宛先を含めたメールの前段で用意するための要素に対して情報を入力する為のコードが用意されています。
実行.xlsmで用意された情報が代入できる様にコードを準備しました。
2_メールの本文にはハイパーリンクを付けたい
メールの本文にハイパーリンクを用意するためには本文をHTML形式で用意しましょう。
HTMLがわからない方はこちらの記事をご覧ください。HTMLについて説明しています。
HTMLについて少しだけ解説VBA×Selenium×ChromeでWebスクレイピング｜nameタグを使う
なぜHTML形式で文字を用意すると扱いやすいのか
HTML形式であれば今回の様に文字列の一部をハイパーリンクで用意するなどより柔軟な対応をすることができます。
箇条書きや文字色の変更、フォントの変更時など複雑な仕様もHTMLを使って指示を出すことができます。
総合的に考えると色々なことができるのでHTML形式で文字を用意することをおすすめしています。
HTML形式を指定する方法
Withステートメント内の先頭行をご覧ください。
.BodyFormat = 2 と書かれています。これがHTML形式でメールを用意するよというコードです。
参考ですが1はテキスト形式、3はリッチテキスト形式です。詳細はリンク先のドキュメントをご覧ください。
具体的にコードを用意する
最終的に以下のようなコードを用意することができるとOutlookの本文の中にハイパーリンクを用意することができます。
<span style= "font-size:11pt";"font-family:游ゴシック">
このURLは<a href="https://www.slt-pgming-21.net">EnjoyExcel</a>へのリンクです
</span>
<br>作成した文章をWeb上で表示してみます。（具体的な方法は割愛します）
私はChromeで表示させてみました。画像内の紫色になっているところがハイパーリンクになっています。
綺麗に表示されているので文字列の並びとしてはこれで問題ないという事になります。
サンプルとして説明に使用した.htmlファイルを用意しておきます。コードが気になる方はダウンロードしてみてください。
ダウンロードしたデータをChromeなどのブラウザで実行すると画像のような状態を再現できます。
代わってダウンロードしたデータをメモ帳などのテキスト形式で開くとHTMLのコードを見ることができます。
HTMLをVBAの中で用意するとコードの書き方が変わります
この状態をVBAで再現すると以下のようなコードになります。（コードのセクションで紹介したものと同じです）
b = "<span style=""font-size:11pt"";""font-family:游ゴシック"">"
c = "このURLは<a href=""& ws.Cells(11, 3) &"">" & ws.Cells(10, 3) & "</a>へのリンクです</span><br>"可読性を上げるために2つの文章に分けて変数に格納したのですがHTMLだけで書かれたコードよりはるかに複雑ですよね。
ダブルクォーテーションやアンパサンド（＆）だらけで非常に分かりにくいことでしょう。
しかしVBAでコードを用意するためには全て必要な要素であり1つ1つの文字に意味があります。
ダブルクォーテーションやアンパサンドの使い方は慣れが必要です。わからない人はこちの記事をご覧ください。
文字列を理解するVBAで文字列を比較Like演算子とワイルドカードで完全／部分一致を確認【一覧で表示】
コードを深堀りする
上で紹介した2つのコードは同じことが書いてあります。ここではどのように形が変わっていったのかを順に見ていきます。
VBEに用意されたVBAのコードから順番に変化させて最後にHTMLだけのコードにしていきますので過程をご覧ください。
ポイントはダブルクォーテーションとアンパサンドの外し方です。では始めて行きます。
まずはVBEに書かれたコードをご覧ください。
b = "<span style=""font-size:11pt"";""font-family:游ゴシック"">"
c = "このURLは<a href=""& ws.Cells(11, 3) &"">" & ws.Cells(10, 3) & "</a>へのリンクです</span><br>"ここから変数bの後方、cの前方にあるダブルクォーテーションを外して2つのコードをつなぎます。
コードを2つに分けていた理由は「bはフォントなどの文字全体の情報」、「ｃは文字列」という分け方をしたかった為です。
あくまで可読性を向上させるための表示上の問題です。気にならない人は最初から1行でコードを書いてください。
"<span style=""font-size:11pt"";""font-family:游ゴシック"">このURLは<a href=""& ws.Cells(11, 3) &"">" & ws.Cells(10, 3) & "</a>へのリンクです</span><br>"ここから各所に配置されているダブルクォーテーションを1つずつ消してインデント（字下げ）を付けます。
ダブルクォーテーションが2重になっていたところ以外はダブルクォーテーションが消えた状態になりました。
<span style="font-size:11pt";"font-family:游ゴシック">
このURLは<a href=" & ws.Cells(11, 3) &"> & ws.Cells(10, 3) & </a>へのリンクです
</span>
<br>最後にセル参照になっている場所をワークシートに用意された実際の文字列に置き換えます。
これでHTMLだけのコードに変換することができました。
<span style="font-size:11pt";"font-family:游ゴシック">
このURLは<a href="https://www.slt-pgming-21.net">EnjoyExcel</a>へのリンクです
</span>
<br>VBAとして機能させるために形を変えていますが最終的にはこの情報をもとにOutlookのメールを書いています。
hrefタグについて
ハイパーリンクに関わるタグだけ少し説明しておきます。hrefタグはハイパーリンクを用意することができるタグです。
続いて使い方をご覧ください。
<a href="リンク先のURL">表示させたい文字</a>リンク先のURLに空白があるとエラーになるのでダブルクォーテーションを使っています。
URLに空白が無い時はダブルクォーテーションは不要ですが基本はダブルクォーテーション付きでURLを用意して下さい。
3_メールにファイルを添付したい
最後はファイルの添付方法です。AttachmentsオブジェクトのAddメソッドで添付ファイルを用意します。
VBEのオブジェクトブラウザーをご覧ください。
パラメータはSourceが必須となります。コード内ではThisWorkbookオブジェクトのPathプロパティを使っています。
こうしておけば実行用のデータと同じ階層に添付ファイルを置くことで都度パスを書かなくても良くなります。
パスの設定方法が分からないという方はこちらの記事をご覧ください。
ファイルパス完全解説【VBA】絶対パス相対パスの書き方｜理解はカレントディレクトリがポイント
まとめ
ExcelVBAを使ってOutlookでメール（メッセージ）を用意する方法を紹介しました。
アプリケーション間の作業だという事とほかの言語を使っているという事で少し難しく感じたかもしれません。
しかしそれぞれの要素はそんなに難しい事はなくゆっくり見ていけば十分理解できる内容です。
一度読んだだけで理解するのは難しいかもしれません。何度か読んで自分でコードを用意することで理解してください。