2021/02/07

Excelで作った日報をPDFでそのままメール送信




1.背景

日報とは「日々の業務の上司への報告」で、上司と部下のコミュニケーション手段であると同時に、部下にとっては指示に対する達成度を客観的に測ったり、出来なかった原因を考えたりする良い機会であり、また相手に理解してもらえる書き方を練習する場でもあると思います。
今回は日報の内容やフォーマットは横に置いておいて、完成した日報を上司等に送付する部分について紹介をします。

Excelを使って日報を作っている方も多くいると思います。それをメールで上司等に送付する場合は、一般的に図1-1の上側のように「一時的にファイルを自分のPCに保存」し、「Outlookなどのメールソフトを開いて添付ファイルに指定」してから送信します。
もちろん一時ファイルを作成しない方法として、「ファイル→共有→電子メール」という手順でExcelファイルやPDFファイルを直接メール送信することも出来ます。
日報送付の手順
図1-1

今回は図1-1の下側のように、ワークシートのPDF化からメール送信までをマクロを使って自動実行することで、極端な言い方をすればダイアログを表示させボタンをクリックすれば「日報の送信が完了」するものです。

なお、今回はPDFでのみ送信としました。これは部下の側からすれば「日報用のブックに日別にシートを作成」し「最新の日付の日報を上司に送付すれば充分」であり、かつ書換可能な状態(Excelブック状態)よりも書換不可能なPDF状態で送付する方が、正しい情報として上に伝えられると考え、PDFのみにしました。もちろん技術的にはExcelブックを添付ファイルとして送信することも可能です。

2.システム概要

本システムは、日報作成用Excelファイル内にマクロ登録して使用しても良いですし、またPCのExcelにアドイン登録しそこから起動することも可能です。アドイン登録をすると、マクロ無しの様々なExcelファイルでもメール送信ができることになります。
アドイン登録した場合は、図2-1のようにユーザーメニューの1つ①から起動することになります。(一番下のサンプルファイルには、日報のシートの上方に起動ボタンをつけてあります。)
ダイアログ表示させ、アクティブなシート面のPDFを送信
図2-1

起動すると②の送信ダイアログが表示されます。メールには宛先・件名・本文・添付ファイルなどが必要ですが、添付ファイルは現在アクティブになっている日報をPDFファイルにしたものになります。その他については、前回送信内容がそのまま保存されていますので、常に同じ内容(添付する日報内容だけが異なる)であれば、そのまま「PDF送信④」ボタンをクリックします。

ここで、PDFファイルにするセル範囲をユーザー側で決めることが可能です。印刷画面の「選択をした部分を印刷」と同様に「PDFにしたい範囲をセル選択」した後に「PDF送信④」ボタンをクリックすれば、選択した範囲のみがPDFになります。範囲選択しなければ(=シート上のどこかの1セルのみを選択した場合)シート上で印刷できる範囲を全てPDFにします(印刷画面の「作業中のシートを印刷」相当)。

なお、宛先の変更については図2-3、宛先内容の編集については図2-4で行います。また件名・本文については、③のTextBox内を修正するだけで内容保存されます(修正後、キャンセルをクリックしても保存されてしまいます)。

「PDF送信ボタン④」をクリックすると、いくつか確認のためのメッセージボックスが表示された後、PDFファイルを作成⑤し、②の送信ダイアログが消えれば送信が完了したことを示しています。
自動マクロでPDFファイルが作成され、自動送信される
図2-2

なお今回システムは、Excelから直接メールを送っている訳では無く、Outlookにデータを渡してOutlookからメールを送信処理をしています。ですので「本当に送信されたか(=ネットワークが繋がっているか、Outlookの設定が正しいか)」「相手に届いたか(=相手のアドレスが間違っていないか)」などの情報はExcel側からは分からず、(調べた限りでは)Outlook側を確認するしか方法がありません。
ですので「正常に日報が送れたか」については、Outlookを確認することが必要です。

メールの送信先は、②の送信ダイアログ上では「表示名の羅列として表示」されています。送信先を変更したい場合には「送信先の枠内⑥をクリック」します。
送信先選択ダイアログ
図2-3

するとクリックした場所(To、CC、BCC)の「送信先選択ダイアログ⑦」が現れます(例:CCの枠をクリックすれば、CCの選択ダイアログが表示。選択ダイアログの上部に「送り先(CC)」と表示されている。)ので、送信に必要な人をクリックして選択状態にさせます。複数人の選択が可能で、選択状態の行を再度クリックすると選択が解除されます。
選択が終了したら「OK」ボタンをクリックし、確定させます。「キャンセル」ボタンをクリックすると、元の選択のままとなります。

宛先内容を編集する場合は、②の送信ダイアログの「送信先編集」をクリックします。すると編集ダイアログ⑨が現れます。
送信先編集ダイアログ
図2-4

宛先リスト⑩には、左列に表示名(氏名等)、右列にメールアドレスが表示されます。表示された状態は現在保存されているリストです。

宛先を追加する場合は、⑪のTextBoxに「表示名」と「メールアドレス」を入力し「追加ボタン」をクリックします。リスト⑩のどれかを選択している状態で追加をすると、その選択項目の一つ上に追加されます。リスト⑩を1つも選択していない状態で追加をすると、一番下に追加されます。
なおリスト⑩の項目選択はシングルクリックで選択状態になり、リスト⑩内のどこかをダブルクリックすると解除(=どれも選択されていない状態)されます。リスト⑩は複数選択は出来ません。

リスト⑩のどれかを選択状態にし、⑪のTextBoxで修正を加えたのち「修正ボタン」をクリックすると、選択項目が書き換えられます。
またリスト⑩のどれかを選択状態にし「削除ボタン」をクリックすると、選択項目が削除されます。復元はできません。

宛先リストの編集が完了したら、「戻る」ボタンをクリックし、②の送信ダイアログに戻ります。

3.プログラムの流れ

プログラムを起動させると、図3-1の送信ダイアログが立ち上がります。
送信ダイアログの表示の流れ
図3-1

送信ダイアログにはメールを送信するために必要な「送信先(TO・CC・BCC)」「件名」「本文」の枠があります。また、マクロが書かれているファイル(アドインファイル、または日報作成ファイル)内の1つのシートを「データ保存用シート」としてあり、そこに「件名」「本文」および「アドレス一覧」+「送信フラグ」のセットを保存しています。

ダイアログの起動・表示時に、「データ保存用シート」から「TO・CC・BCC」別に「送信フラグ」の付いている「氏名」を拾い上げ、複数あるならばそれらを結合してダイアログの送信先に表示します。また「件名」「本文」についても「データ保存用シート」のデータをダイアログ上に表示します。

なおダイアログ表示後、「件名」「本文」を書き換えた場合はBeforeUpdateイベントを利用して「データ保存用シート」のデータを書き換えています。

送信ダイアログの「PDF送信」ボタンをクリックすると、図3-2のように処理が行われてメールが送信されます。
PDFを作成し送信する流れ
図3-2

まず最初に「PDFにする範囲を特定」します。これは、ユーザーが「シート上の一部をPDFにしたい」のか「シート上の(印刷可能範囲の)全てをPDFにしたい」のかを、「印刷範囲としてセル範囲を指定している(=複数セルを選択)」のか「印刷範囲を指定していない(=1セルのみを選択)」のかで判断しています。

次に「PDFのファイル名」を決めます。今回は作ったPDFファイルを「自分のPCのTEMPフォルダー」に一時保存することにしました。ですので新たに作るファイル名「メール件名+今日の日付+PDF拡張子」がTEMPフォルダーに存在しないことを確認します。もし存在する場合にはファイル名の後端に「カッコ付きの番号」を追加していき、存在しないファイル名を決定します。

続いてシート上の日報を「決定したファイル名」でPDF化し、TEMPフォルダーに保存します。
次にメールの各項目を揃えていきます。送信先は、データ保存用シートの送信フラグを確認しながら「メールアドレス」を「;(セミコロン)」で繋いでセットします。また「件名」「本文」もデータ保存用シートから読み取ってセットします。
最後に、TEMPフォルダーに一時保存したPDFファイルを添付ファイルにすればメールが完成です。

メールが完成したら「メール送信」し、そのあとでTEMPフォルダーに一時保存したPDFファイルを削除して作業完了です。

送信ダイアログ上で送信先を再選定するために「宛先枠内」をクリックすると、図3-3のようにクリックした枠位置を引数にして送信先選択ダイアログ(UserForm2)が開きます。
ダイアログのリストボックスには、データ保存用シートから氏名列のデータを貼り付けます。その上で送信フラグに従ってリストボックスの各行を選択状態にします。
ユーザー側が送信先を選択し直しOKボタンをクリックすると、リストの選択状態を調べてデータ保存用シートの送信フラグリストのデータを書き換えます。
送信先選択と送信先編集の流れ
図3-3

一方、送信ダイアログの「送信先編集」ボタンをクリックすると、図3-3の右端のように送信先編集ダイアログ(UserForm3)が開きます。
リストボックスには、データ保存用シートのデータ(氏名+アドレス)が貼り付けられます。ユーザーが選択した行のデータは、リストボックス下のテキストボックスに値がコピーされます。
(リストが未選択状態の時は、下のテキストボックスは空になります。)
また、リストボックスをダブルクリックすると選択が解除され、テキストボックスの値も初期化されます。

