Power AutomateでExcelシートのリストからメールを送信する Part1

Power AutomateでExcel行の複数取得とメール送信。

Power AutomateはVBAをこの世から消し去ることを目的としたMicrosoftの新たなソリューションです(誤)。

電子メールは2020年現在、ビジネスシーンでもっとも幅をきかせているツールだと思われます。個人的にはもう時代遅れでMicrosoft365ユーザーならTeamsがあるし、その他のチャットツールも便利なのがたくさんあるので、せめて社内連絡だけでも移行すればいいのにと常々思っているのですが、残念ながら世界の最先端からは10周遅れで繰り上げスタートしているのが、日本の会社の平均的なITリテラシーの実情ですね。

メールのあの最初と最後に定型句をいれなきゃ失礼みたいな風習とか、全く意味をなさない添付ファイル暗号化zipとか大嫌いなんですよねぇ。まぁ私は経営者ではないので導入コストとか考えないで好き勝手言えるってのはありますが。

どうでもいい話はこれくらいにして
メールを送って返信を待つというシチュエーションはよくあります。Power Automateを使えば、メール受信をトリガーにフローを開始できることは以前の記事で実証されました。そこで今回はメール送信の自動化に挑戦します。

準備

Excelに次のようなテーブルを作成しました。

pam_mail_sent1.png

一目瞭然ですが、メール送受信管理簿です。機能は次のようになります。

  1. 返信があればreplyに○をつけます。
  2. deadlineを超過してreplyに○がなければstatusNGとなります。
  3. statusがNGになっている対象者のmailへ督促メールを送信します。
  4. 督促メールを送信したならばdemandに○をつけます。

以上を本来ならメーラーとExcelをにらめっこしながら手打ちするかVBAでやるところですが、今回はPower Automateに全自動でやらせます。

上のExcelファイルをOneDrive上へ保存します。ファイル名はmail_manager.xlsx、テーブル名はmail_listです。ルート配下のauto_mailフォルダへ配置しました。

pam_mail_sent2.png

メール受信記録フロー

1.の処理については、以前の記事の方法で簡単にできますので、詳しくは省略します。フローのステップとパラメーターは次のようにします。

pam_mail_sent3.png

これで対象者からメールを受信したらreplyに○がつきます。このフローを保存します。

理由は後述しますが、画像ではメール受信にOutlookを使用していますが、Gmailが使えるならGmailにした方が良いです。

statusをNGに切り替える処理はワークシート関数と条件付き書式を使用していますのでフロー側では何もしません。

返信状況確認フロー

次にstatusを確認してNGなら対象者にメールを送信するフローを新たに作成します。注意点として先のフローとは別のフローから同じファイルを参照することになりますが、このような使い方はMicrosoftは動作保証をしていません。主に処理の競合が問題となるからですが、今回のような同時アクセスが起こりにくい緩い条件なら大丈夫でしょう。気にせずやります。

このフローは定期的に実行したいので、メニューの作成からスケジュール済みクラウドフローを選択します。

pam_mail_sent4.png

スケジュールを設定する画面になります。

pam_mail_sent5.png

誰もメールを送ってこなそうな真夜中に毎日実行で設定しておきました。これでトリガーイベントが指定した時間に実行されます。

作成ボタンを押すと、トリガーが設定された状態でフローの編集画面になります。

Excelの複数行を取得するフロー

フローの編集画面で新しいステップボタンでステップを追加します。今回はstatusがNGの行をすべてピックアップする必要があり、以前使用した行の取得ではダメです。行の取得アクションでは絞り込み結果が複数ある場合、最初の1行しか取得できません。

そこで今回は表内に存在する行を一覧表示アクションを使います。

pam_mail_sent6.png

このアクションではテーブル内の検索条件に一致した行をすべて取得できます。検索条件は「完全一致」「前方・後方一致」「含む」等あらかた設定できます。

ただしこの条件=フィルタークエリといいますが、フィルタークエリは書き方にややクセがありますので、この後説明します。

アクションを選択すると次のようなパラメーターの設定画面になります。

pam_mail_sent7.png

ファイル、テーブルは今まで通りですのでちゃっちゃと設定します。選択肢から選ぶだけです。

以下にいろいろ小難しい設定項目がありますが、とりあえずはフィルタークエリだけ使えれば事足りるので、その他は見なかったことにします。

で、フィルタークエリですが、テキストボックス内のヒントに書いてありますが、ODATAという決まりに従って書く必要があります。ODATAとは何なのか、から始めると長くなりそうだし私もよく知らないので置いといて、使いそうなフィルタークエリの書き方を抜粋すると次のようになります。

検索条件書き方
完全一致列名 eq 値city eq 'さいたま'
部分一致contains(列名,値)contains(city,'いた')
前方一致startsWith(列名,値)startsWith(city,'さい')
後方一致endsWith(列名,値)endsWith(city,'たま')

列名が日本語の場合でも列名にはクォーテーションを付けないで書きます。

では、statusがNGの行だけを取得するためのフィルタークエリを設定します。完全一致でいいので次のようになります。

pam_mail_sent8.png

これで、フィルタークエリにより絞り込まれた行だけが取得されるはずです。

フローのテスト

フィルタークエリを設定したら、それが本当に期待通りに動作しているのか確認したいですよね?実際にトリガーから実行された結果を見る方法もありますが、今回のように1日1回のトリガーを待つのは大変です。テストのためにいちいち設定時間を変更するのも面倒です。

