Excelにメアドや別シートへのセル指定リンクを相対パスで設定：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、HyperlinksのEmailSubjectプロパティを使い、リンクにメールアドレスを設定しメールの件名を指定する方法と、リンクで任意のシートにジャンプする方法について。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprize（64ビット）＋Excel 2013

　今回も、Tips「Excelのセルにハイパーリンクを設定し、一括削除する」に引き続きハイパーリンク関連のTipsを紹介する。今回はメールアドレスの管理と、任意のセルにジャンプさせるTipsだ。

ハイパーリンクにメールアドレスを設定してメールの件名を指定

　まずは、ハイパーリンクにメールアドレスを設定してメールの件名を指定するTipsを紹介しよう。

　図1では、Tips「Excelのセルにハイパーリンクを設定し、一括削除する」で紹介した、Webサイトを管理するのと同じようなUIと処理を用いている。

図1　「得意先名」「メールアドレス」「OK」ボタンが表示されたシートと「得意先メールアドレス」と表示された「得意先メールアドレス」シートが用意されている

　「得意先名」「メールアドレス」を入力するセルを用意し、「角丸四角形」を配置して「OK」ボタンとしている。

　ハイパーリンクにメールアドレスを設定する書式は下記の通りだ。Tips「Excelのセルにハイパーリンクを設定し、一括削除する」の「HyperlinksオブジェクトのAddメソッドの書式」と同じ書式だが、Addressに指定する内容が異なるので、再掲載しておく。

HyperlinksオブジェクトのAddメソッドの書式

Hyperlinks.Add Anchor:=｛Anchor｝,Address:=｛Address｝,TextToDisplay:=｛TextToDisplay｝

　｛Anchor｝にはハイパーリンクのアンカーを指定するが、今回はセル（Rangeオブジェクト）やShapeオブジェクトを指定することになる。必須項目だ。｛Address｝にはメールアドレスの先頭に「mailto:」を付けて指定する。これも必須項目だ。

　｛TextToDisplay｝には、ハイパーリンクで表示されるテキストを指定する。これは、省略可能だ。

　今回の場合は、登録したメールアドレスをクリックするとメーラーが起動し、自動的にメールの「件名」を表示するようにしている。メールに「件名」を表示する書式は下記だ。

HyperlinksオブジェクトのEmailSubjectプロパティの書式

Hyperlinks.EmailSubject

　これら2つの書式を活用するのが、下記リスト1のコードだ。

Option Explicit
Sub 得意先メールアドレス()
  If Range("C2").Value = "" Or Range("C3").Value = "" Then
    MsgBox "「得意先名」と「メールアドレス]を入力してください。"
    Exit Sub
  Else
    Dim lastRow As Long
    With Worksheets("得意先メールアドレス")
      lastRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      .Cells(lastRow, 1).Hyperlinks.Add Anchor:=.Cells(lastRow, 1), _
                                    Address:="mailto:" & Range("C3").Value, _
                                    TextToDisplay:=Range("C2").Value
      .Cells(lastRow, 1).Hyperlinks(1).EmailSubject = "ご依頼の件について"
    End With
  End If
  Worksheets("得意先メールアドレス").Select
End Sub

リスト1　複数の「コメント」内を検索するコード

　Tips「Excelのセルにハイパーリンクを設定し、一括削除する」とほとんど同じなので、異なる部分のみ解説する。

　｛Anchor｝には「C3」セルに入力された値の先頭に「mailto:」を連結して指定する。｛TextToDisplay｝には「C2」のセルに入力された値を指定している。

　VBE（Visual Basic Editor）を起動してModule1内にリスト1のコードを記述し、このマクロを「OK」ボタンに関連付ける。

　これで、「得意先名」「メールアドレス」を入力して「OK」ボタンをクリックすると、「得意先メールアドレス」シートに、「得意先名」に「メールアドレス」が関連付けされて順次登録されていく。

　「OK」ボタンをクリックすると、図2のように表示される

図2　得意先名」をクリックすると「ご依頼の件について」という「件名」が付いてメーラーが起動した

　さらに、登録されたメールアドレスをクリックすると、サブジェクトに「ご依頼の件について」という「件名」が付いてメーラーが起動する。

ハイパーリンクで任意のシートにジャンプ

　次は、「ハイパーリンクで任意のセルにジャンプするTipsを紹介しよう。