「追加」ボタンをクリックした時は、まずリストボックスの選択状態を確認し、データを挿入する行位置を確定します。選択状態で無い場合はデータの一番下の行位置とします。
次にデータ保存用シートに於いて、上記で確定した挿入の行位置で「行挿入」をし、テキストボックスの値を書き込みます。

「修正」ボタンをクリックした時は、リストボックスの選択状態を確認し修正するデータの行位置を取得し、データ保存用シート側のデータをダイアログのテキストボックスのデータに置き換えます。なおリストが選択状態で無い場合は、修正対象行が特定できないためにメッセージを表示させます。

「削除」ボタンをクリックした時は、リストボックスの選択状態を確認し削除するデータの行位置を取得し、データ保存用シート側のデータ行を行削除します。なおリストが選択状態で無い場合は、削除対象行が特定できないためにメッセージを表示させます。

4.データ保存用シート(Sheet1)

メール添付するPDFファイル(日報)内容は日毎に異なるのは当然ですが、「送付先」「件名」「本文」はほぼ一定だと思うので、プログラム中に「定数(Const)」として保存しておくのも一つの方法だと思います。但し、それらの変更が必要になった場合には「マクロを操作」する必要が出てきますので、動かなくなったり、マクロのパスワードが流出したりと余り良いことにはなりません。
ですので、今回はワークシートにデータを保存する方法にしました。
データ保存用シートの構造
図4-1

図4-1はサンプルファイルのもので、「件名」をD2セル、「本文」をE2セルに保存しています。件名と本文は、今回は選択式にしていませんので、データとしては1セルずつのみです。
また「メールアドレス」はE6セルから下に並べ、そのアドレスを管理するための「氏名」をD6セルから下に並べています。
送付リストは、同じ行のメールアドレスが「TO」「CC」「BCC」のどれに使われているかを表しており、使われているセルに「1」をフラグとして立てています。

5.標準モジュール

標準モジュールには、システム全体(プロジェクトレベル)で使用する変数・定数の宣言と、送信ダイアログを表示させる起動プロシージャを置きます。図5-1は、変数・定数宣言です。
  1. '========== ⇩(1) プロジェクトレベルの変数・定数宣言 ====================
  2. Public AddressSheet As Worksheet
  3. Public Const AddressRow As Long = 6
図5-1

2行目の変数「AddressSheet」は、「データ保存用シート」を代入する入れ物です。図5-2の8行目で、実際の「データ保存用シート」を指定します。
3行目の定数「AddressRow」は、「データ保存用シート」内でのアドレスリストがスタートする行位置を示しています。今回の場合は、図4-1で分かる様に「6行目からアドレスリストを始めている」ので、値「6」を指定しています。

Excelにアドイン登録した場合にはメニューのボタンから、また日報ファイルの中にマクロを保存してある場合はボタンから呼び出す起動プロシージャが図5-2です。
  1. '========== ⇩(2) システムの起動 ====================
  2. Public Sub MailSend()
  3.  If ActiveSheet.Type = xlWorksheet Then
  4.   Set AddressSheet = ThisWorkbook.Sheets("sheet1")
  5.   UserForm1.Show
  6.  Else
  7.   MsgBox "本システムはワークシート上でのみ起動します"
  8.  End If
  9. End Sub
図5-2

7行目は、呼び出しているシートがワークシートか否かを確認しています。通常は「ワークシート以外(例えばグラフシート)でもPDF化する事は可能」で、グラフをPDFファイルにしたものを添付ファイルにしてメールすることも当然可能です。
しかし今回システムでは、選択しているセル数を調べてPDF化する範囲を分岐させたり(図6-4の44~64行目)、PDF化の際のPageSetupオブジェクトのプロパティがワークシート専用だったりと、ワークシートに特化した作りになっているため、ワークシート以外での使い方は対象外とさせていただきました。
もしワークシート以外でもPDF化が必要な場合は、シートのTypeで仕訳けをして、シート型ごとにPDF化をする必要があると思います。また、データ保存用シートの最下行位置を取得するのに「Application.Rows.Count」というコードを使用していますが、これもグラフシート上で実行するとエラーが発生してしまいますので、「AddressSheet.Rows.Count」等と変更する必要があります。

8行目は、プロジェクトレベル変数のAddressSheetに「データ保存用シート」の位置をセットしています。今回は図4-1の通り「Sheet1」にデータを置いていますので、「ThisWorkbook.Sheets("sheet1")」を指定しています。
なお、別な書き方としては「Set AddressSheet = Sheet1」とオブジェクト名としてのSheet1を指定する方法もあります。

9行目は「送信ダイアログ」であるUserForm1をモーダル(ダイアログが表示されている間はシート操作不可)で起動しています。ここでもしモードレスで起動すると、ダイアログが表示されている間でもシート操作が可能になってしまうため、7行目でワークシートの時のみ起動する事にしても、その後シートを移動(例えばグラフシートをアクティブ)してしまうと、PDF化するときにエラーが発生してしまいます。
ですのでモードレスで起動する場合には、「PDF送信」ボタンをクリックする時に「ActiveSheetがワークシート」の時のみに実行を制限するか、または上記で説明した「シートのTypeごとに処理を分ける」処理が必要となります。

12行目は、「ActiveSheetがワークシート」以外だった時のコメント表示です。

6.UserForm1(送信ダイアログ)

6ー1.フォームの作成

起動プロシージャである図5-2の9行目から呼び出されるUserForm1(送信ダイアログ)の構成が図6-1です。
UserForm1のフォーム構成
図6-1

メールの送信先は「TO」「CC」「BCC」の3種類ありますので、Label1~3に表示させています。メールを送信するには「kacho@it.co.jp」のような「メールアドレス」が必要ですが、英数記号であり一目では見分けられないため、通常「氏名」などに対応付けて作業をしています。図6-1の各Labelには、その氏名をメールアドレスの代わりに表示します。
TextBoxでは無くLabelに表示させている理由は、ユーザーに「氏名」を直接編集させないためです。

メールの送付に必要な「件名」と「本文」は、TextBox1とTextBox2に表示させます。今回は、このUserForm1上で件名と本文を編集できるようにするため、TextBoxとしています。

3つのCommandButtonの表面の文字列はマクロ側から操作しています。またその他の説明用Label(例えば「件名」「本文」など)は、Labelコントロールを配置する際にCaptionプロパティを変更しています。

6ー2.フォームモジュール

6ー2ー1.フォーム起動時の設定

フォームが初めて起動される時に発生するイベントがInitializeイベントです。その時に実行されるのが図6-2です。
  1. '========== ⇩(3) 初回起動時の設定(UserForm1) ====================
  2. Private Sub UserForm_Initialize()
  3.  Me.Label1.BorderStyle = fmBorderStyleSingle      'Labelの罫線(枠線)を単線で表示
  4.  Me.Label2.BorderStyle = fmBorderStyleSingle      '同上
  5.  Me.Label3.BorderStyle = fmBorderStyleSingle      '同上
  6.  Me.TextBox2.MultiLine = True               '複数行の入力可
  7.  Me.TextBox2.EnterKeyBehavior = True          'TextBox2内ではEnterキーで改行が可
  8.  Me.CommandButton1.Caption = "PDF送信"
  9.  Me.CommandButton2.Caption = "キャンセル"
  10.  Me.CommandButton3.Caption = "送信先編集"
  11. End Sub
図6-2

18~20行目は、送付先である「TO」「CC」「BCC」の表示先Labelに外枠線を付けています。既定では外枠線が無く「どこをクリックしたら良いのか分からなくなる」のを防ぐためです。

22行目は、本文用のTextBoxを「複数行が入力可能」な状態にしています。件名用のTextBoxの方は単一行で問題無いと考え、本文のみにしました。
23行目は、TextBox内でEnterキーを押すと改行してくれるようにしています。これを設定しない場合(既定)は、Enterキーを押すと次のタブオーダーのコントロールにフォーカスが移ってしまいます。

25~27行目は、3つのボタンの表面文字列を表示させています。コントロール配置時にCaptionプロパティを設定しても構いません。

フォームが表示される時、つまり「初回にフォームが起動される時」も「一旦フォームがHideされた後、再度起動される時」も発生するイベントが、図6-3のActivateイベントです。表示されるたびに処理内容が異なる部分については、ここで実行します。
  1. '========== ⇩(4) フォーム表示時の設定 ====================
  2. Private Sub UserForm_Activate()
  3.  Call reDisp
  4. End Sub
図6-3

31行目で図6-15の「reDispプロシージャ」を呼び出し、送信先のLabel1~3と、件名・本文のTextBox1~2へデータを表示させます。

6ー2ー2.「PDF送信」の処理

