Excelからメールを一括送信するプログラムの解説。

これまでのプログラミングで、Excelから一括してメール送信することができるようになりました。

メールを一括送信できると、以下のようなメリットがあります。
・独自のメールマガジンを配信できる
・既存の顧客へ個々にカスタマイズしたメールが送れる

プログラムの動作確認、エラー処理の確認までは、前回のステップで行ないました。

今回はメールを一括送信するVBAプログラムについて、詳しく解説します。

(1)宣言セクションに変数を追加しました。

Option Explicit

Dim svname As String
Dim id As String
Dim pass As String
Dim mSender As String '送信者
Dim mailq As String 'メールキュー
Dim logfile As String 'ログファイル
Dim startPos As Long '開始行番号
Dim endPos As Long '終了行番号


・メールキュー 送信メールを一時的に保存するフォルダ名
・ログファイル 送信結果を記録するファイル名
・開始行番号、終了行番号でメール送信範囲を指定

宣言セクションで宣言した変数は、各プロシージャで利用できます。

またtName、eMailは、「メール送信」ボタンをクリックした時のイベントプロシージャに移動しました。他のプロシージャで利用する必要が無くなったからです。


(2)フォーム「frm送信」初期化時のイベントプロシージャに追加した部分。

Private Sub UserForm_Initialize()
  省略
  '初期値
  Me.txtStartPos = 2
  Me.txtEndPos = 2
  startPos = 2
  endPos = 2
  省略
  'メールキュー
  mailq = "C:\mailPG\Send"
  
  'ログファイル
  logfile = "C:\mailPG\logfile.txt"
End Sub


*「\」はWindowsでは円記号のことです。

・開始行番号と終了行番号のテキストボックスに初期値を代入しています。
Me.txtStartPos = 2
Me.txtEndPos = 2

・宣言セクションで宣言した変数 startPos と endPos にも初期値を代入しています。
startPos = 2
endPos = 2

テキストボックスに初期値を代入しているので、変数に代入する必要は無いと思うかも知れませんが、これは必要な処理です。

行番号を変更した時に、変数の値が自動的に変わるようにプログラミングしていますが、初期値のまま送信する可能性もあるためです。

・メールキューの場所を指定しています。
mailq = "C:\mailPG\Send"

・ログファイルの場所とファイル名を指定しています。
logfile = "C:\mailPG\logfile.txt"


(3)行番号のテキストボックスから出る時のイベントプロシージャは、これまでと同じなので、説明を省略します。

Private Sub txtEndPos_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  '省略
End Sub



Private Sub txtStartPos_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  '省略
End Sub


わからない場合は、 Step19・送信先指定プログラムの解説 を参考にしてください。

ただし、ラベルに送信先の氏名とメールアドレスを表示する処理は、無くなりました。送信先が複数あるからです。


(4)「メール送信」ボタンをクリックした時のイベントプロシージャです。

Private Sub cmd送信_Click()
  '省略
End Sub



それではメール送信時のプログラムを、詳しく見てみましょう。

(5)変数の宣言部分に変化があります。

Dim msg As Variant 'メール作成チェック用
Dim rc As Integer '一括送信チェック用
Dim i As Long
Dim tName As String
Dim eMail As String


・msg メール作成チェック用に役割が変わりました。
・rc 一括送信チェック用を新たに加えました。
・i 繰り返し処理を行なう時にカウントする変数です。
・tName、eMailをここへ移動しました。

tName、eMailは、メール送信時のイベントプロシージャ内でしか使わなくなったからです。


(6)行番号の値をチェックしている部分です。

If endPos < startPos Then
  Me.lblMsg = "終了行番号は、開始行番号以上の値を入力してください。"
  'フォーカスを移す
  Me.txtEndPos.SetFocus
  'メール送信ボタン使用不可
  Me.cmd送信.Enabled = False
  Exit Sub
End If


終了行番号は、開始行番号以上でなければ、値が正しくありません。

