保健室来室自動メール送信システム作成　※全文無料

2019年7月23日 16:33

生徒が保健室に来ると、保健室の先生が担任に内線電話をかけて知らせるということをしている学校も多いのではないでしょうか。
しかし、忙しい担任に確実に連絡をすることが難しく、ずっとあとになってから生徒が保健室に行っていたことを知るということもあるのではないでしょうか。

今回は、保健室に生徒が来室していることをリアルタイムに担任が把握するシステム作りを目標とします。
用意するものは、タブレット一台だけです。

保健室に来室した生徒がタブレットを操作して、担任にメールを送ります。

「はい」を選択すると、担任にメールが送信されます。

１．レイアウトを決める

今回はマクロも使いますので、マクロ有効ブックを使います。

シートの構成は、「メイン」「生徒データ」「担任データ」です。

「生徒データ」には、学年・組・番号・氏名の一覧を作ります。
右端に学年・組・番号をひとまとめ５桁の整数で表すようにします。

「担任データ」には、学年・組・担任メールアドレスの一覧を作ります。
右端に学年・組をひとまとめ３桁の整数で表すようにします。

「メイン」には、学年・組・番号・来室退室を入力する枠を用意します。
以下の計算を行うセルを用意します。

学年・組・番号をひとまとめ５桁の整数にする。
　⇒　氏名をVLOOKUP
学年・組をひとまとめ３桁の整数にする。
　⇒　担任メアドをVLOOKUP
来室・退室に合わせて、メールの件名と本文を作成する。

本文内のTEXT(NOW(),"mm月dd日hh時mm分")の部分について解説します。
=NOW()だけですとしたように表示されます。

これは、EXCEL内で日時が数値として認識されているからです。
43662.6112...の整数部分が日付を表し、小数部分が時間を表しています。
これを07月16日14時39分のように人間が見てわかる表示に変更するのがTEXT関数です。

２．入力フォームを作成

次に入力フォームを作成します。
生徒が入力することを考えて、キーボードを使わずに入力できる形にします。
そのためにユーザーフォームを使用します。

ALT+F11でVBEを起動します。
右側のメニュー内にあるMicrosoft EXCEL Object を右クリック＞挿入＞ユーザーフォームを選択します。

ボタンを作成していきます。

右のメニューで背景色を好きなものにし、表示される文字を「入室」にします。フォントの設定で文字の大きさを変更します。

こんな感じです。

「退室」ボタンと「決定」ボタンを同様に作成し、「終了」ボタンを小さく作っておきます。

「決定」ボタンをダブルクリックし、「決定」ボタンが押されたときの挙動をプログラムします。

Private Sub CommandButton1_Click()
       If Me.Controls("OptionButton1").Value = True Then   'OptionButton1が選択されていたら
           Unload UserForm1                                'ユーザーフォーム１（このユーザーフォーム）を閉じる
           Cells(5, 2).Value = "入室"                      'B5のセルに「入室」と代入する。
           UserForm2.Show                                  'ユーザーフォーム２を表示する。
       ElseIf Me.Controls("OptionButton2").Value = True Then
           Unload UserForm1
           Cells(5, 2).Value = "退室"
           UserForm2.Show
       ElseIf Me.Controls("OptionButton3").Value = True Then
           Unload UserForm1
           Application.Quit                                'EXCELを終了する。
       End If
End Sub

もうひとつユーザーフォームを作成します。
リストボックスを使って学年・組・番号の入力欄を作ります。

「決定」ボタンを作成し、先程と同様にプログラムを入れます。
１つ目は「決定」ボタンが押されたときの挙動です。
２つ目はリストボックスの内容です。

