VBAスクリプトでExcelスプレッドシートからメールを送信する方法

Microsoft Excelからメールを送信するのは、実はほんの数行の簡単なスクリプトだけで実現できます。この機能をスプレッドシートに追加すれば、Excelでできることの幅が大きく広がります。

これまで、プログラミング知識がなくてもVBAスクリプトと同等の処理ができる便利なExcelマクロを数多く紹介してきました。しかし、PCの情報を集めたレポートをスプレッドシートとして生成するような高度な処理は、VBAでなければ実現できません。

動画形式のチュートリアルをご希望の方は、こちらもぜひご覧ください。

なぜExcelからメールを送るのか?

Microsoft Excelの中からメールを送りたい理由はさまざまあります。

例えば、スタッフが毎週ドキュメントやスプレッドシートを更新しており、更新が完了したタイミングでメール通知を受け取りたいケース。あるいは、連絡先の一覧表があり、全員に一括で同じメールを送りたい場面などが考えられます。

「Excelからメールを一括送信するスクリプトを作るのは複雑そう」と感じるかもしれませんが、決してそんなことはありません。

本記事で紹介するテクニックは、Excel VBAに昔から搭載されている機能であるCDO(Collaboration Data Objects)を利用します。

CDOは、Windowsの初期世代から使われているメッセージングコンポーネントです。かつてはCDONTSと呼ばれていましたが、Windows 2000やXPの登場とともに「CDO for Windows 2000」へと置き換えられました。このコンポーネントはWordやExcelのVBA環境にすでに組み込まれており、すぐに使用できます。

CDOを使えば、Windows製品のVBAからのメール送信が格段に簡単になります。ここでは、ExcelのCDOコンポーネントを使って、特定のセルの値を本文に含んだメールを送信する例を見ていきましょう。

ステップ1:VBAマクロを作成する

まずはExcelの「開発」タブを開きます。

「開発」タブの「コントロール」グループにある「挿入」をクリックし、コマンドボタンを選択します。

シート上にボタンを配置したら、「開発」リボンの「マクロ」をクリックして新しいマクロを作成します。

「作成」ボタンをクリックすると、VBAエディターが開きます。

エディターのメニューから「ツール」>「参照設定」を選択し、CDOライブラリへの参照を追加します。

一覧をスクロールして「Microsoft CDO for Windows 2000 Library」を見つけたら、チェックボックスにチェックを入れて「OK」をクリックします。

「OK」をクリックしたら、スクリプトを貼り付ける関数の名前を控えておきましょう。後ほど必要になります。

ステップ2:CDOの「From」「To」フィールドを設定する

次に、メールオブジェクトを作成し、送信に必要な各フィールドを設定します。

多くのフィールドは省略可能ですが、From(差出人)とTo(宛先)は必須である点に注意してください。

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
strSubject = "Results from Excel Spreadsheet"
strFrom = "rdube02@gmail.com"
strTo = "rdube02@gmail.com"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))

うれしいことに、strBody変数には自由に組み立てた文字列を代入できるため、メール本文を思い通りにカスタマイズできます。

上記の例のように、&(アンパサンド)で文字列をつなぎ合わせれば、Excelシート上の任意のデータをメール本文に埋め込むことも可能です。

ステップ3:CDOで外部SMTPサーバーを使うよう設定する

コードの次のセクションでは、CDOが任意の外部SMTPサーバーを使ってメールを送信できるよう設定します。

この例では、Gmail経由の非SSL接続の設定を紹介します。CDO自体はSSLにも対応していますが、詳細は本記事の範囲外です。SSLが必要な場合は、GitHubで公開されている高度なコードサンプルが役立つでしょう。

Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
 .Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
 .Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
 .Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With
With CDO_Mail
 Set .Configuration = CDO_Config
End With

ステップ4:CDOのセットアップを完成させる

SMTPサーバーへの接続設定が済んだら、あとはCDO_Mailオブジェクトの各フィールドに値を設定し、Sendコマンドを実行するだけです。

