中小企業情シス稼業

2社連続で中小企業ひとり情シスやっています。 同じ境遇の方のお役に立てればと思います。

広告

広告

スプレッドシートを読み取って個々に別内容のメールを送信する簡単な方法

young-woman-blowing-kiss-to-rubber-duck

これまで新システムのパスワードを社員に知らせるなどの目的で、
Excel VBAでCDO(Microsoft Collaboration Data Objects)を呼び出して、個々の社員に別内容のメールを送るということをやっていました。


2022年10月1日に、Exchange Onlineでの基本認証が廃止されることになりました。
CDOを使うやり方が継続できるか怪しくなり、代替手段を探す必要が出てきました。

Exchange Online での基本認証の廃止


Power Automateで代替できます。
Googleスプレッドシートの内容を読み取り、Microsoft365で個々の社員に別内容のメールを送る例をご紹介します。

Google スプレッドシートでの準備作業

まずはGoogleスプレッドシートからです。
メール送信したい内容を入力する器になります。

※Googleアカウントを持っていない場合は、取得してください。
Google アカウントの作成


Googleドライブを開きます。
GS1


画面左上「新規」をクリックし、
「Google スプレッドシート」をクリックします。
screenshot_03

screenshot_02

ファイルに名前をつけて、メール送信したいデータ項目名を1行目に入力します。
※後でPower Automateから参照するため、わかりやすいファイル名・項目名にします。
※以下キャプチャの右端にある「__PowerAppsId__」は入力不要です。Power Automateが自動で追加します。
screenshot_01

これでGoogle スプレッドシートでの準備作業は完了です。

Power Automateでの準備作業

次はメール送信してくれるPower Automateでの作業です。

Microsoft365のタイルから、「Power Automate」をクリックします。
screenshot_01


「作成」をクリックし、
「スケジュール済みクラウドフロー」をクリックします。
screenshot_02


「フロー名」を入力します。(後から変更可能です)
「繰り返し間隔」を「12」「月」にします。(後から変更可能です)

※「1分」など短い時間を指定し、「スプレッドシートにデータ入力して1分待てばメール送信」のような形にすることもできます。ただあまり頻度を高くするとエラーが記録されます。肝心な時に動かないのも怖いので、私は頻度を低くして手動で実行しています・・・

「作成」をクリックします。
screenshot_03


以下の画面が出た場合、Googleにサインインし、「続行」をクリックします。
PA-4

Power Automateのメイン部分設定

ここからPower Automateのメイン部分の処理を設定します。

以下5つのステップで構成します。


・1.Googleスプレッドシートのデータを読み込む
・2.添付ファイルの情報を読み込む

・3.添付ファイル本体のデータを読み込む
・4.メールを送る
・5.
Googleスプレッドシートのデータを削除する

※今回は一例として、OneDrive for Business上のファイルを添付します。
添付ファイル不要な場合は、2と3の設定をスキップしてください。


・1.Googleスプレッドシートのデータを読み込む

「新しいステップ」をクリックします。
PA_01

「google」で検索し、
「Google Sheets」をクリックします。
PA_02

「複数行を取得」をクリックします。
PA_03

フォルダアイコンと下向き三角をクリックし、
Googleドライブ内のファイルとシート名を指定します。
「新しいステップ」をクリックします。
PA_04


・2.添付ファイルの情報を読み込む

「ファイル」で検索し、「OneDrive for Business」をクリックします。
PA_05

「ファイル メタデータの取得」をクリックします。
※ファイル名とファイル本体は別々に取得する必要があります。面倒ですね・・・
PA_06

フォルダのアイコンをクリックし、OneDrive上の添付ファイルを指定します。
「新しいステップ」をクリックします。
PA_07


・3.添付ファイル本体の情報を読み込む

「ファイル」で検索し、「OneDrive for Business」をクリックします。
PA_08

「ファイル コンテンツの取得」をクリックします。
PA_09

フォルダのアイコンをクリックし、OneDrive上の添付ファイルを指定します。
「新しいステップ」をクリックします。
PA_10


・4.メールを送る

「コントロール」をクリックします。
PA_11

「Apply to each」をクリックします。
PA_12

「以前の手順から出力を選択」項目をクリックし、
「動的なコンテンツ」から「複数行を取得」内の「レコード value」をクリックします。
「アクションの追加」をクリックします。
PA_13

「メール」で検索し、「Office 365 Outlook」をクリックします。
PA_14

「メールの送信 (V2)」をクリックします。
PA_15

「宛先」をクリックし、「動的なコンテンツの追加」をクリックします。
PA_16

「複数行を取得」内の「送信先メールアドレス」をクリックします。
PA_17

同様に「件名」「本文」を「動的なコンテンツ」から指定します。
※以下はIDとパスワードを個別に知らせる例です。Wordの差し込み印刷のように、本文の中に変動する部分(Googleスプレッドシートの内容)を差し込むことができます。
「詳細オプションを表示する」をクリックします。
PA_18

「添付ファイル 名前」をクリックし、
「動的なコンテンツ」から「ファイル メタデータの取得」内の「名前」をクリックします。
※添付ファイルをつけない場合はこの設定は不要です。
PA_19