送信ダイアログの「PDF送信」ボタンをクリックした時のイベントプロシージャが図6-4です。
  1. '========== ⇩(5) PDF送信の処理 ====================
  2. Private Sub CommandButton1_Click()
  3.  Dim objOutlook As Object     'Outlookオブジェクトの変数
  4.  Dim objMail As Object       '新しいOutlookメールオブジェクトの変数
  5.  Dim Ans As Integer         'MsgBoxの戻り値(OK 又は キャンセル)
  6.  Dim i As Long            'カウンタ変数(送信先の行位置)
  7.  Dim myTO As String         'メール送信時に使用する送信先アドレス(TO)
  8.  Dim myCC As String         'メール送信時に使用する送信先アドレス(CC)
  9.  Dim myBCC As String         'メール送信時に使用する送信先アドレス(BCC)
  10.  Dim PDF_FileName As String     'メールに添付するPDFファイルのパス+ファイル名
  11.  With ActiveSheet
  12.   If Selection.Count = 1 Then
  13.    Ans = MsgBox("シート内を全てPDFにし、送信します", vbOKCancel)
  14.    If Ans = vbCancel Then
  15.     Me.Hide
  16.     Exit Sub
  17.    End If
  18.    .PageSetup.PrintArea = ""
  19.   Else
  20.    Ans = MsgBox("選択範囲のみをPDFにし、送信します", vbOKCancel)
  21.    If Ans = vbCancel Then
  22.     Me.Hide
  23.     Exit Sub
  24.    End If
  25.    .PageSetup.PrintArea = Selection.Address
  26.   End If
  27.  End With
  28.  PDF_FileName = PDF_FileName_Make
  29.  With ActiveSheet
  30.   .PageSetup.Zoom = False
  31.   .PageSetup.FitToPagesWide = 1
  32.   .PageSetup.FitToPagesTall = False
  33.   .PageSetup.PaperSize = xlPaperA4
  34.   .PageSetup.Orientation = xlPortrait
  35.   .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_FileName
  36.  End With
  37.  With AddressSheet
  38.   For i = AddressRow To .Cells(Application.Rows.Count, 4).End(xlUp).Row
  39.    If .Cells(i, 1) = 1 Then myTO = myTO & .Cells(i, 5).Value & ";"
  40.    If .Cells(i, 2) = 1 Then myCC = myCC & .Cells(i, 5).Value & ";"
  41.    If .Cells(i, 3) = 1 Then myBCC = myBCC & .Cells(i, 5).Value & ";"
  42.   Next i
  43.  End With
  44.  Set objOutlook = CreateObject("Outlook.Application")
  45.  Set objMail = objOutlook.CreateItem(0)
  46.  With objMail
  47.   .To = myTO
  48.   .CC = myCC
  49.   .BCC = myBCC
  50.   .Subject = AddressSheet.Cells(2, 4).Value
  51.   .Body = AddressSheet.Cells(2, 5).Value
  52.   .Attachments.Add PDF_FileName
  53.   .Send           ' メール送信
  54.  End With
  55.  Call PDF_Delete(PDF_FileName)
  56.  Set objOutlook = Nothing
  57.  Set objMail = Nothing
  58.  Me.Hide
  59. End Sub
図6-4

図6-4の中では、今回システムのメインであるPDFファイル作成からメール送信までを行っています。少しコード量が多いので、プロシージャ内の流れを図6-5にまとめてみました。ブロックごとに説明をしていきます。
プロシージャ内のPDFファイル作成からメール送信までの流れ
図6-5

6ー2ー2ー1.PDF範囲の確定
44~64行目は、シート上のどこをPDFファイルにするかを決めています。
まず、ユーザーが「PDFにする範囲を部分選択しているか否か」を45行目のIF文で調べています。部分選択を希望する場合は、PDF範囲として「複数セルを選択」しているはずですし、シート全体をPDFにしたいと希望する場合は「どこもセル選択しない=単一のセルを選択している」ことを代理特性としました。

「範囲を選択していない(1セルのみを選択)場合=シート全体をPDFにする場合」は46~52行目を実行しします。
まず46行目で「本当にシート全体で良いのか」をユーザーに確認します。MsgBoxの第二引数に「vbOKCancel」を指定することで、MsgBoxに「OK」「キャンセル」の2つのボタンを表示させることが出来ます。(図6-6を参照下さい)

MsgBoxの第二引数
定数内容
vbOKonly0「OK」ボタンのみ表示(既定値)
vbOKCancel1「OK」「キャンセル」ボタンを表示
vbAbortRetryignore2「中止」「再試行」「無視」ボタンを表示
vbYesNoCancel3「はい」「いいえ」「キャンセル」ボタンを表示
vbYesNo4「はい」「いいえ」ボタンを表示
vbretryCancel5「再試行」「キャンセル」ボタンを表示
図6-6

なお、今回は「OK」「キャンセル」のボタンを使用することにしましたが、「はい・いいえの方が適切だ」と思われる方もいるかもしれません。その際にはボタン種類を変更しても良いですが、その戻り値(図6-7)も変更になりますので合わせて変更して下さい。

MsgBoxの戻り値
定数内容
vbOK1「OK」ボタンが選択された
vbCancel2「キャンセル」ボタンが選択された
vbAbort3「中止」ボタンが選択された
vbRetry4「再試行」ボタンが選択された
vbIgnore5「無視」ボタンが選択された
vbYes6「はい」ボタンが選択された
vbNo7「いいえ」ボタンが選択された
図6-7

46行目でユーザーがクリックしたボタンの種類を変数Ansで受け取り、その種類を48行目で確認します。
ユーザーがクリックしたボタンが「キャンセル」だった場合は49~50行目を実行し、ダイアログを閉じ(Hide)、Clickイベントプロシージャを終了します。また、それ以外(=OKボタンをクリック)の時は、52行目で「印刷範囲を特定しない」設定にしています。
つまり、 シート上で特に範囲指定していない → 印刷範囲を特定しない という設定にします。

一方「範囲を選択している場合=ユーザーが意図してPDFにする範囲を選択している場合」は55~61行目を実行します。
まず、55行目で「本当に範囲選択部分のみで良いのか」をユーザーに確認します。
「ユーザーが意図的に範囲指定しているのだから、再確認は不要」との考え方もありますが、このプログラムでは「2つのセルを選択していても、範囲選択と判断」しています。つまり、手が滑って複数個のセルを選択した状態に気づかずに今回システムを起動してしまうと、思わぬ日報を送付してしまう事になります。
ですので、親切すぎるかもしれませんが55行目で再確認をしています。

55行目のMsgBoxに対してキャンセルボタンをクリックした(=PDF選択範囲が違っていた)際には58~59行目が実行され、ダイアログを閉じ(Hide)てプロシージャが終了します。
それ以外(=PDF選択範囲を了承)の場合は、61行目で「選択している範囲をPDF印刷範囲にする」設定をします。なお、PrintAreaには「セル範囲を文字列として設定」する必要があるため、「.Address」を使用しています。

6ー2ー2ー2.PDFファイル名の決定
66行目ではPDFファイル名を取得し、変数「PDF_FileName」に代入しています。PDFファイル名は、図6-13の「PDF_FileName_Make」関数プロシージャで作成します。ちなみに、今回システムではファイル名は「メールの件名+送信年月日」にしています。
添付するPDFファイル名は「送付した日が分かる」必要は無いですし、「誰から送付したか分かる」必要もありません。メールを受けた側(上司)でのメール管理アプリ(Outlook等)でメール送信者・受信日などで確認できるからです。
しかし上司が添付ファイルのみを保存し、業務や査定に役立てることは良くある事だと思います。その時に添付ファイルのファイル名が皆同じだったら、ファイルを保存する際に一つ一つに名前を付けなければならなくなります。これを避けるために、今回は「件名(≒送信者名)+送信年月日」としています。

6ー2ー2ー3.印刷レイアウト等の設定・PDFファイルの作成
68~75行目は、印刷で言うと「ページ設定」(図6-8)とPDFファイルの「印刷実行」の部分になります。ページ設定の内、印刷範囲については44~64行目で設定済みです。
PDFのページ設定
図6-8

まず69行目の「.PageSetup.Zoom = False」は、図6-8の②に相当します。Zoom(拡大/縮小)をOff(False)にすることで、その下の「次のページ数に合わせて印刷」の方をOnにしています。

70行目の「.PageSetup.FitToPagesWide = 1」は、その「次のページ数に合わせて印刷」の「横」の値を1に設定③することで、「幅をページに揃える」ことができます。
また71行目の「.PageSetup.FitToPagesTall = False」は、「次のページ数に合わせて印刷」の「縦」の値を空④にし、縦長の日報の場合は複数頁とする設定にしています。

ただしシート上の記入部分が小さい時には、この方法では「幅をページに揃える」ことが出来ません(右側に大きく余白が出来る形になります)。これは FitToPagesWideプロパティでは、拡大/縮小の100%を超えてページに合わせることが出来ないためです。
このような場合は、拡大/縮小の%値を変更させながら横幅一杯に調整するとか、用紙をA4→A5にする(実際の印刷の時に注意が必要)などの方法も考えられますが、日報のフォームをA4よりも大きくなるように作ってからユーザーに渡す方法が実用的かと思います。

72行目は、用紙サイズをA4に設定⑤しています。また73行目は印刷の向きを縦(xlPortrait)に設定①します。なお印刷の向きを横にする場合は、Orientationプロパティに「xlLandscape」を指定します。

印刷の設定については、最低限と思われるプロパティに絞りました。人によって余白やヘッダ・フッタなどを調整している場合には、その設定を引きずったままPDFファイルが作られますので、確認が必要になるかもしれません。
またこのシステムでPDFファイルを作ると、その印刷設定がPCに残ります。そのあとで通常の印刷処理を行うと思った通りの印刷が出来ない可能性もありますので注意が必要です。

