MSAccessからWindows10標準のメールを使う。URLエンコードに苦労したけどExcel関数が使えるの巻。開発17日目

2020/07/012020/07/08

美容院向け顧客管理システムをお休みを使って作っています。ベータテストをしていただいている方から、「前日にお客様にご予約確認のメールを送りたい」とリクエストをいただいたので、実装中。MSAccessでメールを扱うのは初めての経験だったので、いろいろ学びがありました。

Accessでメールを送るには主に3種類の方法あり

１．Outlookオブジェクトを使う

Office製品であるOutlookと連動させる方法で、参照設定の「Microsoft Outlook 16.0 Object Libraryを使います。オブジェクトをAccessで作って使う方法で多分これが正攻法です。

こちらのサイトで非常にわかりやすいサンプルコードを紹介してくださっています。税理士が教えるAccessとExcelで経理会計の仕事を効率的にする方法

難点はOutlookを普段メールクライアントとして使っていないといけないこと。

２．Access単体でメールを送れるDLLを導入する

メールクライアントへ情報をコピーするのではなくて、Access自体にメール機能を組み込むためのDLLはいろいろ存在するようです。「Access VBA メール DLL」のようなキーワードでググるといろいろ出てきます。

参照設定で使えるMicrosoft謹製のものもあるようで、Microsoft CDO for Windows 2000 Library というのがそれみたい。2000か！！サンプルコードは下記のサイトで紹介されていました。

AccessのVBAを使ってメールを直接送信～Microsft CDO編～

これは面白いんだけれど、今回はそんなに件数がない美容院のご予約お知らせメールのようなものなので、定型文を確認しながら送ってほしいなというところもあるのでパス。大量のご送信を起こしそうで自分の技量に自信がないのが主たる理由。

でも、これが使えると、従業員向けに「本日ご予約のお客様一覧」的なものを携帯へ送ってお知らせ、なんて使い方はできそう。

３．古のキーワード mailto: をつかう

いわゆるホームページを作ったことがある人ならご存じでしょ？　ハイパーリンクに　mailto:hoge@hoge.com のようなリンクを設定しておくと、クリックしたときに自動でメールアプリが起動して宛先が設定されるのです。

mailto: 　には、じつは宛先だけではなく、件名や本文も潜り込ませられます。一続きの長い文字列にするだけでオッケーです。しかもメールクライアントは使う必要がありますが、Outlookでもユードラでも手裏剣でも、もちろんWindows10標準のメールでも規定のメーラーを起動させるので汎用性があります。君に決めた！

そして地獄が始まる。エンコードってそういうことか

なんだ楽勝じゃん！とAccessでテストのフォームを作り、ラベルに以下のようなハイパーリンクを設定してみました。

mailto:hoge@hoge.com&subject=こんにちは&body=これはTEST本文です

宛先やタイトルは入るのに本文がなぜか反映せず。クライアントはWindows10標準のメールです。

どうやら日本語の部分をエンコードする必要あり。よくURLに日本語が含まれているときにURLをコピーしてテキストに貼り付けると％がついた長い英数が並ぶことがありますが、あれは文字がエンコードされたもの。とくにWEBなどで使うエンコードはURLエンコードやパーセントエンコーディングと呼ぶようです。

ならばVBAでしてみようではないか。パーセントエンコーディングというものを！

ググります。「VBA パーセントエンコーディング」

なるほど、Scripting Controlというのを使えばよいらしい。サンプルコードを借りてきてテストしてみると、なぜかエラー・・・。

ならば参照設定からせててみると・・・

やはり黄色いところではじかれます。なんで？　ふたたびググります。「createobject(“scriptcontrol") 使えない」

答えはこちらにありました。https://teratail.com/questions/10072

このスクリプティングコントロールは、JavaScriptのコードを解釈、実行するもののよう。WEBで使われる技術なら納得。でも実は64bit環境のVBAではサポートされていないよう。解決法は、「32bitのAccessを入れる・・・」おいおい。

ググったらExcelの情報はたくさん出てくる。おや？まてよ？

URLエンコーディングをググるとExcelの情報は非常にたくさん出てきます。なんと、Excel2013以降では標準関数として「ENCODEURL()」という関数を持っていて、引数に入れるだけでパーセントエンコーディングを吐き出してくれるという神のような存在。

ならばAccessに参照設定でExcelのオブジェクトライブラリを取り込んでやればいいんでは？

そして編み出した究極の変換コードは・・・

Private Sub コマンド36_Click()
    enc_本文 = Excel.Application.encodeurl(txt_本文)
    enc_件名 = Excel.Application.encodeurl(txt_件名)
    str_mailto = "mailto:" & txt_宛先 & "?subject=" & enc_件名 & "&body=" & enc_本文
    Me.HL.HyperlinkAddress = str_mailto
End Sub

なんともあっけなかった。Excel.Application.encodeurl(“変換する文字列")　だけで解決しました。MSAccessのVBAでエンコーディングするためにどれだけの時間を費やしたことでしょう・・・。

追記：なんやかんやでメール画面ができました。

というわけで、あらかじめ用意したヘッダーとフッター、その間に明日以降のお客様のご予約日時を差し込む形のメールを、「セット」ボタン１つクリックで左側のプレビューに表示させ、「メールアプリへ転送」というボタンにリンクづけられたMailto：で既定のメールアプリへ飛ばすという仕組みができました。

ボタンを押すと右側の予約一覧の「確認済日時」にボタンを押した日時を入力。目立つように条件付き書式でオレンジになります。DAOで売上IDがレコードになければ追加。あれば更新という条件分けさせています。

Private Sub btn_送信_Click()
        Dim DB As DAO.Database
        Dim RS As DAO.Recordset
        
        Set DB = CurrentDb
        Set RS = DB.OpenRecordset("DAT_コミュニケーション", dbOpenDynaset)
        
        RS.FindFirst "売上ID = " & Me.txt_売上ID
  
        If RS.NoMatch = False Then
            RS.Edit
        Else
            RS.AddNew
            RS!売上ID = Me.txt_売上ID
        End If
        
        RS!カテゴリ = "メール通知済"
        RS!日付 = Now()
        RS!内容 = Me.txt_本文
        RS!お客様ID = Me.txt_お客様ID
        
        RS.Update
        RS.Close: Set RS = Nothing
        DB.Close: Set DB = Nothing
        
    Me.subform.Requery
End Sub

「セット」を押してもメールアドレス登録していないお客様は「メールアドレスを登録されていないお客様です」と警告だしたり、チェックボックスでリストに表示させないようにしてます。これもフィルターをつかって以下のようにしてます。

Private Sub chk_noMail_AfterUpdate()
    Me.Filter = "Email"
    If Me.chk_noMail Then
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub