Excelでメールアラートを自動化!Power AutomateとVBAで期限管理を効率化する2つの簡単な方法

本記事では、複数の担当者のメールアドレスと締め切り日(期限)を含むデータセットを使用し、その日付をもとにメールアラートを自動的に送信する仕組みを構築します。期限が近づいたら自動でリマインドメールが届くようにすれば、締め切りの見落としや遅延を防ぎ、業務の効率化につながります。

方法1:Power Automateを使ってExcelで自動メールアラートを作成する

まずはMicrosoftのクラウド自動化ツール「Power Automate」を活用する方法です。ExcelファイルをOneDriveまたはSharePointに保存しておけば、定期的にデータをチェックして期限に応じたメールを自動送信できます。

ステップ1:データセットの準備

• 冒頭で紹介したデータセットを使用します。
• 締め切り日(Deadline)に基づいて、メールアラートを自動送信できるようにします。

• データセット内の任意のセルを選択し、Ctrl + Tキーを押します。
• 「テーブル作成」ダイアログボックスが表示されます。
• テーブルの範囲はデータセットの範囲になっていることを確認します。
• 「OK」をクリックしてテーブルを作成します。

• ExcelファイルをOneDrive for BusinessまたはSharePointに保存します。
• 次にMicrosoft 365のアプリケーション画面へ移動します。

ステップ2:Power Automateを起動する

• 画面左上のアイコン(四角形の中に9個の点が並んだもの)をクリックします。

注意:この機能は個人用OneDriveアカウントでは動作せず、Microsoft 365アカウントが必要です。

• 「すべてのアプリ」をクリックすると、MS 365のアプリ一覧が表示されます。

• 一覧から「Power Automate」アプリケーションを選択します。

• Power Automateの画面で「作成(Create)」をクリックし、「スケジュール済みクラウドフロー(Scheduled Cloud Flow)」を選びます。

• フローに名前を付けます。ここでは「Automated Email Alert(自動メールアラート)」という名前を使用しました。
• アラートを毎日送信したいので、「繰り返し間隔:1日ごと」に設定します。
• 「作成」をクリックします。

ステップ3:Power AutomateとExcelを連携させる

• 最初は「繰り返し(Recurrence)」という1つのステップだけがあります。
• 「新しいステップ(New Step)」をクリックして、他のステップを追加していきます。

• 「アクション」タブから「表内に存在する行を一覧表示(List rows present in a table)」を選択します。見つからない場合は検索ボックスで検索してください。

• 「場所(Location)」のドロップダウンメニューをクリックします。
• 「OneDrive for Business」を選択します。

• 「ドキュメントライブラリ(Document Library)」には「OneDrive」を選択します。

• ファイルの場所まで移動し、対象ファイルを選択します。

• データセットを含むテーブル名を選択します。ここではExcel内の「Table 1」を使用しました。

• 「詳細オプションを表示する(Show Advanced Options)」をクリックします。

• 「フィルタークエリ(Filter Query)」欄に Deadline eq' と入力します。
• 「動的なコンテンツを追加」をクリックすると、フローティングウィザードが開きます。
• 「式(Expression)」タブに移動し、数式バーに以下の数式を入力します。

formateDateTime(convertFromUTC(utcNow(),'Central America Standard Time'),'yyyy-MM-dd')

これにより、ExcelファイルのDeadline(締め切り日)列の値を、中央アメリカ標準時の標準形式にフォーマットしています。

• 最後に '(シングルクォート)で閉じます。

• 「DateTime形式(DateTime Format)」を「ISO 8601」に設定します。

ステップ4:メールアドレスの一覧を抽出する

• 「次のステップ」を選択し、検索ボックスに「select」と入力して、「Select(Data Operation)」をアクション一覧から選択します。

• 「動的なコンテンツを追加」をクリックし、一覧から「Value」を選択します。

• 「Map」オプションを選択した状態で、テキストアイコンをクリックします。

• 検索バーで「Email」を検索し、一覧から「Email」を選択します。

ステップ5:抽出したメール一覧を整理する

• さらに別のステップを追加し、検索バーで「Compose」と検索します。
• 「Compose」を選択します。

• 「Compose」操作が表示されたら、「動的なコンテンツを追加」をクリックします。
• 「式(Expression)」バーに以下の数式を入力します。

union(body('Select'),body('Select'))

• 「OK」をクリックして進みます。

ステップ6:各メールにコントロールを追加する

• 「Control(コントロール)」を選択します。

• 「アクション」タブから「Apply to each(それぞれに適用する)」を選択します。

• 「以前の手順から出力を選択」の下にあるボックスをクリックします。
• 「動的なコンテンツを追加」アイコンを使って「Outputs」を選択します。

ステップ7:各メールのデータを抽出する