そのような場合に役に立つのが、フローのテスト機能です。

フロー編集画面の右上にテストメニューがあります。

pam_mail_sent9.png

クリックするとメニューが展開して、テスト方法を選択できます。

手動を選択して、テストボタンを押します。

pam_mail_sent10.png

続けて表示されるフローの実行ボタンを押すとトリガーに関係なく即時にフローを実行することができます。またフローの途中経過を詳細に確認することもできます。概ねVBAのデバッグ実行にあたると思っていただければよろしいです。

ためしに次のテーブルの状態でここまでのフローを実行してみましょう。

pam_mail_sent11.png

フローが開始されると次のような画面になります。実行結果を確認するにはフロー実行ページへのリンクをクリックします。

pam_mail_sent12.png

フロー実行結果の詳細が表示されます。緑のチェックマークは正常に完了したステップ、エラーの場合は赤いびっくりマークがつきます。

ステップをクリックすると、そのステップでどのような処理がおこなわれたか確認できます。

表内に存在する行を一覧表示のステップを展開すると出力データを見ることができます。出力のところにあるクリックしてダウンロードのリンクをクリックします。

pam_mail_sent13.png

出力の生データを見ることができます。データはJSONでブラウザによって表示のされ方は違いますが、FireFoxの場合は次の画像のように自動整形されます。

valueがテーブルから取得されたフィルタークエリの結果です。

pam_mail_sent14.png

ちょっと細かくて見づらいですが、問題なく取得されていますね。

フローで総当たり処理

前段のステップで取得した行データのすべてに対してアクションを実行していくにはApply to Eachコントロールを使用します。

新しいステップボタンからコントロールを選択します。

pam_mail_sent15.png

一覧からApply to Eachを選択します。

pam_mail_sent16.png

Apply to EachはVBAでいうところのFor Eachに相当します。コレクションの要素を1つずつ取り出していくことができます。

選択すると、対象となるコレクションをパラメーターとして指定する必要があるので、前のステップから引き継がれた候補からvalueを指定します。

ついでにデフォルトだとステップ名がApply to Eachになり、何をしているのかわからないので、適当に名前を変えておきましょう。ステップ名の変更はステップの右の[・・・]からできます。

pam_mail_sent17.png

つづけてアクションの追加をクリックします。

ここでOutlook.comのメール送信アクションを選択したいところですが、やってみたらエラーで送信できません。

なんでかなとOutlook.comを見てみると、何やら「自分、自動プログラムで迷惑メールを送信しようとしとらんかぁ?そうはさせんでぇ!送信したいならサインインして認証せぇや!」というメールが来ていて、サインインのリンクをクリックしましたが何も起こりません。

pam_mail_sent21.png

いくらやってもダメなのであきらめました。Power Automateを迷惑メールボット扱いとはMicrosoftさん、なかなか面白いギャグです。

Gmailにしたらすんなり送れました。なのでGmailでやります。初めてGmailを接続する場合は、Googleアカウントでログインする必要があります。

Gmailのメール送信アクションでそれぞれのパラメーターを設定します。

メールアドレスはmail列に格納されているので、宛先(バグって終了とかいう表示になってますが・・・)にはmailを指定します。その他は好きにしましょう。次のようにしてみました。

pam_mail_sent20.png

サンプルのアドレスはデタラメなので、マジもののアドレスに書き換えてテスト実行してみると次のようなメールが届きました。

pam_mail_sent22.png

もちろんもう一人のNGさんのアドレスへもメールが来ています。

これで一連のフローは完成ですが、この状態では返信が来るまで毎日同じ督促メールが送信されることになります。

期限をぶっちぎる奴らにはそれでもいいとは思うのですが、あまりに頻繁だとオオカミ少年状態になりかねないので、1回送ったらそれ以上は督促しないようにしてあげます。

そのための処理4.を作成しますが、長くなったので、次回に続きます。

続きはこちら

related pages
Excel-VBAでクラスを使って機能を拡張する
Workbookオブジェクトに機能を追加して便利に。

どの言語にも何回も登場する定番のコードというのがあります。はたらきたくないという思いに関しては誰にも負ける気がしない私は、この何回も同じことを書くという行為が嫌で何とかできないかと試行錯誤してきました。たどり着いたひとつの答えがクラスを使って機能を定義しておくという方法です。

Read More ...
Excelに最適な画面解像度はどれか
Full HDを表示できないモニターは窓から投げ捨てろ!

結論。WQHD(2560×1440px)以上の解像度でFull HD相当まで拡大表示。 私は4kモニターをデュアルで175%拡大表示にして使っています。最強です(自慢)。

Read More ...
Excelの表へ行をシステマチックに挿入する
すでにある表に規則的に行を挿入します。

Excelシートに作成した表にあとから、例えば1行おきに列を挿入するというシチュエーションありませんか?えっ?ないですか?私はありました。実際にやってみてください。10行やそこらの表ならたいした手間ではないですが、これが100行ともなるとかなりハードです。こんな単純作業の繰り返しはVBAにやらせましょう。

Read More ...
この記事の
作成日

2020-12-23

更新日

2021-05-07

ページ内検索
目次
WEB MASTER
さいた
神エクセル撲滅協会理事(自称)
さいたま市民 埼玉こそ地上の楽園