　「シート表示」というシートを作成し、図3のように「シート名表示」ボタンを配置しておく。

図3　「シート名表示」ボタンと「シート名」が追加表示されるセルが用意されている

　このボタンをクリックすると、「1月売上」から「12月売上」の項目名に、「1月売上」から「12月売上」までのシートの「A1」のセルにハイパーリンクが張られて表示される。このリンクをクリックすると、各指定したシートにジャンプする。月別売り上げ管理などに利用すると便利なTipsだと思う。

　書式は下記のようになる。

HyperlinksオブジェクトのAddメソッドの書式（引数にSubAddress付き）

Hyperlinks.Add Anchor:=｛Anchor｝,Address:=｛Address｝,SubAddress:=｛SubAddress｝,TextToDisplay:=｛TextToDisplay｝

　｛Anchor｝にはハイパーリンクのアンカーを指定する。セル（Rangeオブジェクト）やShapeオブジェクトを指定する。必須項目だ。｛Address｝には今回は何も指定しない。何も指定しないからといって省略することはできない。必須項目だからだ。

　｛SubAddress｝にはハイパーリンクのサブアドレスを指定する。これは、省略可能だ。TextToDisplayには、ハイパーリンクで表示されるテキストを指定する。これも、省略可能だ。

　この書式を活用し、図3の「シート名表示」ボタンをクリックし、表示される「シート名」をクリックして任意のシートのA1のセルにジャンプするコードはリスト2になる。

Sub 任意のシートにジャンプ()
  Dim lastRow As Long
  Dim i As Integer
  For i = 1 To 12
    With ActiveSheet
      lastRow = .Cells(Rows.Count, 4).End(xlUp).Row + 1
      .Cells(lastRow, 4).Hyperlinks.Add Anchor:=.Cells(lastRow, 4), _
                                    Address:="", _
                                    SubAddress:=i & "月売上!A1", _
                                    TextToDisplay:=i & "月売上"
    End With
  Next
End Sub

リスト2　シート名の一覧を表示して、任意のシートにジャンプするコード

　まずは、Long型の変数lastRowとInteger型変数iを宣言する。

　「1月売上」から「12月売上」のシート名を表示させるために、変数iを12回繰り返す。繰り返し処理の間で下記の処理を行う。

　記事「Excelの基本オブジェクト「セル」の参照、移動、コピー、選択、挿入、削除」でも解説した、終端セルを参照するEndプロパティを使用して、アクティブシートの4列目（D列）の最終行から上方向の終端セル（「D列」の最終行）を選択し、データが入っている場合は、次の行にデータが追加されるようにする。

　Hyperlinks.Addメソッドを使って、「D列」のセルに順次データが追加されるよう、｛Anchor｝に「.Cells(lastRow,4)」と指定する。こうすることで、変数lastRow行の4列目のセル（D列）から順次データが追加されていく。

　｛Address｝には何も指定しないでおく。｛SubAddress｝には、12回繰り返す変数iの値に「月売上!A1」という文字列を指定して、表示された「サイト名」がクリックされたとき、任意のサイト名の「A1」のセルにジャンプするように指定する。例えば「2月売上!A1」は「2月売上」シートの「A1」にジャンプする意味を表す。

　｛TextToDisplay｝には、12回繰り返す変数iの値に「月売上」という文字列を連結して指定する。

　これで、「シート名表示」ボタンがクリックされると、アクティブセルのD列に「1月売上」から「12月売上」の文字列にジャンプ先のハイパーリンクが設定されて表示される。

　このマクロを「シート名表示」ボタンに関連付け、実行すると図4のように表示される。

図4　表示されたシート名をクリックして、任意のシートのA1セルにジャンプした

まとめ

　今回は、お得意先のメールアドレスをハイパーリンクで管理するTipsを最初に紹介した。このように、ちょっと工夫するだけで、いちいちExcelのメニューから、ハイパーリンクを設定してメールの設定を行うより、数倍手間が省けるのではないだろうか。

　また後半では、シート名を一気に表示させて、任意のシートにジャンプするTipsを紹介した。わずか、これだけのマクロを記述するだけで、日常の作業の手間は大いに省けるのではないだろうか。

　ぜひ、今回のTipsも各自の職場でアレンジして使ってもらえるとうれしい。

著者プロフィール

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev（Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。