汎用的なツールで作るDB→Excel→メール配信の自動システム：Windows環境でちょっとした定型業務を自動化する（2/2 ページ）
データベースから抽出したデータをExcelブックのレポートにまとめてメールで配信する。よくある業務だが、自動化するにはどうすればよいのか？ 筆者が実際に作成して現在も運用中のシステムを例に、幅広く応用しやすい汎用的なツールによる自動化手法を説明する。
●記録したマクロを編集する
記録したマクロを編集するには、VBAのエディタを起動できるようにする。まず［ファイル］タブ−［オプション］をクリックして「Excel のオプション」ダイアログを開く。Excel 2010以降であれば、［リボンのユーザー設定］を選んで、［メイン タブ］−［開発］にチェックを入れてオンにする。Excel 2007であれば［基本設定］−［[開発] タブをリボンに表示する］にチェックを入れてオンにする。
次に［開発］タブ−［Visual Basic］をクリックし、左上の「プロジェクト - VBAProject」ペインから、［VBAProject （＜保存したマクロ有効ブック＞）］−［標準モジュール］とツリーを展開し、［Module1］をダブルクリックすると、記録したマクロが編集可能な状態で表示される。
VBAの編集画面の例
リボンの［開発］タブ−［Visual Basic］をクリックすると、この画面が現れる。
（1）マクロを保存したブック。
（2）ここに記録したマクロが保存されている。ダブルクリックすると右側にソースが表示されて編集できるようになる。
ここからは、マクロが正しく実行されるように、テストしながらマクロを修正していくことになる。例えば、マクロ記録時とは行数の異なるCSVファイルを読み込ませたり、Excelのウィンドウ・サイズを替えたりしながら繰り返し実行し、正常な結果が得られるか確認する。VBAそのものについてはExcelのヘルプや専門の解説書籍などを参照していただきたい。
1点だけセキュリティ上の注意点を記しておく。記録・保存したマクロを初めて実行する際、「マクロが無効にされました」というセキュリティ警告が表示される。ここで［コンテンツの有効化］あるいは［マクロを有効にする］をクリックすると、該当マクロに関しては以後この警告は表示されなくなる。ただし、ブックのファイル名を変更したりすると再び表示され、自動処理が停止する原因にもなるので注意したい。
●バッチ・ファイルからExcelマクロを呼び出す
Excelブックを作成・整形・保存するマクロは、自動化のためにバッチ・ファイルから呼び出す必要がある。しかし、バッチ・ファイルから直接Excelマクロを実行させることはできない。ここではバッチ・ファイルとExcelの間にVBScriptを挟んでマクロを呼び出す方法を紹介しよう。
バッチ・ファイルからは、次のようにExcelマクロ起動用VBScriptファイル「ExecExcelMacro.vbs」を実行する。
cscript //Nologo ExecExcelMacro.vbs /macroBook:"%BaseDir%¥Macro-MakeSheets.xlsm"
/macroName:"AutoExecSubsidySheet" /inputFile:"%BaseDir%¥CSV¥subsidy.csv"
/outputFile:"%BaseDir%¥ExcelBook¥subsidy.xls" /pubDate:%PubDate%
| オプション | 意味 |
|---|---|
| /macroBook | 実行するマクロを保存してあるExcelブック・ファイル名 |
| /macroName | 実行するマクロの名前 |
| /inputFile | 入力ファイル（テキスト整形を終えたCSVファイル） |
| /outputFile | 出力ファイル（Excelブック・ファイル） |
| /pubDate | 入力ファイルに記載されているデータの最終更新日 |
| 上記コマンドラインの各オプションの意味 | |
ExecExcelMacro.vbsの内容を以下に記す。バッチ・ファイルから指定されたファイル名やオプションに従ってブックを開き、マクロを呼び出している。
Dim xlApp
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False ……Excelを表示しない（キーやマウスの操作を反映させないため）
Set WshArguments = WScript.Arguments
Set WshNamed = WshArguments.Named ……コマンドラインで指定された引数を参照できるようにする
If WshNamed.Exists("inputFile") Then ……オプション指定の有無を確認
xlApp.Workbooks.Open WshNamed("macroBook") ……マクロを含むExcelブックを開く
xlApp.Run WshNamed("macroName"), WshNamed("inputFile"), WshNamed("outputFile"), WshNamed("pubDate") ……マクロの実行
xlApp.ActiveWorkbook.Close False ……Excelブックを閉じる
xlApp.Quit ……Excelを終了する
Set xlApp = Nothing
End If
呼び出されたマクロの側では、次のように記述することでバッチ・ファイルから指定した引数を受け取る。
Public Sub AutoExecSubsidySheet(inputFile As String, outputFile As String, pubDate As String)
Workbooks.Open Filename:=inputFile ……CSVファイルを開く
Application.Run "MakeSubsidySheet", pubDate ……体裁を整えるマクロ本体を呼び出す
Application.DisplayAlerts = False ……確認メッセージの表示を止めることで自動処理の中断を防止する
ActiveWorkbook.SaveAs Filename:=outputFile, FileFormat:=xlExcel8, ReadOnlyRecommended:=True, CreateBackup:=False ……xls互換フォーマットかつ読み出しのみ推奨、バックアップ・ファイルなしで保存
Application.DisplayAlerts = True
ActiveWorkbook.Close ……保存したブックを閉じる
End Sub
どうやってExcelブックを添付したメールを送信するか？
Excelブックを自動で保存できるようになったら、次はそれをメールに添付して送信する処理を自動化する。それにはコマンドラインからメールを送信できる「SMAIL」というツールを利用する。
- ダウンロード・SMAIL
※商用利用の場合は、あらかじめ作者に事前連絡が必要。詳細はアーカイブに同梱のreadme.txtを参照していただきたい
SMAILを利用するには、最初に送信用メール・サーバとの接続をセットアップする必要がある。その手順は「Wgetとメールを使ったお手軽サーバ死活監視 − どうやって障害発生を知らせる？」で解説しているので参照していただきたい。
さて、作成したExcelブックは複数の顧客に送信する必要がある。そのため受信者のメール・アドレスはもちろん、本文の冒頭には宛名として顧客の会社名や名前を記さなければならない。これを受信者ごとに実行するために、まずは「＜メール・アドレス＞,＜宛名＞」という並びの受信者一覧のファイルを用意する。
customer01@example.jp,株式会社１２３カンパニー 鈴木様
customer02@example.com,株式会社ＡＢＣカンパニー 佐藤様
customer03@example.net,ＺＹＸ株式会社 マーケティング部御中
（以下略）
バッチ・ファイル内では、forコマンドを利用して上記の受信者一覧ファイルからメール・アドレスと宛名を読み出し、SMAILのオプションに指定しながら顧客ごとに1通ずつSMAILを実行して送信する。
recipients.txt： 受信者メール・アドレスと宛名の一覧
honmon-header.txt： 本文冒頭の定型文（「お世話になっております」など）
honmon-footer.txt： 本文末尾の定型文（署名など）
%PubDate2%、%PubDate3%： データを集計した年月日を設定した環境変数
REM ----- メール本文の共通部分を作成する
type honmon-header.txt > honmon-temp.txt
echo %PubDate3%集計の自治体別補助金提供状況をお知らせいたします。>> honmon-temp.txt
echo 添付のExcelブック「subsidy.xls」をご覧ください。>> honmon-temp.txt
type honmon-footer.txt >> honmon-temp.txt
REM ----- 顧客ごとに1通ずつメールを送信する
for /F "tokens=1,2* delims=," %%i in (recipients.txt) do @(
echo %%j>destination.txt
copy /b destination.txt + /b honmon-temp.txt /b honmon.txt ……宛名を本文先頭に挿入してhonmon.txtに保存
"C:¥Program Files (x86)¥SMAIL¥smail.exe" -i -hmailserver.exmaple.jp -ftaiyo-sender@example.jp -s"【太陽生活】自治体別補助金提供状況（%PubDate3%集計）" -Fhonmon.txt -asubsidy.xls %%i,BCC:taiyo-sender@example.jp
……メール・サーバと送信者メール・アドレス、サブジェクト、本文テキスト・ファイル、添付ファイル、受信者とBccのメール・アドレスをそれぞれ指定して、メールを送信
)
以上で各処理の自動実行をすべて説明した。バッチ・ファイルは以下からダウンロードできる。
どうやって月1回のレポート作成・送信を自動化するか？
最後に、一連の処理を定期的に自動実行する方法を説明しよう＊1。基本的には「Wgetとメールを使ったお手軽サーバ死活監視 − どうやって自動化する？」で解説している手順で、Windowsのタスク・スケジューラに自動実行用バッチ・ファイルのタスクを登録する。以下、この記事との相違点だけ触れておく。
＊1 ここでは説明を簡単にするために一連の処理を一度に実行している。だが実際には、突発的なトラブルでExcelブックの作成に失敗することもあるだろう。それがそのままメール送信されないよう、Excelブック作成までのタスクとメール送信のタスクに分割し、手動でExcelブックの内容を確認してからメール送信が行われるように調整した方が、より安全・確実である。
月に1回実行するためのタスク・スケジュール
この画面はタスクの新規作成ウィザードの途中か、あるいは作成済みタスクのプロパティの［トリガー］タブから開くことができる。
（1）月に1回実行するには、これを選ぶ。
（2）ここをクリックして＜すべての月を選択＞を選ぶと毎月1回実行される。
（3）毎月の実行日は、「11日」あるいは「第2水曜日」のいずれかの形式で指定できる。
特に注意していただきたいのは、対話的ログオンをしていないときでもタスクを実行する場合である。Excelによる処理をタスクで自動実行する場合、対話的ログオンなしで起動しようとすると通常はExcelの起動に失敗してしまう。しかし、あまりお勧めできない設定も含まれるものの、筆者が試した限りでは次のように設定すると、対話的ログオンをしなくても実行できるようになった。
- 管理者アカウントで該当タスクを起動する
- タスクのセキュリティ・オプションで［最上位の特権で実行する］チェック・ボックスをオンにする。
- あらかじめ次のフォルダを作成しておく：
32bit版Windows OSの場合： %SystemRoot%\System32\config\systemprofile\Desktop
64bit版Windows OSの場合： %SystemRoot%\SysWOW64\config\systemprofile\Desktop
本稿のバッチ・ファイルを自動実行するために必要なセキュリティ・オプションの設定
本稿のようにExcelの自動処理を含む場合、ログオンしていないときでも実行するには、次のようにセキュリティ・オプションを設定する必要がある。
（1）試した限りでは、ローカルAdministratorsグループに所属するユーザー・アカウントを指定する必要があった。
（2）これを選んで、ログオフ時でも自動実行されるようにする。
（3）管理者の持つ特権の下でバッチ・ファイルを実行するために、これにチェックを入れてオンにする。
ただ、いうまでもなく管理者権限でむやみにタスクを実行することは、セキュリティ上の危険性が高まる。常時稼働しているからといってサーバ・マシンで実行するのは避けた方がよいだろう。またマイクロソフトによれば、対話的ログオンなしでOfficeアプリケーションを自動実行すると、対話処理によって自動実行が中断したり多重実行に失敗したりすることがあるので推奨もサポートもしていない、とのことだ。
- Office のサーバーサイド オートメーションについて（マイクロソフト サポート技術情報 KB257757）
対話的ログオンをせずに該当タスクを実行する場合は、上記のようなリスクや制限を踏まえていただきたい。
以上、データベースから抽出したデータをExcelブック形式でメール配信する処理を自動化する方法について説明した。汎用的なツールを組み合わせた自動処理では、バッチ・ファイルやスクリプトを書いたりツールのオプション指定に試行錯誤したりと、面倒なことも多い。ただ、いったん手法を確立すれば、別の自動処理にも適用しやすい。実際、本稿でもメール送信については、以前の「Wgetとメールを使ったお手軽サーバ死活監視」で解説した手法をほぼ踏襲できた。こうした積み重ねが業務の効率向上につながっていくと筆者は考えている。
冒頭にも記したように本稿で紹介したテクニックがベストな解というつもりはない。「もっと簡単な方法がある」「こっちの方がセキュリティ面でも安心だ」といったご意見・提案があれば、ぜひすぐ下にあるSNSボタンからつぶやきや書き込みをお願いしたい。それがほかの読者の方々に役立つ情報になるだろう。
「運用」
Copyright© Digital Advantage Corp. All Rights Reserved.