そんな時は、メッセージを表示して、終了行番号のテキストボックスにフォーカスを戻しています。

そしてメール送信ボタンは使用不可にし、「Exit Sub」でこのイベントプロシージャを抜け出しています。

つまり行番号の値が適正でないと、以下のプログラムは実行されない仕組みです。


(7)ワークシートに未入力の項目が無いか、チェックしている部分です。

For i = startPos To endPos
  'ワークシートの値を取得
  tName = Trim(Range("A" & i).Value)
  eMail = Trim(Range("B" & i).Value)
  
  If tName = "" Or eMail = "" Then
    Me.lblMsg = "行番号" & i & "にデータが未入力の項目があります。"
    'フォーカスを移す
    Me.txtEndPos.SetFocus
    'メール送信ボタン使用不可
    Me.cmd送信.Enabled = False
    Exit Sub
  End If

Next i


「For Next ステートメント」は、繰り返し処理を行なう制御文です。

変数 i が startPos から endPos になるまで、中に書いた処理を繰り返します。つまり行番号を選択した範囲を繰り返すわけです。

iはカウントアップ(加算)されるので、それに合わせてワークシートから1行ずつデータを取得することができます。

tName = Trim(Range("A" & i).Value)
eMail = Trim(Range("B" & i).Value)

もし、ワークシートの選択範囲で、未入力の項目(氏名、メールアドレス)があった場合は、(6)と同じように処理を行なってから、このイベントプロシージャを抜け出しています。

やはり値が適正でないと、以下のプログラムは実行されない仕組みです。

これらはメール送信時のエラーを、できるだけ防ぐための処理です。


(8)この部分はこれまでと同じです。

'オブジェクトを作成
Set bobj = CreateObject("basp21")

'送信者
mailfrom = mSender & vbTab & id & ":" & pass

'件名取得
subj = Me.txtSubject

'本文取得
body = Me.txtBody



(9)変数を追加しました。

Dim tmpSubj As String
Dim tmpBody As String
Dim itemName As String
Dim price As String


メール一括送信では、個々にメールを作成し、後でまとめて送る仕組みです。

フォーム上から取得した件名や本文は、変数 subj や body に代入されますが、この変数をそのまま使うと、個々のメール作成時に値が変わってしまうため、都合が悪いです。

そこで一時的に値を保存しておく変数 tmpSubj と tmpBody を追加しました。これらの一時的な変数は、値が変わっても、subj や body を代入し直せば、元に戻すことができます。

itemName、price の使い方は、これまでと同じです。


(10)開始~終了行番号のメールを作成している部分です。

For i = startPos To endPos
  省略
Next i


変数 i が startPos から endPos になるまで、処理を繰り返します。つまりメールが複数作成されます。

それではプログラムを細かく見てみましょう。

・ワークシートの値を取得している部分です。
tName = Trim(Range("A" & i).Value)
eMail = Trim(Range("B" & i).Value)

iはカウントアップしていきます。

・宛先を代入している部分です。宛先はメールごとに変化します。
mailto = eMail

・件名を一時変数に代入している部分です。
tmpSubj = subj

この代入が毎回行なわれるので、tmpSubjの値は、繰り返す度に元に戻ることがポイントです。

・件名に自動挿入するのは、これまでと同じです。
tmpSubj = Replace(tmpSubj, "[氏名]", tName)

違いは、subj ではなく tmpSubj に置換を行い、代入していることです。

・本文を一時変数に代入している部分です。
tmpBody = body

tmpBodyの値は、繰り返す度に元に戻ることがポイントです。

・本文に自動挿入するのは、これまでと同じです。
tmpBody = Replace(tmpBody, "[氏名]", tName)

itemName = Trim(Range("D" & i).Value)
tmpBody = Replace(tmpBody, "[商品名]", itemName)

price = Trim(Range("E" & i).Value)
price = Format(CLng(price), "##,##0")
tmpBody = Replace(tmpBody, "[金額]", price)

