子どもの迎えに焦って、メール送信ミス。その処理VBAで解決できるかもしれませんよ。

2020年10月8日 05:52

時刻は18：30を過ぎようとしているところ

友子「まずい…もう18：30！！」
友子「今日は店舗からの問い合わせも多くて、気が付けばこんな時間に…
もう迎え(保育園)に行かないと延長料金かかっちゃう！！」
友子「あぁー、でも業務報告書作って今日の問い合わせのクレームの件は作って報告しないと…」

「カタカタカタカタ………………………これで鈴木課長へ送信！」

友子「よし、出来た！」
友子「では、お先失礼します！」
同部署の人達「お疲れさまでしたー」

翌日

鈴木課長「山田さん、昨日の業務報告書来てないけど送ってくれる？」

友子「ぇ？メール送りましたけど、届いてませんか？」

鈴木課長「いや、来てないよ？ちょっと確認してもらえる？」

友子「かしこまりました。（おかしいな…昨日ちゃんと送ったはずなんだけど…」

そしてOutlookメールを開いてよくメールの内容を確認してみると…

友子「あ！これ宛先が違う！！鈴木課長じゃなくて、〇〇社の鈴木さんのところにメール送ってる…」

友子「鈴木課長、申し訳ありません。昨日の業務報告メールを「鈴木」違いで、〇〇社の「鈴木」さん宛てにメールを送信してしまっていました…」

鈴木課長「ぁ、そういうこと？わかりました。これ、内容によっては会社の信用を失いかねない大事故になりうるから、気を付けて。
とにかく早急に先方に謝罪連絡をお願いします。」

友子「申し訳ございません。早急に連絡します。」

………………………………………………………………………………………………………………………

友子「はぁ…やっちゃったなぁ…。課長の言う通り、内容次第じゃ会社に重大な損失を与えかねないよ…。時間も時間で早く子ども迎えに行かなきゃいけないのが先走って、焦ってよく確認しないで送っちゃったのがいけなかったなぁ…」

武ちゃん「友ちゃん、お疲れ！どうしたの？またアンパンマンが顔を水で濡らしちゃって元気が出ないような顔してるけど？」

友ちゃん「ぇ…またそんな顔してた…？」

武ちゃん「うん。なんかあったの？」

友子「いや実はね………………………………ってことがあって。」

武ちゃん「なるほどねぇ。宛先間違って送っちゃうはよくあるパターンよね。」
武ちゃん「でも、メールの宛先とかは基本同じはずだから、そこを利用すればVBAを使えばメールをいちいち毎回作らなくても、勝手にやってくれるよ。」

友子「ぇ！？VBAってメールも自動化できちゃうの？」

武ちゃん「うん。Outlookのメールを使えばね！あたしは基本定型のメールはVBAで自動化して極力手入力は避けてるわ。
その方が面倒もないし、ミスも無いしね。」

友子「武ちゃん、本当すごい…。」

武ちゃん「基本面倒くさ！って思った瞬間にこれ、自動化できないかな？って考えてVBAを使って自動化してるだけなんだけどね。ま、それより同じミスをしないように友ちゃんもこれ自動化のコード書いていこうか！」

友子「ありがとう！毎度助けられてて、本当に感謝ですm(_ _)m」

武ちゃん「じゃ、早速やってくよ！」

自動化していく上での事前確認

武ちゃん「これから作っていくものの流れなんだけど。」

1. 報告書の中の内容でメールに転記できるものがあるか確認する。
今回の場合「宛先名」「件名」なんかは転記できそうかな。
※1ちなみに転記できるセルがセル結合などされてたら事前に解除しておくこと
それについては"セル結合　悪"なんかでググるとセル結合がいかにダメなことかがいくらでも出てくるから調べてみて
2. エクセルブックが閉じる直前にプログラムが起動する処理を書く
3. エクセルブックを閉じたらOutlookアプリケーションと連携する処理を書く
4. Outlookと連携出来たら、必要な内容(宛先、CC、件名、本文、添付ファイル)をVBAでOutlookメールにデータセットするようにコードを書く