74行目は、上記で調整した印刷設定で、PDF出力をしています。パラメータは全部で9個ありますが、その内必須のTypeに「xlTypePDF」を指定することでPDFファイルが発行されます。またパラメータFilenameには、保存するファイルの「フルパス+ファイル名」を文字列で指定します。
この74行目を実行する際に、図2-2の「発行中」のダイアログが表示されます。

6ー2ー2ー4.送付先の作成
76~82行目は、データ保存用シート(図5-2の8行目でセットしたAddressSheet)のメールアドレスリストから、送信に必要なアドレスを選択して送信先の変数(TO、CC、BCC)に代入しています。

メールアドレスリストは、図5-1の3行目で宣言した通り「AddressRow行 = 6行目」からスタートしています。また最下行は、シートの一番下の行から「Endキー + ↑」で選択されるセルとするため「AddressSheet.Cells(Application.Rows.Count, 4).End(xlUp).Row」という式を使用しました。
別な方法として、タイトル行(今回の場合は5行目)から「Endキー + ↓」を使うことも考えられますが、もし「アドレスリストに1つもデータが無かった場合」には、シートの最下行を示してしまうことになるので残念ながら使えません。ですので、今回システムでは最下行から上に検索する方法を使います。

77行目のForでカウンタ変数iをスタート行から最終行まで移動させながら、送付リスト(TO、CC、BCC)が1か否かを調べています。もし1だった場合は「送付対象になっている」ので、5列目(E列)のメールアドレスを拾い上げます。
複数のメールアドレスを設定する場合もあるため、メールアドレスの間には「;(セミコロン)」を入れてつなげていきます。なお、連なったメールアドレスの一番最後に「;」が残ってしまいますが、試したところ「;」が残っていても問題無く送付されましたので、最後の「;」を削除することは、今回省略しました。もしシステムによってエラーが出る場合には「最後の1文字を削除」するコードを追加して下さい。

この処理により送信先「TO」「CC」「BCC」のアドレスは、変数「myTO」「myCC」「myBCC」に代入されている事になります。

6ー2ー2ー5.メール組立・メール送信
84行目ではOutlookオブジェクトを生成し、85行目ではそのCreateItemメソッドで「新しいメール」オブジェクトを作成し、変数objMailとします。なお、CreateItemメソッドの引数は図6-9のようになります。
CreateItemメソッドの引数
定数内容
olMailItem0MailItemオブジェクト(メール)
olAppointmentItem1AppointmentItemオブジェクト(予定表)
olContactItem2ContactItemオブジェクト(連絡先)
olTaskItem3TaskItemオブジェクト(タスク/仕事)
olJournalItem4JournalItemオブジェクト(履歴)
olNoteItem5NoteItemオブジェクト(メモ)
olPostItem6PostItemオブジェクト(投稿)
olDistributionListItem7DistListItemオブジェクト(配布リスト)
図6-9

85行目で「.CreateItem(0)」と引数に「0」を与えていますが、これは図6-9の「MailItemオブジェクト」の値0のことです。
事前バインディング(Microsoft Outlook XX.X Object Libraryを参照設定)している場合には、定数「olMailItem」で指示できるのですが、今回は実行時バインディングでOutlookオブジェクトを生成していますので、定数は使えず「値」で指示します。

MailItemオブジェクトには多くのメソッド/プロパティがありますが、その一部を図6-10に示します。
MailItemのメソッド/プロパティの一部
メソッド/プロパティ内容
To送信先の設定
CCCC送信先の設定
BCCBCC送信先の設定
Subject件名の設定
Importance重要度の設定
Attachments.Add添付ファイルの設定
Body本文の設定
Sendメールの送信
Displayメール送信ウィンドウの表示
図6-10

85行目で作成した新規メール(objMail)に対して、88~94行目でメールを組み立てていきます。
まず88~90行目は、76~82行目で作成した送信先(変数myTO、myCC、myBCC)をプロパティのTo、CC、BCCに代入します。
91行目では件名(Subject)プロパティにデータ保管用シートの件名部分(Cells(2,4)セル)からデータを代入し、92行目では本文(Body)プロパティにデータ保管用シートの本文部分(Cells(2,5)セル)からデータを代入ています。

そして94行目で、添付ファイル(Attachments)を追加(Add)します。追加する添付ファイルは、74行目で一時保管したPDFファイルです。
以上のプロパティは最低限の内容だと思いますが、他に設定が必要なものがあったら追加をして下さい。

メールが完成したら、96行目でメール送信(Send)します。
ここで「Send」メソッドの代わりに「Display」メソッドを使用すると、図6-11のように「Outlookのメール送信ウィンドウ」が表示され、添付ファイルを含めた内容の再確認ができます。またウィンドウの右上×印のクリックで送信を中止することも可能です。
メール送信ウィンドウの表示
図6-11

手間を1つ増やしてチェック工程を作るか、作業スピード・簡略化を優先するかは、ユーザーのExcel習熟度などで判断して下さい。
なお前述した通りメールアドレスが間違っていてもメールは発信できてしまいますので、あとでOutlookなどでエラーが帰ってきていないことの確認は必要と思います。この確認は、「Display」メソッドを使ったとしても必要です。

6ー2ー2ー6.PDFファイルの削除他
メールが送信されたら、99行目で一時保存したPDFファイルを削除します。削除プロシージャ「PDF_Delete」は図6-14で詳細説明しますが、引数に削除するファイル名「PDF_FileName」を指定します。このファイル名は66行目でファイル名作成し、74行目で実際に作成したファイルです。

添付ファイルを削除したら、送信ダイアログを104行目で閉じ(Hide)ます。

6ー2ー3.その他のボタンの処理

送信ダイアログ(UserForm1)には「PDF送信」ボタンの他に、「キャンセル」ボタンと「送信先編集」ボタンがあります。そのボタンをクリックした時に動作するイベントプロシージャが図6-12です。
  1. '========== ⇩(6) キャンセルボタンをクリックした時 ====================
  2. Private Sub CommandButton2_Click()
  3.  Me.Hide
  4. End Sub
  5. '========== ⇩(7) 送信先編集ボタンをクリックした時 ====================
  6. Private Sub CommandButton3_Click()
  7.  UserForm3.Show
  8.  Call reDisp
  9. ECall reDispCall reDisp
図6-12

「キャンセル」ボタンをクリックした時には、109行目の「Me.Hide」が実行され、送信ダイアログ(UserForm1)が閉じます。

また「送信先編集」ボタンをクリックした時には、114行目の「UserForm3.Show」が実行され、送信先編集ダイアログ(UserForm3)がモーダルで表示されます。
なお、送信ダイアログ(UserForm1)がモーダルで開いています(図6-2の9行目)ので、その子供である送信先編集ダイアログはモードレスでは開けません。何重にもダイアログを開く場合には注意が必要です。

115行目で呼び出している「reDisp」は、送信ダイアログの「送信先」「件名」「本文」を上書きするプロシージャです。
送信先の編集をしているだけなので不必要に見えますが、編集機能の中に「アドレス削除」がありますので、削除したアドレスが「送信する」ことになっていた場合、送信ダイアログの送信先と保存データが一致しなくなるのを防ぐためです。

6ー2ー4.PDFファイルの名前作成

図6-4の66行目から呼び出される「PDFファイルの名前を決める」プロシージャが、図6-13です。
  1. '========== ⇩(8) PDFファイルの名前作成 ====================
  2. Private Function PDF_FileName_Make() As String
  3.  Dim PDF_Title As String          ' 拡張子を除いたファイル名
  4.  Dim PDF_FileName As String       ' フルパス、拡張子を含めたファイル名
  5.  Dim i As Long                ' カウンタ変数(同名ファイルの数量)
  6.  PDF_Title = AddressSheet.Cells(2, 4).Value & Format(Date, "yyyymmdd")
  7.  PDF_FileName = Environ("TEMP") & " ¥ " & PDF_Title & ".pdf"
  8.  Do Until Dir(PDF_FileName) = ""
  9.   i = i + 1
  10.   PDF_FileName = Environ("TEMP") & " ¥ " & PDF_Title & "(" & i & ").pdf"
  11.   DoEvents: DoEvents
  12.  Loop
  13.  PDF_FileName_Make = PDF_FileName
  14. End Function
図6-13

123行目は、拡張子を除いたファイル名のベースを作っています。今回は日報を受け取った上司が、PDFファイルを管理し易いように「件名(送信者の名前が入っている可能性大)」+「送信年月日」をファイル名とすることを考えました。
氏名がメールの件名に入っていない場合には、送信するPCのユーザー名(取得方法は「アンケートの回数と集計方法 固有情報の取得」を参照下さい)などを取得し、ファイル名とする方法もあります。
(ベースのファイル名を使う側で改造し易いように、変数PDF_Titleを独立させておきました。)

今回、件名については「データ保存用シート(AddressSheet)」の件名が保存されているセル(Cells(2,4))から取得し、送信年月日については「今日の日付」を書式「yyyymmdd」で文字列として取得しています。例えば2021年2月3日であるならば「20210203」となります。
この「件名」+「送信年月日」を文字列として、変数PDF_Titleに代入します。