• 「アクションの追加(Add an action)」をクリックします。

• 検索バーで「filter array」と検索し、「Filter array(配列のフィルター処理)」を選択します。

• 右側のプラスアイコンをクリックして開いたウィザードから「Value」を選択します。

• 「動的なコンテンツを追加」を選択し、そこから「Email」を選びます。

• 3番目のフィールドでは「Current item(現在の項目)」を選択します。

ステップ8:メールアラート用のHTMLを生成する

• 「アクションの追加」をクリックし、検索ボックスで「Create HTML Table」と検索します。
• 「Create HTML Table」を選択します。

• 「From」フィールドのボックスをクリックし、「動的なコンテンツを追加」アイコンを押します。
• フローティングウィザードから「Body」を選択します。
• 「詳細オプションを表示する」をクリックします。

• 「Columns(列)」フィールドから「Custom(カスタム)」を選択します。

• テーブルに表示したい列の名前を「Header」列に入力します。
• 「Header」の1番目の列には「Deadline」と記述します。
• 「2番目の列」では、「式」バーに以下の数式を入力します。

formatDateTime(item()['Deadline'],'MMM d,yyyy')

これでDeadline列の値が指定した書式で整形されます。

• 次に「Topic」という名前の別の列を追加します。
• この列の値も「式」バーで以下のように設定します。

ステップ9:Outlookでメールを自動作成する

• 「Send an email(V2)(メールの送信V2)」というアクションをもう1つ追加します。

• 「To(宛先)」フィールドをクリックし、「動的なコンテンツを追加」を選択します。
• フローティングウィザードから、一覧の「Current item(現在の項目)」を選択します。

• 「件名(Subject)」フィールドにメールの件名を入力します。
• 「本文(Body)」フィールドでは「動的なコンテンツを追加」をクリックし、一覧から「Output」を選択します。

• 「詳細オプションを表示する」をクリックすれば、高度な設定も選べます。

• 「保存(Save)」を選択します。
• これでフローの準備が完了です。「テスト(Test)」ボタンをクリックして結果を確認できます。

• フローを手動でテストする場合は「Manually(手動)」を選択します。
• 「Outlook」アプリケーションを開いて、自動メールアラートを確認しましょう。

最終結果

• Outlookアプリケーションに届くメッセージのサンプルは以下の通りです。

方法2:VBAを使ってExcelワークシートから自動メールアラートを送信する

続いて、VBAマクロを使用する方法です。クラウド環境が不要なため、ローカルのExcelだけで完結したい場合におすすめです。今日から1日以上7日以内に期限が到来する場合に、自動でメールアラートを生成します。

手順:

• 前述と同じデータセットを使用します。
• 期限が本日から1〜7日以内の場合、自動メールアラートを生成する条件を設定します。

• VBAマクロを開くには、Alt + F11キーを押します。

• 「Microsoft Visual Basic for Applications」ウィンドウが起動します。
• 「挿入(Insert)」タブから「標準モジュール(Module)」を選択します。

• 以下のVBAコードをモジュールに貼り付けます。

Public Sub SendReminderMail()
'Declare the variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    'To enter the text mail, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        If xValDateRng <> "" Then
        'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
        'Means 1 < X< 7, X = Due Date - Current Date
        If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
            'Create the subject, body and text contents with the required variables
            xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
            CrVbLf = "<br><br>"
            xMsg = "<HTML><BODY>"
            xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                .Send
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

• 保存後、F5キーを押してプログラムを実行します。
• 再生アイコンをクリックして実行することも可能です。

• 入力メッセージボックスが表示されます。
• 締め切り日の列を選択します。ここではD5:D10範囲を選択して「OK」を押しました。

• 次の入力メッセージボックスが表示されます。
• メールアドレスの列を選択します。ここではB5:B10範囲を選択して「OK」を押しました。

• 最後の入力メッセージボックスが表示されます。
• 「トピック(Topic)」の列を選択します。ここではC5:C10範囲を選択して「OK」を押しました。

• これで、期限前にすべてのメールがリマインダーとして送信されます。
• また、上記の自動化プログラムでメールを手動送信することもできます。.sendコマンドの前にアポストロフィ(')を付けて無効化すれば、下の画像のように送信前に確認できるようになります。

• 完了した下書きがすべてアプリ上に表示されます。
• 「送信」ボタンをクリックして、任意の相手にメールを送信できます。

練習用ワークブックのダウンロード

関連記事

• Excelでポップアップリマインダーを作成する方法
• Excelで通知やリマインダーを作成する方法
• Excelでアラートを作成する方法
• Excel VBAでアラートを無効化する方法
• Excelで期限リマインダーの数式を設定する方法
• Excelで期限リマインダーを設定する方法

<< Excelのアラートに戻る | Excelを学ぶ