違いは、bodyではなく、tmpBodyに置換を行い、代入していることです。


・実際にメールを作成しているのは、この一行です。
msg = bobj.SendMail(mailq, mailto, mailfrom, tmpSubj, tmpBody, "")

引数に mailq(メールキュー)、tmpSubj、tmpSubj が使用されている点が、これまでと違うところです。

*svname、subj、bodyではないので、注意してください。

SendMailメソッドは、メールキューを引数に指定すると、すぐにメールを送信するのではなく、メールを作成するようになります。

作成されたメールは、メールキューで指定したフォルダに、テキストファイル形式で保存されます。

・メールが作成されたか、チェックしている部分です。
If msg <> "" Then
  MsgBox "行番号" & i & "のメールで作成エラー。" & vbCrLf & msg, vbOKOnly + vbCritical, "作成時エラー"
  Exit Sub
Else
  Me.lblMsg = "行番号" & i & "のメールを作成しました。"
End If

もしメールの作成に失敗した場合は、「Exit Sub」でこのイベントプロシージャを抜け出しています。

失敗した時、既に作成されたメールは、メールキューに残ったままです。次回送信時に重複して送信しないためには、手動で削除する必要があります。

・残ったメール
mail-227.gif

*削除はプログラムで自動化できます。


ここからが、メールを一括送信する処理です。

・ラベルにメッセージを表示しています。
Me.lblMsg = "メール送信開始・・・"

・実際にメールを一括送信しているのは、この一行です。
rc = bobj.FlushMail(svname, mailq, logfile)

FlushMailメソッドは、引数にsvname(SMTPサーバー)、mailq(メールキュー)、logfile(ログファイル)を指定している点がポイントです。

*ログファイルが不要なら、省略することも可能です。

・メールを一括送信できたか、チェックしている部分です。
If rc <= 0 Then
  MsgBox "送信できませんでした。" & vbCrLf & "エラー:" & rc, vbOKOnly + vbCritical, "送信時エラー"
Else
  MsgBox rc & "件のメールを送信しました。", vbOKOnly + vbInformation, "完了"
End If

FlushMailメソッドは、メール送信が成功すると、1以上の送信したメール数を返します。すると変数 rc には件数が入るので、メッセージボックスに表示しています。

逆に失敗した場合は、マイナスの数値を返すので、エラーのメッセージボックスを表示しています。

どちらにしても、数値が返ってくるので、変数 rc は Integer(数値)型で宣言しました。
Dim rc As Integer

Integer型に代入できる数値の範囲は決まっています。そのため、あまりにも多くのメールを送信すると、変数がオーバーフローしてエラーになります。

・送信が完了したら、ラベルをクリアして終了です。
Me.lblMsg = ""


今回は、プログラムが少し長いので、解説を1回読んだだけでは、わからないかもしれません。

そんな時は、動作確認をしながら、何度も何度もプログラムを追ってみると、わかるようになります。


【補足】
(1)変数の宣言場所について
プロシージャ内では、変数はどこでも宣言できます。最初の部分でまとめて宣言したり、使う直前で宣言したり様々です。プログラマーによって書き方が違います。

ソフトウェア開発会社では、開発効率やメンテナンスを考え、プログラミングの書き方のルールを決めている場合もあります。

プロシージャ全体で使う変数は、最初の部分でまとめて宣言して、部分的に使う変数は、使う直前で宣言するなど、自分なりのルールを決めておくとよいでしょう。


(2)入力チェックについて
行番号の大小チェックや、ワークシートの未入力チェックは、テキストボックスから出る時のイベントプロシージャに書いてもよさそうですよね。実際に可能です。

でもテキストボックスの入力チェックを厳しくしすぎると、逆に入力が不便になるので、「メール送信」ボタンをクリックした時のイベントプロシージャに書いているわけです。

スポンサードリンク

スポンサードリンク






メール送信・受信プログラミング初心者入門 TOPへ