「添付ファイル コンテンツ」をクリックし、
「動的なコンテンツ」から「ファイル コンテンツの取得」内の「ファイル コンテンツ」をクリックします。
※添付ファイルをつけない場合はこの設定は不要です。
PA_20

「新しいステップ」をクリックします。
PA_21


・5.
Googleスプレッドシートのデータを削除する

「コントロール」をクリックします。
PA_22

「Apply to each」をクリックします。
※さっきとはまた別の「Apply to each」です。
PA_23

「以前の手順から出力を選択」をクリックし、
「動的なコンテンツ」から「複数行を取得」内の「レコード value」をクリックします。
「アクションの追加」をクリックします。
PA_24

「google」で検索し、「Google Sheets」をクリックします。
PA_25

「行の削除」をクリックします。
PA_26

先ほど同様、Googleスプレッドシートのファイルとシート名を指定します。
「行 ID」をクリックし、
「動的なコンテンツ」から「複数行を取得」内の「__PowerAppsId__」をクリックします。
※「複数行の削除」というアクションがないので、1行ずつ削除するアクションをループで回す形になっています。行の特定に「__PowerAppsId__」を使っています。
PA_27

「保存」をクリックします。
PA_28

これでPower Automateでの準備作業は完了です。

※設定の全体像は以下になります。
(いくつか上記と違う部分があります。ご了承ください)
PA-all


※2022.9.29追記
上記のやり方では、添付ファイルなしの場合と添付ファイルありの場合の2つのフローが必要でした。
Googleスプレッドシートに添付ファイルへのOneDriveのパスを入力するようにし、このセルの値で処理を分岐させることで、フローを1つにしました。
以下をご覧ください。
Power Automateで空白を判定して処理を分岐させる一例

メール送信

実際にメール送信します。

0.Power Automateのフローがオフの場合は、オンにします。
screenshot_29

1.Google スプレッドシートに送信したいデータを入力します。
screenshot_31

2.Power Automateフローを実行します。
screenshot_30

3.
Google スプレッドシートに__PowerAppsId__」列が自動で追加され、その下に長い文字列が表示されます。
Power Automateから認識された印にもなります。
screenshot_33

4.Power Automateの処理が完了すると、
Google スプレッドシートに入力したデータ行が削除されます
screenshot_32

5.宛先にメールが届きます。
screenshot_34

Power Automateフローの初回実行は時間がかかります。(10分くらい?)
「動かない!」と焦らず、気長にお待ちください。
1度動けばその次からはサクサクと動きます。

実際にメール送信する前には、必ず自分のメールアドレス宛などに送信テストをしてください。
間違ったデータを大量に送信すると、問い合わせが殺到しますので…


Googleスプレッドシートのデータが自動で削除されない場合は、
Power Automate側でエラーが出ているので修正します。

エラー表示されず、ずっとリトライし続けることも多いです。
そうなると地味~なトライ&エラーが必要です。これが辛い・・・



以前は個別にパスワードを知らせる用途もありましたが、最近はシステムからリセットメール送信し、各個人でパスワード決めてね、という形が多いかもしれません。

ただこの記事はPower Automateの練習にはなると思います。

Power Automateはちょっとしたことで動かなくなるので、
試行錯誤する際は「名前を付けて保存」でフローを複製しておくのをお忘れなく。

VBAからCDOを使用してメール送信時にサーバ接続エラー

woman-closes-her-eyes-while-journalling-in-a-notebook

CDO(Microsoft Collaboration Data Objects)を使うと、
裏でOutlookのアプリを使わないで、VBAでMicrosoft365等から直接メールを送れます。


※2022.09.12追記
2022年10月1日に、Exchange Onlineでの基本認証が廃止されることになりました。
CDOを使うやり方が継続できるか怪しくなります。
代替手段としては以下がありますので、よろしければご参照ください。

関連記事:
スプレッドシートを読み取って個々に別内容のメールを送信する簡単な方法



ソースは↓みたいな感じです。
※詳細はネット上にたくさん情報あります。

Set objMessage = CreateObject("CDO.Message")
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "aaa@contoso.com"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxxxxxx"
objMessage.Configuration.Fields.Update
objMessage.Send

社員一人一人に、パスワードなど個別に知らせる必要がある情報を送りたいときに使っています。
メールを1件ずつ手で送るなんて悲しすぎます・・・

エラー発生

このマクロをふと実行してみると

実行時エラー '-2147220973 (80040213)':
転送においてサーバーに接続できませんでした


のエラーが・・・

CDOエラー

これがないと手で一人一人にメールを送らないといけない。辛い・・・

エラー解消

ポート番号を変えてみたり、
セキュリティ絡みのパラメータを変えてみたり、
「Microsoftめこっそり使えなくしたのか」と恨み言を言ってみたりw

1時間半くらい悩みました・・・





VPNで社内環境に接続したらあっさり送信できました。

そういえば今まで社内からしか実行したことがありませんでした。
在宅勤務で初めて実行した気がします。

在宅勤務もシステム環境としては大きな変化ですもんね。

意外とハマりポイントな気がするので、共有させていただきます。



