エクセルVBA テーブル行が変化したら上司へ自動メール連絡

2020年10月27日



プログラムを組んだ目的



エクセルのテーブル機能を使ってデータやリストの管理を行っており、データが追加されたら上司に連絡する必要がある時ありませんか?

毎回、同じ内容を伝えるのも面倒と言うことでメールで伝えることができるようにしてみました。

これなら、宛先とメール本文を固定しておけば、テーブルに変化が起きた時に自動で上司へ報告することができます。

報告忘れ防止にもなります。



必要になるソフトウェア

  • エクセル
  • アウトルック



プログラムの組み方

特定のシートからテーブルのオブジェクトと最終行を取得するプログラム


Alt + F11」でVBAを開く。

thiswookbook」をダブルクリックで開きそこにプログラムを記述していく。

'変数の定義
Dim lst1 As ListObject
Dim cntBf As Long
Dim cntAf As Long


Private Sub Workbook_boforeclose(wb As Boolean)

'  顧客リストシートからテーブルの行を取得する
  cntBf = Sheets("顧客リスト").Range("b1")
  Set lst1 = Sheets("顧客リスト").ListObjects(1)
  cntAf = lst1.ListRows.Count

'  顧客リストシートのテーブル行を比較し、変化があればメール送信プログラムを動かす
  If cntBf <> cntAf Then
    Call 自動メール送信  'メール送信用プログラムを呼び出す(下に記載)
    MsgBox("メールを送信しました")
  End If

End Sub



Private Sub workbook_open()

'  顧客リストシートからテーブルの最終行を取得
  Set lst1 = Sheets("顧客リスト").ListObjects(1)
  cntBf = lst1.ListRows.Count
  Sheets("顧客リスト").Range("b1").Value = cntBf

End Sub



メール送信プログラム(標準モジュールを追加)


まず、エクセルファイルに「メール送信先シート」を作成する。

VBAに移動し、

1、メニューバーの「ツール」から「参照設定」を選択。

2、その中から「Microsoft Outlook 16.0 Object Library」を探してチェックを入れる。

3、メニューバーの「挿入」から「標準モジュール」を選択し、プログラムを記述していく。

Sub 自動メール送信()

  '変数の定義とオブジェクト作成(outlook起動)
  Dim toaddress As String
  Dim subject, mailBoby As String
  Dim outlookObj As Outlook.Application
  Dim mailItemObj As Outlook.MailItem

  '変数へデータ格納(メール送信先シートへ書いておく必要がある)
  toaddress = Sheets("メール送信先シート").Range("b2") '宛先
  subject = Sheets("メール送信先シート").Range("b3") '件名
  mailBody = Sheets("メール送信先シート").Range("b4") '本文

  '新規メール作成
  Set outlookObj = CreateObject("Outlook.Application")
  Set mailItemObj = OutlookObj.CreateItem(olMailItem)
  mailItemObj.BodyFormat = 2
  mailItemObj.To = toaddress
  mailItemObj.subject = subject

  '本文へハイパーリンクをつける
  Dim link1, strstyle, url As String
  link1 = Sheets("メール送信先シート").Range("b5")
  url = "<a href=""" & link1 & """>" & "リンクを付けたいファイルの名前" & "</a>"
  strstyle = "<font face="" MS P明朝 "" color = "" #fff ""><b>" & mailBody & "</b></font><br>" & url
  mailItemObj.HTMLBody = strstyle

  'メール送信
  mailItemObj.display
  'mailItemObj.send  ※コメント化を解けばメール送信される

  'オブジェクト開放
  Set outlookObj = Nothing
  Set mailItemObj = Nothing

End Sub



このプログラムを使うための注意点

  1. テーブルは「3行目以下」に作成しておく
  2. メール送信先シートの「b2セル〜b5セル」に「宛先」、「件名」、「本文」を記載する

プログラムの説明

  1. エクセルファイルが開いた時に「顧客リストシート」のテーブルの最終行を取得し、「b1セル」に数値を貼り付ける。
  2. ファイルを閉じる動作をした時に「顧客リストシート」の「b1セル」の数値とテーブルの最終行を比較し、違っていればメールの送信プログラムを動かす。
  3. 「メールの送信先シート」から「宛先、件名、本文」を読み取りメール作成からメール送信までを自動で実施する。