※1 たった1秒で仕事が片づくエクセル自動化の教科書著者の吉田さんもこうおっしゃってます。

快適Excelライフの為に
・セル結合はしない
・データベースファースト
・絶対参照
・作業列
・長く複雑な式は作らない
・関数ネストは3つまで
・セル結合はしない
・勝手にセルや行や列の削除挿入しない
・マウスは使ったほうが絶対いいって
・だからセルは結合すんなって何度も言ってんだろ！！（怒）
— 吉田拳@Excelすごい改善社長 (@sugoi_kaizen) April 8, 2019

武ちゃん「ま、ざっとこんな流れかな。」
武ちゃん「で、今使っている報告書の形式がこんな感じで」

武ちゃん「メールに記述しなきゃいけないのは、"E5"(宛先名)と"E9"(件名)この二つはメールの内容に転記できるところかな」

今回の全体コード

武ちゃん「今回の全体のコードはこんな感じで」

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim objOutlook As Object
   Dim objItem As Object
   
   Set objOutlook = CreateObject("Outlook.Application")
     
   Dim mlItem As Outlook.MailItem
   Set mlItem = objOutlook.CreateItem(olMailItem)
   
   Dim Name As String: Name = Range("E5").Value                       
   Dim Overview As String: Overview = Range("E9").Value               
   Dim MailTo As String: MailTo = "suzuki@xxxx.co.jp"                 
   Dim MailCC As String: MailCC = "otsubone@xxxx.co.jp"               
   Dim MailBody As String
   MailBody = Name & "課長" & vbCrLf & vbCrLf & _
                     "お疲れさまです。山田です。" & vbCrLf & _
                     "本日の業務報告書を提出いたします。" & vbCrLf & _
                     "ご確認の程よろしくお願いします。" & vbCrLf
                                   
                                   

   Dim Judge As Integer
   Judge = MsgBox("メール作成します。よろしいですか？", vbYesNo)
   With mlItem
       If Judge = vbYes Then
           .Display
           .Subject = Overview
           .To = MailTo
           .CC = MailCC
           .Body = MailBody
           
           Dim Awb As Workbook
           Set Awb = ActiveWorkbook
           
           Dim strFileName As String
           strFileName = Awb.Name
           
           Dim strPath As String
           strPath = ThisWorkbook.Path
           .Attachments.Add (strPath & "\" & strFileName)
           
           '.Save
           '.Send
            Awb.Save

       Else
           .Close (1)
       End If
   End With
   
End Sub

武ちゃん「今までに比べたらコードの量はそこまで長くないんだけど、VBA→Outlook連携のところがちょっとややこしいから、そこもひとつずつ解説していくね！」

友子「毎度ありがとう！よろしくお願いします！」

Outlookとの連携設定（参照設定）

武ちゃん「まずVBAとOutlookを連携させてOutlookのどの機能を使うか？っていうのを明確にVBAで指示をしなければいけないのよ。」

武ちゃん「この図みたいにOutlookにもメール、スケジュール、連絡先、タスクって大きく分けて4つ機能があって、今回使いたい機能はOutlookの中のメール機能。」

武ちゃん「そのためにまずはVBEの参照設定をしなきゃいけないんだけど、Alt+F11でVBEの画面を開いて、ツール→参照設定→Microsoft Outlook xx.x Object Libraryの✔を付けてOK」
※現時点(2020/10 時点)のMicrosoft Outlook Object Libraryの最新バージョンは16.0

閉じるときに起動するイベント処理の記述方法

武ちゃん「じゃあこれからVBEにコード書いていくよ！」
武ちゃん「今までは標準モジュールを挿入してその中にコードを記述してたと思うんだけど、
今回は、ユーザー(友ちゃん)が意図して実行ボタンを押さなくても、×ボタンでExcelブックを閉じる瞬間に起動する処理を書いていくには、今までとは違って、
VBEのThisWorkbookをクリックて、Generalって書かれたところのリストをクリックしてWorkbookを選択を選択する必要があるのよ。」

武ちゃん「これを選択するとこんな感じの構文が自動で挿入されるんだけど、今回この構文は使わないから消しちゃってOK」

武ちゃん「さらにその右横にあるDeclarationsと書かれたリストボックスを選択して、"BeforeClose"を選択」

武ちゃん「これをすることによって"BeforeClose"要するにExcelブックを閉じる瞬間にコードが実行される処理を書くことが出来るのよ。」

武ちゃん「こんな感じで自動でコードが挿入されたのがわかるかな？
こういうのをイベント処理って言って、他にもExcelブックを開く瞬間に起動するものだったり、色々あるわ」

友子「なるほどね！自分が意図してなくても、〇〇するときに○○するみたいな処理もVBAではできるってことなんだね。」

Outlookの操作コード

武ちゃん「下準備が整ったからこれから処理を書いていくよ！」

Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

武ちゃん「始めにOutlookと連携するためにOutlook用の変数(オブジェクト)を用意するんだけど、ここのコードは、
CreateObject("Outlook.Application")なので、"CreateObject"つまりオブジェクトを作る、何の？Outlook.Applicationのってことになるの。」

武ちゃん「余談なんだけど、Outlookの操作をする為にSet objOutlook = CreateObject("Outlook.Application")って記述してるんだけど、これは、Microsoft Outlook xx.x Object Libraryのバージョン(xx.xの部分)が16.0であれば、"Set objOutlook = New Outlook.Application"の記述でOK」

Outlookの機能判断「CreateItemメソッド」

Dim mlItem As Outlook.MailItem
Set mlItem = objOutlook.CreateItem(olMailItem)

武ちゃん「で、次のコードなんだけど、
OutlookのMailItemというオブジェクトのmlItem変数を準備して、
objOutlook.CreateItem(olMailItem)をセットする」

武ちゃん「CreateItemメソッドについてはこの表の感じで、CreateItem(定数)で指定した定数によってOutlookで使う機能の種類を判断させてるの。」

メール作成の為の挿入する変数を作る

Dim Name As String: Name = Range("E5").Value
Dim Overview As String: Overview = Range("E9").Value
Dim MailTo As String: MailTo = "suzuki@xxxx.co.jp"
Dim MailCC As String: MailCC = "otsubone@xxxx.co.jp"
Dim MailBody As String
MailBody = Name & "課長" & vbCrLf & vbCrLf & _
           "お疲れさまです。山田です。" & vbCrLf & _
           "本日の業務報告書を提出いたします。" & vbCrLf & _
           "ご確認の程よろしくお願いします。" & vbCrLf

武ちゃん「ここはメール作成する際のデータをセットする時の変数。これを事前に準備しておけば、あとはこの変数を割り振っていけばいいだけ！」

補足：vbCrLf = 改行コードの事です。
「vbCrLf & vbCrLf」と&で繋げることで、改行を2回していることになります。

友子「なるほど。あらかじめ定型の内容はこうやって変数に入れておけば、いちいち手入力しなくても済むし、なにより手入力による誤りも防げるってわけね！」

武ちゃん「そういうこと！」

MailItemプロパティに変数を当てはめる

武ちゃん「あとは次の表に従って、宛先、CC、件名、本文、添付ファイルなどをさっき作った変数を使って作っていくよ。」

【セットの仕方の例】
With mlItem
.Display 'メール作成画面を表示
.Subject = "件名" 'メールの件名
.To = "xxxx@xx.xx" 'メールの宛先
.CC = "###@xx.xx" 'メールのCC
.Body = "本文" 'メール本文
.Attachments.Add ("ファイルパス & ファイル名") '添付ファイル
　.Send '送信
（基本的にこのSendはコメントアウトしておくかそもそも記述しておかない方が無難です。内容確認しないままメールが送信されてしまう為。
本内容も.Sendはコメントアウトしています。）
End With

武ちゃん「ざっくりこんな感じで、プロパティの値が何かが分かっていれば、あとはそれにあらかじめ用意しておいた変数を当てはめていけばいいだけ」

Dim Judge As Integer
Judge = MsgBox("メール作成します。よろしいですか？", vbYesNo)
With mlItem
    If Judge = vbYes Then
       .Display
       .Subject = Overview
       .To = MailTo
       .CC = MailCC
       .Body = MailBody
       
       Dim Awb As Workbook
       Set Awb = ActiveWorkbook
       
       Dim strFileName As String
       strFileName = Awb.Name
       
       Dim strPath As String
       strPath = ThisWorkbook.Path
       .Attachments.Add (strPath & "\" & strFileName)
       Awb.Save
    Else
       .Close (1)
    End If
End With

MsgBoxでメールを作成するかどうかの処理判断

友子「ここでIf文を使ってるみたいなんだけど、これはどういった意味があるの？Judge = MsgBox("メール作成します。よろしいですか？", vbYesNo)
If Judge = vbYes Then
この部分」

武ちゃん「ここ、実はちょっとしたポイントなんだけど、このマクロは"ブックを閉じたときに毎回書いたコードの処理が走る"ようになってるから、
そうなると、ただ中身の修正をしたいだけなのに、閉じる度にメールウィンドウが開いてメールが作成されるようになってしまうから、それって相当うっとうしいじゃない？
なんで、メッセージボックスを使ってメールを作成するかどうかをユーザーに選択させて、"はい"を選んだ時だけメールを作成するようにIf文で条件分岐をするようにしてるのよ。」

友子「ぁーたしかに！間違えて開いちゃった、なんてときにも閉じたらいちいちメール作成されたら嫌だもんね。」

武ちゃん「そうだね。MsgBoxについてはこちらのサイトもわかりやすく解説されているから確認しておくといいかも」

    Dim Awb As Workbook
    Set Awb = ActiveWorkbook
    
    Dim strFileName As String
    strFileName = Awb.Name
    
    Dim strPath As String
    strPath = ThisWorkbook.Path
    .Attachments.Add (strPath & "\" & strFileName)
    Awb.Save
Else
    .Close (1)

添付ファイル処理

武ちゃん「最後のこの部分は、簡潔に言うと、現在開いているブック名称と、そのブックが置いてあるファイルパスがどこか？を指定して、Attachments.Addで報告書をメールへ添付処理をしている。」

武ちゃん「あとは実際に動かしてみるとより分かりやすいと思うから、動いているところを見てみよう！」

武ちゃん「と、まぁこんな感じでExcelブックを閉じると、勝手にOutlookが起動して、宛先、CC、件名、本文が挿入されたのがわかるでしょ？」

友子「本当だ！すごい！」
友子「これなら事前ちゃんと内容を登録しておけば、まず宛先も間違うことないね！」

武ちゃん「そうだね。今回アドレスとか本文は割と単純な内容だったからプログラムのコードとして記述したけど、Excelのセルにアドレスや本文を予め用意しておいて、その値をOutlookに転記するってこともできるから、本文が長い時なんかはセルに内容を入力しておいた方がいいかもね。」

武ちゃん「こんな感じでExcelに準備しておいて、MailTo = Range("A2").Valueみたいな感じで、CC、宛名、本文もRange(またはCells)の値を変数に代入しておくのも一つの手かな。」

友子「これはこれでVBEの中身をいちいち確認しなくても済むから見やすいかも！」

武ちゃん「どうかな？これでこれからは急いでても報告書作って閉じるだけでメール出来上がるからミスも防げそうかな？」

友子「うん。ありがとう。これで心にもゆとりを持って出来そう！」

今後、友子は誤ってメール送信することはなくなったそうです。
皆さんも、ぁ、やべ！！！ってなった経験ありませんか？
何か手入力をしていてミスが多い、なんて時はVBAの出番かもしれません。

今回の内容がよかったとか、面白かったと思いましたら、いいねやフォロー、またご意見ご感想などもお待ちしておりますので、連慮なくご連絡ください。
実際にこんなことしたんだけど、VBAで出来ますか？といったご質問大歓迎です！！

Twitterアカウント@Kyrieee_aで日ごろtweetしています。
ご感想などについては気軽にDMください。

ここまで読んでいただきありがとうございました。
今後ともよろしくお願いします。

また、ブログも運営しております。
普段プログラミング学習で思うようにできず、悩んでいる、学習の仕方がわからない、エンジニアってどんなことしているの、など
プログラミングにおけるマインドについて情報発信しております。