Microsoftは会社のIPアドレスを把握して、ちゃんとチェックしているということなんですね。
確かにアカウントとパスワード漏洩したら、全然関係ない人がメールを送信できちゃいますからね。

会社が移転してグローバルIPアドレスが変わり、すぐこのマクロを使おうとするとエラーということなんですかね。

Salesforceフローでユーザ設定を別のユーザにコピーする

the-air-show-streams

Salesforceってユーザのコピー(複写)ができない気がします。
IDaaS的なやつならできるのかな?そんな高級なものは弊社にはなく・・・

設定項目が多いと、GUIから大量にやるのは萎えます。
弊社は20個近く設定項目があります・・・

なのでSalesforceのフローでやってみました。クセがすごかったです!

※コピー先のユーザは、必須項目だけ入力して作成済みとします。


フローの概要

画面フローを使います。
1

ユーザ指定

設定のコピー元のユーザとコピー先のユーザを画面で指定できる画面を作ります。

画面のメインはルックアップコンポーネントです。
ユーザを選択できるようにします。


ルックアップコンポーネントは2つ置きます。
1つ目でコピー元のユーザを指定します。

何故かルックアップコンポーネントの参照先として、
「ユーザ」オブジェクトを直接指定できません。
「取引先」等、「ユーザ」オブジェクトを参照しているオブジェクトを指定する必要があります。

また「取引先」オブジェクトの場合、「取引先の所有者」項目は指定できません。
それ以外の「ユーザ」オブジェクトを参照している項目を指定する必要があります。
こういうハマりポイントが多く、Salesforceは泣きそうになります・・・

API参照名は後で使うので、わかりやすく「①」と表示しています。
実際には自由に名前をつけてください。
2


2つ目のルックアップコンポーネントでコピー先のユーザを指定します。
オブジェクトと項目は、1つ目のルックアップコンポーネントと同じで大丈夫です。

API参照名は後で使うので、わかりやすく「②」と表示しています。
実際には自由に名前をつけてください。
3

コピー元ユーザのレコードを取得

ユーザ指定画面でコピー元ユーザのレコードを持ってきてくれれば良いのですが、
IDしか取れない模様です。
仕方がないのでIDを基にレコードを取りに行きます。

※もっとシンプルなやり方があればお教えいただけるとありがたいです・・・


取得するレコードがあるオブジェクト:ユーザ
レコードの絞り込みを行う項目:ID
レコードの絞り込みを行う項目と一致する値:①のID

API参照名は後で使うので、わかりやすく「③」と表示しています。
実際には自由に名前をつけてください。
4

コピー先ユーザのレコードを更新

いよいよ処理のメイン部分です。
取得したコピー元ユーザのレコードを利用して、コピー先ユーザのレコードを更新します。

更新するレコードがあるオブジェクト:ユーザ
レコードの絞り込みを行う項目:ID
レコードの絞り込みを行う項目と一致する値:②のID
レコードに設定したい項目:ユーザオブジェクトの更新したい項目
レコードに設定したい値:③で利用したい項目

※レコードに設定したい項目は、複数指定できます。
5

終了メッセージ

処理が終わったか不安になるので、メッセージ画面を表示します。

※ポップアップが表示できれば良いのですが、やり方がわかりませんでした。
これも簡単なやり方があればお教えいただけるとありがたいです・・・

6



色々やり方はあると思いますが、ラクできればなんでもいいです。

広告
広告
プロフィール
40過ぎて何とか結婚し、2015年末に子どもができた、左利き初老オヤジです。日本史を再勉強中。株、囲碁、鼻炎、眼振少々。中小企業ハッタリテキトー情シス(社内SE)。Access/ExcelVBAしか武器(?)はなし。情試はPM/SM/SA/NW。 ベンダーSEを数年やり、その後情シスに転職しました。 情シスでは中小企業ばかり3社見てきました。現職と前職ではひとり情シスです。 会社では「いないとヤバいが、評価はしない」という扱いです。 ノウハウを伝える相手もいないので、せっかくなので自分なりのコツを公開したいと思います。 同じような環境で苦労されている方のお役に立てればと思います。 「情シスの格を上げる」が目標です。 Twitterをフォローいただくと、ブログ更新時に通知されます。http://twitter.com/suiton_everyday よろしくお願いいたします。
お問い合わせ
お問い合わせはこちら
プライバシーポリシー
・当サイトに掲載されている広告について
当サイトでは、第三者配信の広告サービス(Googleアドセンス)を利用しています。
このような広告配信事業者は、ユーザーの興味に応じた商品やサービスの広告を表示するため、当サイトや他サイトへのアクセスに関する情報 『Cookie』(氏名、住所、メール アドレス、電話番号は含まれません) を使用することがあります。
またGoogleアドセンスに関して、このプロセスの詳細やこのような情報が広告配信事業者に使用されないようにする方法については、こちらをクリックしてください。

・当サイトが使用しているアクセス解析ツールについて
当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。
このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。
このトラフィックデータは匿名で収集されており、個人を特定するものではありません。
この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。
この規約に関して、詳しくはこちら、またはこちらをクリックしてください。