124行目は、123行目で作成したファイル名に「フルパス」と「拡張子」を結合しています。
まずPDFファイルを「一時的にどこに保管しておくか」を考えます。PCには様々なフォルダーがありますが、どこでも自由にファイルの書込み・削除が出来るというものではありません。
今回は「一時的」という言葉通り「TEMPフォルダー」を使用することにしました。TEMPフォルダーであれば、他のアプリでも一時的に良く使っているはずですので、書込み・削除が可能だと思います。

但しO/SやバージョンによってTEMPフォルダーの位置は変わる可能性があります。そこで、どのPCでも設定してある「環境変数TEMP」を用い、関数「Environ」を使ってTEMPフォルダーのフルパスを得ることにします。

124行目は、関数「Environ」の引数に「環境変数のTEMP」を指定することで、TEMPフォルダーのフルパスを得ています。関数「Environ」で得られるパスは、最後が「・・・¥Temp」などとフォルダー名になっていますので、ファイル名(PDF_Title)と繋ぐためには「¥」印を付けておく必要があります。
ファイル名の後ろには、拡張子である「.pdf」を結合(ドットも忘れない様に)し、変数PDF_FileNameに代入します。

126~130行目のDo~Loopは、脱出条件が「Until Dir(PDF_FileName) = ""」となっています。Dir関数は引数に指定したファイル名が存在しない時には長さゼロの文字列("")が戻ってきますので、この脱出条件は「変数PDF_FileNameのファイル名が存在しなくなるまで」となります。
脱出条件が126行目のDo側に付いていますので、124行目で作ったPDF_FileNameが実際に存在した場合のみ127~129行目を実行します。存在しなかった場合には実行されずにDo~Loopを抜けます。

127行目でカウンタ変数iを1つ増やした後、128行目でPDF_Titleの後ろにカッコ付きで数字を増やしていきます。
例えば124行目のPDF_FileNameが「C:¥・・・¥Temp¥日報_佐藤20210203.pdf」だった場合、128行目を1回目に迎えた時には「C:¥・・・¥Temp¥日報_佐藤20210203(1).pdf」となり、これが新たにPDF_FileNameに代入されます。
130行目のLoopにより再び126行目のDo が実行され、脱出条件「Until Dir(PDF_FileName) = ""」が計算されます。

ここで、新たな(カッコ数字付きの)PDF_FileNameが実在しなければ、Do~Loopを抜けます。もし、まだ実在するようであれば再度127~129行目を実行し、PDF_FileNameは「C:¥・・・¥Temp¥日報_佐藤20210203(2).pdf」となって再々度脱出条件「Until Dir(PDF_FileName) = ""」を計算していく事になります。

Do~Loopを抜けた時には、PDF_FileNameはダブリの無いファイル名となっています。132行目では、このファイル名をPDF_FileName_Make関数プロシージャの戻り値にしています。

6ー2ー5.PDFファイルの削除

図6-4の99行目から呼び出されるのが、図6-14のPDF_Deleteプロシージャです。引数として削除するファイル名を受け取ります。
  1. '========== ⇩(9) PDFファイルの削除 ====================
  2. Private Sub PDF_Delete(PDF_FileName As String)
  3.  Dim Fso As Object      'FileSystemObjectオブジェクト
  4.  Set Fso = CreateObject("Scripting.FileSystemObject")
  5.   Fso.DeleteFile (PDF_FileName)
  6.  Set Fso = Nothing
  7. End Sub
図6-14

138行目は、FileSystemObjectオブジェクトを生成しています。FileSystemObjectオブジェクトには多くのメソッド・プロパティがありますが、今回はその中の「DeleteFile」メソッドを使用して、引数として受け取ったPDF_FileNameを削除します。

6ー2ー6.ダイアログへの送付先・件名・本文の表示

図6-3の31行目、図6-12の115行目、及び図6-16の167・173・179行目から呼び出されるreDispプロシージャが図6-15です。
送信ダイアログの「送信先」「件名」「本文」を表示するプロシージャですが、起動時だけで無く送信ダイアログから送信先選択変更を行ったり、送信先編集を行ったりした際には、表示データが変わっている可能性がありますので、再表示が必要になります。
  1. '========== ⇩(10) ダイアログへの送付先・件名・本文の表示 ====================
  2. Private Sub reDisp()
  3.  Dim i As Long     'カウンタ変数(送信先の行位置)
  4.  Me.Label1.Caption = ""
  5.  Me.Label2.Caption = ""
  6.  Me.Label3.Caption = ""
  7.  With AddressSheet
  8.   For i = AddressRow To .Cells(Application.Rows.Count, 4).End(xlUp).Row
  9.    If .Cells(i, 1) = 1 Then Me.Label1.Caption = Me.Label1.Caption & .Cells(i, 4).Value & ";"
  10.    If .Cells(i, 2) = 1 Then Me.Label2.Caption = Me.Label2.Caption & .Cells(i, 4).Value & ";"
  11.    If .Cells(i, 3) = 1 Then Me.Label3.Caption = Me.Label3.Caption & .Cells(i, 4).Value & ";"
  12.   Next i
  13.   Me.TextBox1.Value = .Cells(2, 4).Value
  14.   Me.TextBox2.Value = .Cells(2, 5).Value
  15.  End With
  16. End Sub
図6-15

まず147~149行目は、送信先(TO、CC、BCC)のLabelを初期化します。
その上で151~156行目で、データ保存用シート(AddressSheet)の送付先リストに「1」がついている行の「氏名」列データを拾っていきます。対象となる氏名は複数の場合がありますので、氏名同士は「;(セミコロン)」で繋いでいきます。

形としては図6-4の76~82行目とほぼ同じ構造(扱うデータが、メールアドレスか氏名かの違いはあります)なので比較して見て頂きたいのですが、図6-15では「myTO」などの変数を使わずに直接Labelの値を扱っています。
処理速度としては図6-4の変数を使った方が有利と思いますが、扱うデータ数が少ないので、あまり差は無いと思います。自分が理解し易い方式を使って下さい。

158行目は件名を送信ダイアログのTextBoxに貼付け、159行目は本文を貼り付けています。

6ー2ー7.送付先枠をクリックした時の処理

送信先(TO、CC、BCC)の枠をクリックした時には「送信先選択ダイアログ」を開くようになっています。送信先Label1~3 をまとめ、WithEventsキーワードを使って1つのイベントプロシージャで扱うことも可能ですが、今回は3箇所のみですので同じようなプロシージャを3つ並べました。
  1. '========== ⇩(11) 送信先(TO)枠をクリックした時 ====================
  2. Private Sub Label1_Click()
  3.  Call UserForm2.List_Start(1)
  4.  Call reDisp
  5. End Sub
  6. '========== ⇩(12) 送信先(CC)枠をクリックした時 ====================
  7. Private Sub Label2_Click()
  8.  Call UserForm2.List_Start(2)
  9.  Call reDisp
  10. End Sub
  11. '========== ⇩(13) 送信先(BCC)枠をクリックした時 ====================
  12. Private Sub Label3_Click()
  13.  Call UserForm2.List_Start(3)
  14.  Call reDisp
  15. End Sub
図6-16

まず送信先選択ダイアログを起動させる方法ですが、今回はLabelをクリックすることで起動するようにしましたが、その他の手段として「ダブルクリック」による起動も検討しました。
しかし試してみると不具合が出ました。ダブルクリックした位置が、起動した「送信先選択ダイアログ(UserForm2)」のリストの位置とちょうど重なってしまう場合があり、起動中のUserForm2のListBoxのClickイベントが反応してしまう(送信先リストを意図せずに変更してしまう)のです。
ダイアログは通常、画面中央に表示されますので、UserForm1とUserForm2が重なる形で表示されます。親のダイアログをダブルクリックして子のダイアログを開く時には、充分注意が必要そうです。

ということで、今回はLabelを「クリック」することで送信先選択ダイアログを開きます。
代表してLabel1で説明していきますが、166行目でUserForm2内のList_Startプロシージャを呼び出します。
List_Startプロシージャには引数として数値を渡しています。数値には「TOは1、CCは2、BCCは3」という意味を持たせており、UserForm2側に「どの送信先枠をクリックしたか」が伝わるようにしています。

ユーザーが送信先選択ダイアログの操作を完了した後、167行目で図6-15の「reDisp」プロシージャを呼び出し、送信ダイアログ(UserForm1)の送信先・件名・本文を再表示します。(送信先選択ダイアログでは件名・本文は変更できないので、167行目の段階では送信先のみの再表示で充分です。しかし、プロシージャをできるだけ共通で使いたいので、reDispを利用しています。)

他のLabelをクリックした時も基本は同じで、UserForm2に渡す引数が異なるだけです。

6ー2ー8.件名・本文を変更した時の処理

送信ダイアログ上の件名・本文のTextBox内で内容を編集した時に、変更されたデータをデータ保存用シートに保存するプロシージャが図6-17です。TextBoxデータが更新される直前(データ変更してTextBoxを抜け出す直前)に発生するBeforeUpdateイベントを利用しています。
  1. '========== ⇩(14) 件名が変更になった時 ====================
  2. Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  3.  AddressSheet.Cells(2, 4).Value = TextBox1.Value
  4. End Sub
  5. '========== ⇩(15) 本文が変更になった時 ====================
  6. Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  7.  AddressSheet.Cells(2, 5).Value = TextBox2.Value
  8. End Sub