Private Sub CommandButton1_Click()
   Cells(2, 2) = ListBox1.Value        '学年・組・番号の値をセルに入力する。
   Cells(3, 2) = ListBox2.Value
   Cells(4, 2) = ListBox3.Value
   If IsError(Cells(9, 2).Value) Then  '入力チェック
       MsgBox ("入力された学年・組・番号に対応する生徒が見つかりませんでした。再度入力をしてください")
       Unload UserForm2
       UserForm1.Show
   Else
       Unload UserForm2
       Call 確認
End If
   End Sub

Private Sub UserForm_Initialize()
   With ListBox1
       .AddItem "1"
       .AddItem "2"
       .AddItem "3"
   End With
   
   With ListBox2
       .AddItem "1"
       .AddItem "2"
       .AddItem "3"
       .AddItem "4"
       .AddItem "5"
       .AddItem "6"
       .AddItem "7"
       .AddItem "8"
       .AddItem "9"
       .AddItem "10"
   End With
   
   With ListBox3
       .AddItem "1"
       .AddItem "2"
       .AddItem "3"
       .AddItem "4"
       .AddItem "5"
       .AddItem "6"
       .AddItem "7"
       .AddItem "8"
       .AddItem "9"
       .AddItem "10"
       .AddItem "11"
       .AddItem "12"
       .AddItem "13"
       .AddItem "14"
       .AddItem "15"
       .AddItem "16"
       .AddItem "17"
       .AddItem "18"
       .AddItem "19"
       .AddItem "20"
       .AddItem "21"
       .AddItem "22"
       .AddItem "23"
       .AddItem "24"
       .AddItem "25"
       .AddItem "26"
       .AddItem "27"
       .AddItem "28"
       .AddItem "29"
       .AddItem "30"
       .AddItem "31"
       .AddItem "32"
       .AddItem "33"
       .AddItem "34"
       .AddItem "35"
       .AddItem "36"
       .AddItem "37"
       .AddItem "38"
       .AddItem "39"
       .AddItem "40"
       .AddItem "41"
       .AddItem "42"
       .AddItem "43"
       .AddItem "44"
       .AddItem "45"
       .AddItem "46"
       .AddItem "47"
       .AddItem "48"
       .AddItem "49"
       .AddItem "50"
       .AddItem "51"
       .AddItem "52"
       .AddItem "53"
       .AddItem "54"
       .AddItem "55"
   End With
End Sub

３．マクロの作成

右側のメニュー内にあるMicrosoft EXCEL Object を右クリック＞挿入＞標準モジュールを選択し、以下３つのマクロを作ります。

Sub メインメニュー表示()
UserForm1.Show
 Call メインメニュー表示 'メインメニューが自動でポップアップするようにする。
End Sub

Sub 確認()
   Dim YN As String
   YN = MsgBox(Cells(9, 2).Value & " さんでよろしいですか。", vbYesNo)         '人物確認
   If YN = vbYes Then
       If Cells(5, 2).Value = "入室" Then                                      '入室か退室か判断
           MsgBox (Cells(4, 1).Value & " さんの保健室入室を受け付けました。")
           Call メール送信                                                     'メール送信
       Else
           MsgBox (Cells(4, 1).Value & " さんの保健室退室を受け付けました。")
           Call メール送信
       End If
   Else
       MsgBox ("再度はじめからしてください。")
   End If
End Sub

Sub メール送信()
   Dim outlookObj As Outlook.Application       'Outlookを起動するおまじない
   Dim mailItemObj As Outlook.MailItem
   Set outlookObj = CreateObject("Outlook.Application")
   Set mailItemObj = outlookObj.CreateItem(olMailItem)
                                               'メールの設定
   mailItemObj.BodyFormat = 3                  'リッチテキストに変更
   mailItemObj.To = Cells(10, 2).Value         'to宛先
   mailItemObj.subject = Cells(11, 2).Value    '件名
   mailItemObj.Body = Cells(12, 2).Value       '本文
   
   mailItemObj.Send                            'メールを送信
   Set outlookObj = Nothing                    'Outlookを終了するおまじない
   Set mailItemObj = Nothing
End Sub