具体的には以下のように記述します。

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

Outlookのメールオブジェクトを使う場合に発生しがちなポップアップ表示やセキュリティ警告は一切表示されません。

CDOは単純にメールを組み立て、SMTPサーバーの接続情報を使ってメッセージを送信するだけです。WordやExcelのVBAスクリプトにメール機能を組み込む最も手軽な方法と言えるでしょう。

コマンドボタンとこのスクリプトを連携させるには、コードエディターを開いて「Sheet1」をクリックし、そのワークシートのVBAコードを表示します。

そして、先ほどスクリプトを貼り付けた関数の名前を入力します。

以下は、実際に受信トレイに届いたメッセージの様子です。

注意:「The transport failed to connect to the server(サーバーへの転送接続に失敗しました)」というエラーが表示される場合は、「With SMTP_Config」以下のコード行に入力したユーザー名、パスワード、SMTPサーバー、ポート番号が正しいかどうかを確認してください。

さらに一歩進めて、プロセス全体を自動化する

ボタン一つでExcelからメールを送れるだけでも十分便利ですが、この機能を日常的に活用したいなら、プロセス全体を自動化するのが得策です。

そのためには、マクロに少し変更を加えます。Visual Basicエディターを開き、作成したコード全体をコピー&ペーストしましょう。

次に、プロジェクト階層から「ThisWorkbook」を選択します。

コードウィンドウ上部の2つのドロップダウンから、左側で「Workbook」を、右側のメソッド一覧で「Open」を選択します。

そして、先ほどのメール送信スクリプトを「Private Sub Workbook_Open()」の中に貼り付けます。

こうすることで、Excelファイルを開くたびにマクロが自動的に実行されるようになります。

次に、Windowsの「タスクスケジューラ」を開きます。

このツールを使って、一定間隔でスプレッドシートを自動的に開くようWindowsに指示します。ファイルが開かれた時点でマクロが起動し、メールが送信される仕組みです。

「操作」メニューから「基本タスクの作成...」を選択し、ウィザードを進めて「操作」画面まで移動します。

「プログラムの開始」を選択して「次へ」をクリックします。

「参照」ボタンでPC上のMicrosoft Excelの場所を指定するか、パスをコピーして「プログラム/スクリプト」欄に貼り付けてください。

続けて、「引数の追加」欄にExcelドキュメントへのパスを入力します。

ウィザードを完了させれば、スケジュール設定は完了です。

動作確認のため、まずは数分後に実行されるようスケジュールしてテストし、正しく機能することを確認してからタスクを修正することをおすすめします。

注意:マクロが正常に動作するよう、信頼センター(Trust Center)の設定を調整する必要がある場合があります。

その場合は、スプレッドシートを開いて「ファイル」>「オプション」>「トラストセンター」へ移動します。

「トラストセンターの設定」をクリックし、次の画面で「ブロックされたコンテンツに関する情報を表示しない」にラジオボタンを設定しましょう。

Microsoft Excelをあなたのために働かせよう

Microsoft Excelは非常に強力なツールですが、そのポテンシャルを最大限に引き出す方法を習得するのは、少しハードルが高いと感じるかもしれません。このソフトウェアを真にマスターしたいなら、VBAに慣れ親しむ必要があり、それは決して容易な道のりではありません。

しかし、その成果は努力に見合うものです。少しでもVBAの経験を積めば、定型的なタスクをExcelに自動実行させられるようになり、より重要な業務に集中する時間を生み出せるでしょう。

VBAのスキルを磨くには時間がかかりますが、諦めずに続ければ必ず実りある結果が得られます。

まずは、ExcelでのVBA活用に関する体系的なチュートリアルから始めるのがおすすめです。それを終えた頃には、今回のようなExcelからメールを送るシンプルなスクリプトは、お茶の子さいさいに感じられるはずです。