図6-17

まず、TextBoxのイベントを図6-18にまとめました。
TextBoxのイベントの発生順序
図6-18

今回、送信ダイアログの件名・本文のTextBoxには、マクロ側から文字列を書き込み、そのあとでユーザーが文字列を操作することになります。図6-18の黒線は文字列を編集しない(そのまま)時で、赤線が編集する時です。
Changeイベントは「1文字変更するたびに発生」してしまいますし、Exitイベントは「文字列を変更しなくても発生」します。今回は文字列全体を変更したあとで処理したいので、BeforeUpdateイベントかAfterUpdateイベントのどちらかを使うことになり、今回の図6-17ではBeforeUpdateイベントを使うことにしました。

件名・本文の文字列を変更したのち別のコントロールに移動(含:3つのボタンコントロール)すると184・189行目を実行し、各TextBoxの値をデータ保存用シートに書き込んでいます。

寄り道
実は、このTextBox変更をUpdateする手順については悩みました。
例えば、テキストボックスを書き換えた後で「しまった、間違えてテキストを修正してしまった」と思い「キャンセルボタン」をクリックすると、修正されずに元のテキストが復活する、というのが通常の感覚なのではないかと考えたからです。

しかし「件名・本文まで修正」して「さあ、送信しょう」とした時に、「しまった、PDFの印刷範囲を間違えた」と思って「キャンセルボタン」をクリックしたら、せっかく修正した件名・本文が元に戻ってしまった、というのもガッカリするだろうなと思った訳です。
で、今回は「日報の件名も本文も、そんなに長文にはならないだろう」との判断から、「書き換え→即保存」という手順にしました。ご了承下さい。

7.UserForm2(送信先選択ダイアログ)

7-1.フォームの作成

送信ダイアログの送信先Labelをクリックすることで呼び出されるUserForm2(送信先選択ダイアログ)の構成が図7-1です。
UserForm2のフォーム構成
図7-1

フォーム上には、送信先候補である全氏名(メールアドレスに対応)を表示するListBoxを置き、その下にOK・キャンセル用のボタンを配置します。

7-2.フォームモジュール

7-2ー1.フォームを起動させる

UserForm2内で使用する変数の宣言と、送信ダイアログ(UserForm1)の図6-16から呼び出されるのが図7-2です。
  1. '========== ⇩(16) フォームレベル変数宣言 ====================
  2. Dim SM As Long      '送信先のレベル(TO、CC、BCC を数値1,2,3で表す)
  3. '========== ⇩(17) フォーム起動プロシージャ ====================
  4. Public Sub List_Start(SendMember As Long)
  5.  SM = SendMember
  6.  Me.Show
  7. End Sub
図7-2

193行目で宣言している変数SMには、送信先レベルが入ります。
送信ダイアログ(UserForm1)の図6-16からUserForm2のList_Startプロシージャを呼び出す際に、送信先のレベル(TO、CC、BCC)を引数として渡してきます。その値は196行目で引数として受け取り、197行目で変数SMに代入しています。

なお送信先のレベルを表す引数は「TO、CC、BCC」と言った文字列では無く、今回は「1,2、3」と数値に置き換えた値を引数にしています。これは「1,2、3」をデータ保存用シートの列位置「A列、B列、C列」として使用するためです。
(もしTOやCCなどの文字列を引数値として使用するのであれば、図7-3の215~222行目でのSelect Caseの分岐内コードはもう少し簡単になる代わりに、229行目は少し面倒になるかもしれません。)

送信先レベルをフォームレベル変数に代入(197行目)した後、198行目で自分(UserForm2)を起動します。

7-2ー2.起動時の処理

UserForm2の起動時に実行されるInitializeイベント、Activateイベントプロシージャが図7-3です。
  1. '========== ⇩(18) 初回起動時 ====================
  2. Private Sub UserForm_Initialize()
  3.  Me.ListBox1.MultiSelect = fmMultiSelectMulti
  4.  Me.CommandButton1.Caption = "OK"
  5.  Me.CommandButton2.Caption = "キャンセル"
  6. End Sub
  7. '========== ⇩(19) フォーム表示時 ====================
  8. Private Sub UserForm_Activate()
  9.  Dim i As Long       'カウンタ変数(メールアドレス先の行位置)
  10.  Select Case SM
  11.   Case 1
  12.    Me.Caption = "送り先(TO)"
  13.   Case 2
  14.    Me.Caption = "送り先(CC)"
  15.   Case 3
  16.    Me.Caption = "送り先(BCC)"
  17.  End Select
  18.  Me.ListBox1.Clear
  19.  With AddressSheet
  20.   For i = AddressRow To .Cells(Application.Rows.Count, 4).End(xlUp).Row
  21.    Me.ListBox1.AddItem .Cells(i, 4).Value
  22.    If Not .Cells(i, SM) = "" Then Me.ListBox1.Selected(i - AddressRow) = True
  23.   Next i
  24.  End With
  25. End Sub
図7-3

204行目の「Me.ListBox1.MultiSelect = fmMultiSelectMulti」は、送信先リストのListBoxを「複数行選択可」の状態にしています。ちなみにMultiSelectプロパティの値としては3種あり(図7-4)、「fmMultiSelectExtended」は一見便利そうですが「選択解除」が出来ませんので今回には適しません。
MultiSelectプロパティの値
定数内容
fmMultiSelectSingle01行だけ選択可(既定値)
fmMultiSelectMulti1複数行選択可。選択解除には再度選択行をクリック
fmMultiSelectExtended2Shiftキー+クリックで連続選択可。Ctrlキー+クリックで非連続に複数選択可
図7-4

206~207行目は、ボタン表面に文字列を表示させています。

212~233行目は送信先選択ダイアログ(UserForm2)が表示される時に発生するActivateイベントです。
215~222行目は送信先選択ダイアログのタイトル(ダイアログの左上)に送信先レベル(TO、CC、BCC)を表示させています。これは、ユーザーに「現在、どの送信先レベルを選択しているのか」が分かるようにするためです。
この分岐(Select Case)には、送信ダイアログ(UserForm1)から引数として渡され図7-2の197行目で変数SMに代入された値を使用しています。

224行目で一旦ListBoxを空にし、226~231行目でリストを作成しています。今回のように「リスト作成のコードが複数回呼び出される場合」には必ず「一旦リストを空に」しないと、リスト行が次々に追加されてしまいますので注意が必要です。

227行目のForでは、カウンタ変数iを「AddressRow」(図5-1の3行目で、アドレスリストの先頭行として定数宣言)から「AddressSheet.Cells(Application.Rows.Count, 4).End(xlUp).Row」(データ保存用シートのアドレスリストの最下行)までを回しています。最終行の求め方は、図6-4や図6-15と同じく、ワークシートの最下行からEndキーで上に飛んでいき止まったセルの行位置としています。

228行目は、データ保存用シートのアドレスリストの「氏名列(4列目)」データを1つ1つListBoxに追加(Add)していきます。また同時に、送付リストに1が付いてる(送付の対象となっている)場合は、229行目の「Me.ListBox1.Selected(i - AddressRow) = True」でその行を選択状態(青反転)にしています。なおListBoxのインデックスはゼロから始まります。

なお、1行しか選択できないListBoxならば、選択状態の行は「ListIndexプロパティ」で取得・設定できますが、複数選択が可能なListBoxは「Selectedプロパティ」を使用して選択状態を取得・設定します。今回は複数行可であるため「Selectedプロパティ」を使用しています。

7-2ー3.ボタンの処理

2つのCommandButtonをクリックした時のイベントプロシージャが図7-5です。
  1. '========== ⇩(20) OKボタンをクリックした時 ====================
  2. Private Sub CommandButton1_Click()
  3.  Dim i As Long        'カウンタ変数(アドレスの行位置)
  4.  With AddressSheet
  5.   For i = AddressRow To .Cells(Application.Rows.Count, 4).End(xlUp).Row
  6.    If Me.ListBox1.Selected(i - AddressRow) = True Then
  7.     .Cells(i, SM) = 1
  8.    Else
  9.     .Cells(i, SM) = ""
  10.    End If
  11.   Next i
  12.  End With
  13.  Me.Hide
  14. End Sub
  15. '========== ⇩(21) キャンセルボタンをクリックした時 ====================
  16. Private Sub CommandButton2_Click()
  17.  Me.Hide
  18. End Sub
図7-5

まずOKボタン(CommandButton1)ですが、240行目のForでカウンタ変数iをデータ保存用シートのアドレスリストの先頭行位置から最終行位置まで回しています。
その中で、241行目でListBox1の各行が選択状態かを調べ、選択状態であれば242行目を、そうでなければ244行目を実行します。
242行目ではデータ保存用シート側にフラグの1を記入し、そうでなければ244行目でセル値を空にしています。ここで使われている「.Cells(i, SM) 」のSMは送信先レベル(TO=1、CC=2、BCC=3)で、データ保存用シートの列位置に一致させていますので、SMのまま使用できます。

なお、個数が同じであるUserForm2のListBox1の各行を上から調べて行く方法でも良いと思いますが、その場合には242・244行目の「データ保存用シートの行位置指定」の式が変わりますので注意して下さい。

データ保存用シートへのフラグの転写が完了したら、248行目で「送信先選択ダイアログ」を閉じます。なお、この段階で「送信ダイアログ」側の送信先Labelとデータ保存用シートのデータが食い違う可能性がありますので、フォーム呼び出し側である図6-16では「送信先の再表示(reDisp)」が必要になります。

キャンセルボタン(CommandButton2)をクリックした時は、データ保存用シートへの書き込みはせずに、データ破棄し254行目でダイアログを閉じます。

8.UserForm3(送信先編集ダイアログ)

送信ダイアログ(UserForm1)の「送信先編集」ボタンから呼び出されるのが「送信先編集ダイアログ(UserForm3)」です。

8-1.フォームの作成

フォーム上には図8-1のように、氏名とメールアドレスを2列に表示するListBoxを配置し、その下に編集をするための「氏名用TextBox1」と「メールアドレス用TextBox2」を配置します。
TextBoxの横幅は適当ですが、ListBoxの2列の幅はTextBox幅と合わせるように図8-2の266行目で調整しています。
また、その下にメールアドレスの「追加」「修正」「削除」ボタンを配置し、最後に「戻る」ボタンを置いています。
UserForm3のフォーム構成
図8-1

8-2.フォームモジュール

8-2ー1.初回起動時のフォーム設定

この送信先編集ダイアログ(UserForm3)は、送信ダイアログ(UserForm1)の「送信先編集」ボタン(図6-12の114行目)から呼び出されます。フォームの初回起動時に発生するInitializeイベントプロシージャが図8-2です。
  1. '========== ⇩(22) 初回起動時のフォーム設定 ====================
  2. Private Sub UserForm_Initialize()
  3.  Me.CommandButton1.Caption = "追加"
  4.  Me.CommandButton2.Caption = "修正"
  5.  Me.CommandButton3.Caption = "削除"
  6.  Me.CommandButton4.Caption = "戻る"
  7.  Me.ListBox1.ColumnCount = 2
  8.  Me.ListBox1.ColumnWidths = Me.TextBox1.Width & ";"
  9. End Sub
図8-2

260~263行目は、各ボタンの表面に文字列を表示させています。

265行目は、氏名+メールアドレスのリストをListBoxに表示させるために、2列の設定をしています。
また、その横方向の割り振りを266行目で定めています。通常は「ListBox1.ColumnWidths = "100 ; 200"」などとポイント単位の実数値(間はセミコロンで区切る)で指定しますが、下方のTextBox1の幅に合わせて1列目の幅を決めるのであれば、266行目のような記述も可能です。
なお「Me.TextBox1.Width」の後ろに「& ";"」を付けているのは、Microsoftサイトに「列幅の一部または全部を省略して設定できます。値の設定を省略するときは、区切り文字(セミコロン)だけを記述します。」とありますので付けています。
但し、私のPCではセミコロンが無くても表示されています。

8-2ー2.フォーム表示時の設定

ダイアログが実表示される時に発生するActivateイベントプロシージャが図8-3です。
  1. '========== ⇩(23) フォーム表示時の設定 ====================
  2. Private Sub UserForm_Activate()
  3.  Dim i As Long        'カウンタ変数(アドレスリストの行位置)
  4.  With AddressSheet
  5.   Me.ListBox1.Clear
  6.   For i = AddressRow To .Cells(Application.Rows.Count, 4).End(xlUp).Row
  7.    Me.ListBox1.AddItem ""
  8.    Me.ListBox1.List(i - AddressRow, 0) = .Cells(i, 4)
  9.    Me.ListBox1.List(i - AddressRow, 1) = .Cells(i, 5)
  10.   Next i
  11.  End With
  12.  Me.TextBox1.Value = ""
  13.  Me.TextBox2.Value = ""
  14. End Sub
図8-3

274~281行目ではリストボックスに「氏名+メールアドレス」のリストを表示させています。274行目の「AddressSheet」は、データ保存用シートです。
275行目では、まず現状のListBox1のリストを全消去しています。
次に276行目のForでカウンタ変数iをデータ保存用シートのアドレスリストの先頭行から最終行までを回します。リストは2列有り、277~279行目で1行ずつ作っていきます。

リストが1列なら「ListBox1.AddItem 〇〇〇」と、AddItemメソッドのみでリスト行を作りますが、複数列の場合には
 「まず空のリスト行を作る」→「作ったリスト行の1列目にデータを入れる」→「2列目にデータを入れる」→・・・
という手順で作っていきます。(なお、1列目のインデックスはゼロ、2列目のインデックスは1になります。)
今回も、277行目で「空のリスト行」を作り、278行目で「インデックス=0のリスト行にデータ(4列目の氏名列)」を入れ、279行目で「インデックス=1のリスト行にデータ(5列目のメールアドレス列)」を入れています。
この時のインデックスは「i - AddressRow」という式を使い、ゼロからスタートするようにしています。

「氏名+メールアドレス」のリストが完成した段階では、リストは何も選択していない状態ですので、283~284行目でリストの下の「編集用TextBox」を空にしています。

8-2ー3.リストボックスをクリック、ダブルクリックした時の動作

今回の場合、ListBoxは起動直後は「未選択状態」です。その状態からリストのどこかの行をクリックした時に動作するのがClickイベントプロシージャで、図8-4の上側になります。
またこのListBoxは複数行選択される訳にはいかないので、MultiSelectプロパティの設定はしていません(図7-4でのfmMultiSelectSingle(既定)ということになります)。ということは、一旦どこかのセルを選択状態にしてしまうと二度と未選択状態(どの行も選択していない状態)にはできないと言うことになります。
ですので今回は「リストのどこかをダブルクリックをすると未選択状態」になるようにしました。それが図8-4の下側のDblClickイベントプロシージャです。
  1. '========== ⇩(24) リストボックスをクリックした時の動作 =================
  2. Private Sub ListBox1_Click()
  3.  Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
  4.  Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
  5. End Sub
  6. '========== ⇩(25) リストボックスをダブルクリックした時の動作 ====================
  7. Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  8.  Me.ListBox1.ListIndex = -1
  9.  Me.TextBox1.Value = ""
  10.  Me.TextBox2.Value = ""
  11. End Sub
図8-4

「クリック」した時には、291~292行目で選択した行(ListIndex)のデータを下のTextBoxにコピーします。氏名列とメールアドレス列の両方についてコピーしています。

「ダブルクリック」時には、まずListBox1を未選択状態にするため、299行目でListIndexを「ー1」にします。
そしてリスト下の編集用TextBoxを空の状態(300~301行目)にします。

寄り道
なお、DblClickイベントを使用しないでも、一旦「戻る」ボタンで送信ダイアログ(UserForm1)に戻り、再度「送信先編集ダイアログ」に入るという方法はあります。
また「未選択状態」を作りたかったのは、アドレスデータを一番下に追加できるようにするためです。追加する位置を決める方法は色々考えられ、良くあるのは「選択行のひとつ上」に暫定的に挿入し、そのあと「↑」「↓」で移動する、というものです。
しかし今回は、移動する機能は省き、選択状態ならば「選択行のひとつ上」に挿入し、未選択状態ならば「一番下」に挿入としました。そのため、未選択状態を操作後にも作れるようにしたかった訳です。

8-2ー4.「追加」ボタンのクリック時

「追加」ボタンをクリックした時に動作するプロシージャが図8-5です。
  1. '========== ⇩(26) 「追加」ボタンのクリック時 ====================
  2. Private Sub CommandButton1_Click()
  3.  Dim Ans As Integer         'MsgBoxの戻り値(OK=1、キャンセル=2)
  4.  Dim ListNo As Long         'データ挿入する位置(アドレスリスト中の行位置)
  5.  If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Then
  6.   MsgBox "表示名、またはアドレスが空になっています"
  7.   Exit Sub
  8.  ElseIf Me.ListBox1.ListIndex = -1 Then
  9.   Ans = MsgBox("一番下にデータを追加して良いですか?", vbOKCancel)
  10.   If Ans = vbCancel Then Exit Sub
  11.   ListNo = Me.ListBox1.ListCount
  12.  Else
  13.   Ans = MsgBox("選択項目の一つ上にデータを追加して良いですか?", vbOKCancel)
  14.   If Ans = vbCancel Then Exit Sub
  15.   ListNo = Me.ListBox1.ListIndex
  16.  End If
  17.  With AddressSheet
  18.   .Cells(AddressRow + ListNo, 4).EntireRow.Insert
  19.   .Cells(AddressRow + ListNo, 4) = Me.TextBox1.Value
  20.   .Cells(AddressRow + ListNo, 5) = Me.TextBox2.Value
  21.  End With
  22.  Call UserForm_Activate
  23. End Sub
図8-5

まず310行目で、TextBox1(氏名側)とTextBox2(メールアドレス側)のどちらも空で無いことを確認しています。もしどちらかが空だった場合は、311行目でコメントを出し、312行目でプロシージャを抜けます。

TextBox1、TextBox2の両方とも空で無い場合は、314行目でリストが選択状態か否かを調べます。もし未選択状態(ListIndex = -1)の時は315~317行目を実行し、選択状態(ListIndex >= 0)の時は320~322行目を実行します。

「未選択状態」の時、新規データは「一番下に挿入」することにしていますので、315行目でMsgBoxを出しその回答がキャンセル(vbCancel)だった時には316行目でプロシージャを抜けます。それ以外の時(OKの時)は、変数ListNoに「現在のリストの件数」を代入します。
例えば図8-6のように5件のリスト項目があった場合、リスト件数(ListBox1.ListCount)は「5」となり、その値が変数ListNoに代入されます。データ保存用シートのアドレスリストに、そのListNoの位置(5番目)にデータを追加挿入することになるので「一番下に項目が追加される」ことになります。

「追加」でデータを入れる位置
図8-6

一方「どこかを選択している状態」の時は、320行目でMsgBoxを出しその回答がキャンセル(vbCancel)だった時には321行目でプロシージャを抜けます。それ以外の時(OKの時)は、322行目で変数ListNoに「現在選択しているリスト行のListIndex」を代入します。
ListIndexはゼロからはじまりますので、例えば図8-6のように3番目のデータを選択しているとすれば、ListIndex=2 が入ります。そしてデータ保存用シートのアドレスリストに、そのListNoの位置(2番目)にデータを追加挿入することになるので「選択している3番目項目の一つ上にデータが追加される(追加項目が新しい3番目になる)」ことになります。

325~329行目では、実際にデータを挿入します。
まず326行目では挿入する行全体をEntireRowで取得し、Insertで「行挿入」します。326行目のコードは4列目のセルを元に行全体を取得していますが、何列目でもOKです。また、行挿入をするため、他の「件名」「本文」はアドレスデータとは異なる行(今回は2行目)に書き込むことにしています。
挿入したら4列目にTextBox1の氏名データを(327行目)、5列目にTextBox2のアドレスデータを(328行目)書き込みます。

最後に331行目で、UserForm2のActivateイベントプロシージャ(図8-3)を呼出し、ListBox1を再表示しています。この331行目の実行によりデータが挿入されたことがユーザーの目に見えるようになります。
なお、Activateイベントプロシージャのようなイベントを外部から呼び出す事に抵抗のある方は、ListBox1の表示部分を別プロシージャにしておき、そのプロシージャをActivateから呼び出したり、CommandButton1から呼び出したりすれば分かり易いかと思います。

寄り道
なお、メールアドレスは「・・・@・・・」というように、「@(アットマーク)」を境にして右側にドメイン名、左側にユーザー名が来ます。どちらも必須で、アットマークも1つのみでないとエラーが発生します。また、使ってはいけない文字もあります。
今回、310行目などでは「TextBox2が空で無い事」を条件にしていますが、これだけでは足りないという事になります。

あまり正規表現に詳しく無いので恐る恐る書いていますが、メールアドレスが単に「〇〇@△△.□□」という形である、ということだけをチェックするのであれば、以下のような関数になりそうです。
但し、先頭に使える文字種は無視していたり、ドットで終わろうがOKとしていますので、不完全なものであることはご承知おき下さい。
  • Private Function AddressCheck(MA As String)
  •  Dim RE As Object
  •  Set RE = CreateObject("VBScript.RegExp")
  •  RE.Pattern = "^.+@.+¥..+$"
  •  RE.Global = True
  •  AddressCheck = RE.Test(MA)
  • End Function
図8-7

また正規表現を使わずに、Replace関数やSplit関数で「@(アットマーク)」「.(ドット)」の存在や数量を確認したり、その前後の文字列を確認したりすることも可能です。

8-2ー5.「修正」ボタンのクリック時

「修正」ボタンをクリックした時に動作するプロシージャが図8-8です。
  1. '========== ⇩(27) 「修正」ボタンのクリック時 ====================
  2. Private Sub CommandButton2_Click()
  3.  Dim Ans As Integer         'MsgBoxの戻り値(OK=1、キャンセル=2)
  4.  If Me.ListBox1.ListIndex = -1 Then
  5.   MsgBox "編集するリスト項目を選択して下さい"
  6.   Exit Sub
  7.  End If
  8.  If Me.TextBox1.Value = "" Or Me.TextBox2.Value = "" Then
  9.   MsgBox "表示名、またはアドレスが空になっています"
  10.   Exit Sub
  11.  Else
  12.   Ans = MsgBox("選択した項目を編集して良いですか?", vbOKCancel)
  13.   If Ans = vbCancel Then Exit Sub
  14.   With AddressSheet
  15.    .Cells(AddressRow + Me.ListBox1.ListIndex, 4) = Me.TextBox1.Value
  16.    .Cells(AddressRow + Me.ListBox1.ListIndex, 5) = Me.TextBox2.Value
  17.   End With
  18.   Call UserForm_Activate
  19.  End If
  20. End Sub
図8-8

まず「修正」ですので「どの項目を修正するのか」が決まっていないと修正になりません。よって339~342行目で「未選択状態(ListBox1.ListIndex = -1)」だったら340行目でコメントを出し、341行目でプロシージャを抜けます。

リストのどこかを選択状態であれば、次は344行目で「TextBox1(氏名側)とTextBox2(メールアドレス側)のどちらも空で無いこと」を確認しています。もし一方でも空だった場合は、345行目でコメントを出し、346行目でプロシージャを抜けます。

TextBox1、TextBox2の両方とも空で無い場合は、349行目で「選択した項目を編集上書きして良いか」をユーザーに確認し、キャンセル(Ans=vbCancel)の時はプロシージャを抜けます。

AnsがvbCancel以外の時(OKの時)は、353~356行目を実行します。
354行目ではデータ保存用シートのアドレスリストの選択している行の氏名列(4列目)データをTextBox1のデータで置き換えます。また355行目ではTextBox2のデータで置き換えます。

最後に358行目で、UserForm2のActivateイベントプロシージャ(図8-3)を呼出し、ListBox1を再表示しています。

8-2ー6.「削除」ボタンのクリック時

「削除」ボタンをクリックした時に動作するプロシージャが図8-9です。
  1. '========== ⇩(28) 「削除」ボタンのクリック時 ====================
  2. Private Sub CommandButton3_Click()
  3.  Dim Ans As Integer         'MsgBoxの戻り値(OK=1、キャンセル=2)
  4.  If Me.ListBox1.ListIndex = -1 Then
  5.   MsgBox "削除するリスト項目を選択して下さい"
  6.   Exit Sub
  7.  End If
  8.  Ans = MsgBox("選択した項目を削除して良いですか?", vbOKCancel)
  9.  If Ans = vbCancel Then Exit Sub
  10.  With AddressSheet
  11.   .Cells(AddressRow + Me.ListBox1.ListIndex, 4).EntireRow.Delete
  12.  End With
  13.  Call UserForm_Activate
  14. End Sub
図8-9

まず「削除」ですので「どの項目を削除するのか」が決まっていないと削除になりません。よって368~371行目で「未選択状態(ListBox1.ListIndex = -1)」だったら369行目でコメントを出し、370行目でプロシージャを抜けます。

リストのどこかを選択状態であれば、次は373行目で「選択した項目を削除して良いか」をユーザーに確認し、キャンセル(Ans=vbCancel)の時はプロシージャを抜けます。

AnsがvbCancel以外の時(OKの時)は、377~379行目を実行します。
378行目では、削除する行全体をEntireRowで取得し、Deleteで「行削除」します。378行目のコードは4列目のセルを元に行全体を取得していますが、何列目でもOKです。

最後に381行目で、UserForm2のActivateイベントプロシージャ(図8-3)を呼出し、ListBox1を再表示しています。

8-2ー7.「戻る」ボタンのクリック時

戻るボタンをクリックした際は、図8-10のように「送付先編集ダイアログ(UserForm2)」を閉じ(Hide)ます。
  1. '========== ⇩(29) 「戻る」ボタンのクリック時 ====================
  2. Private Sub CommandButton4_Click()
  3.  Me.Hide
  4. End Sub
図8-10

「追加」「修正」「削除」ボタンをクリックしていない状態で、このキャンセルボタンをクリックした時には、元の状態のままダイアログが閉じることになります。

9.アドインとしてExcelにマクロを登録

このマクロをExcelの機能の1つに登録し、Excel上部のリボンのボタンを押せばシステムを起動できるようにできます。
その方法については「年賀状リスト等の宛名検索と追記 アドイン登録」を参照下さい。

10.最後に

アドインマクロの中のシート上にデータを残しておく手法を使うと、色々便利に使えそうです。例えば今回の日報の様なシステムに、良く使うワードを登録しておくとか、日程表のセルに図形で予定線を描くときの線色や線種を登録しておくとかが考えられます。実際に図形の線引きシステムは過去に作ったことを思い出したので、今度再製作したら紹介したいと思います。

ExcelのPDF化ですが、今回はワークシートにしか対応させることが出来ませんでした。その理由は、印刷設定のプロパティ・メソッドがあまりにも多くて途中で挫けてしまったためです。少し整理しながら、今後はグラフシート等のPDF化もできるようにしていきたいと思います。

しかし今回は、①グラフはPDF化できない、②メールアドレス書式のチェックは出来ない、③送信先エラーは確認できない と残念ながら未完成度の高いシステムになってしまいました。つくづく私の勉強不足を痛感させられました。
実務には使えないかもしれませんが、部分的にでもつまみ食いしていただければ幸いです。


Excelで作った日報をPDFでそのままメール送信(it-049.xlsm)

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル(Excel等)のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。(2022年4月より切替開始)
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。