くろむぽんの雑記ノート

EXCEL: PDF添付＆Outlookでメールを一斉送信【VBAマクロ】

今回作成したVBAマクロは、PDFを添付して、複数の宛先にメールを一斉送信するマクロです。
同じ書類を、決まった人に送ることって、よくありますよね。
1ヶ月に1回とか、1週間に1回とか、頻度が多くなればなるほど、大変になります。
しかも、宛先が数十人超えてくると、1通1通全て手作業で送るのは、時間もかかりますし、宛先名や送信漏れなど注意深く作業しなければいけないので、想像以上に疲れます。
ということで、メールを一斉送信できるプログラムを書いてみました。

参考にさせて頂いたサイト：
・VBAで複数Outlookメールを個別作成し一斉送信｜添付ファイルも付ける
・【Outlook VBA】HTML形式のメールを作成/送信する方法！書式やハイパーリンクも設定可！

このマクロであれば、下の画像のように、指定したPDFを自動添付して、複数の宛先に一斉にメールを送信できます。
なおかつ、メール文面に、リンクを入れたり、強調したい文字の色や大きさも変更可能です。

【目次】
1. 概要
2. コード【VBAマクロ】
3. コード【VBAマクロ】の解説

概要

Outlook.Applicationにより、指定したPDFファイル（PDF以外も可能）をメール添付や、メールの宛先入力・本文入力を行っています。
資料の添付は「Attachments.Add」にて行い、件名や宛先は「SubjectとTo」で指定可能です。
複数の宛先に対しては、For Next構文で、リストにある全てのメールアドレスに送信できます。
メール本文の中身は、Functionを作って対応しています。

エクセル画面

送信ボタンを押すと、送信履歴にCompleteと現在時刻が印字され、下の画像のようにPDFが添付され、準備した文面のメールが自動で生成される。

↓

自動作成されたメール

このマクロで出来ること
・PDFの添付
・多数の宛先への一斉メール
・送信履歴の管理
・URLのリンク挿入
・文字の色や大きさの変更




コード【VBAマクロ】

Sub メール一斉送信_PDF添付() パート

  1. Sub メール一斉送信_PDF添付()
  2.     Dim R_Start As Integer, R_End As Integer
  3.     Dim Tenp1 As String, Tenp2 As String
  4.     Dim mailBody As String
  5.  
  6.     Dim OutlookObj As Outlook.Application
  7.     Set OutlookObj = CreateObject("Outlook.Application")
  8.  
  9.     Tenp1 = Range("B2")
  10.     Tenp2 = Range("B3")
  11.     R_Start = Range("F3") + 6
  12.     R_End = Range("H3") + 6
  13.  
  14.     For R_Start = R_Start To R_End
  15.         mailBody = CreateMailBody(R_Start)
  16.  
  17.         Dim mailItemObj As Outlook.MailItem
  18.         Set mailItemObj = OutlookObj.CreateItem(olMailItem)
  19.  
  20.         mailItemObj.Subject = Range("B1")
  21.         mailItemObj.To = Cells(R_Start, "B")
  22.         mailItemObj.HTMLBody = mailBody
  23.  
  24.         If Tenp1 <> "" Then
  25.             mailItemObj.Attachments.Add Tenp1
  26.         End If
  27.         If Tenp2 <> "" Then
  28.             mailItemObj.Attachments.Add Tenp2
  29.         End If
  30.  
  31.         mailItemObj.Display
  32.         'mailItemObj.Send 'チェックせずに送信したい場合
  33.         Range("E" & R_Start).Value = "Complete" & Now()
  34.  
  35.     Next
  36.  
  37.     Set OutlookObj = Nothing
  38.     Set mailItemObj = Nothing
  39.  
  40.     MsgBox "Complete"
  41.  
  42. End Sub

Function CreateMailBody パート

  1. Function CreateMailBody(R_Start) As String
  2.     Dim Company As String
  3.     Dim Name As String
  4.  
  5.     Company = Cells(R_Start, "C").Value
  6.     Name = Cells(R_Start, "D").Value
  7.  
  8.     Body = Range("B4").Value
  9.     Body = Replace(Body, "{Company}", Company)
  10.     Body = Replace(Body, "{Name}", Name)
  11.  
  12.     CreateMailBody = Body
  13.  
  14. End Function

コード【VBAマクロ】の解説

Sub メール一斉送信_PDF添付() パートの解説

2-4.
メール送信先の開始番号をR_Start、終了番号をR_Endと定義しています。
また、添付書類の1つ目（Tenp1）と2つ目（Tenp2）を、文字列として定義しています。（添付書類を増やすことも可能）
さらに、メールの本文も、mailBodyとして文字列で定義しています。
6-7.
OutlookObj（オブジェクト）をOutlook.Applicationを使う箱として用意します。
※参照設定で、Microsoft Outlook 16.0 Object Libraryなどのライブラリーにチェックを入れておく必要があります。
※Macでは使えません。私はこれを知らずに、1時間近く悩んでいました笑。
その時のツイートはこちら
CreateObject関数により、Outlook.ApplicationをOutlookObjで使えるように設定します。
9-12.
B2セルの情報をTenp1として、B3セルの情報を添付2として使用します。
※「C:¥Users¥___(user name)¥Desktop¥___(file name).pdf」のようにPDFファイルの絶対パスを記入します。
　注意点として、最後のファイル名（拡張子含む）まで記入する必要があります。
実際のR_Startの行番号は、開始番号欄（F3）に記入する数に6を足したものとなるため、その計算をしています。
同じく、R_Endの行番号は、終了番号欄（H3）に記入する数に6を足したものとなるため、その計算をしています。
14.
For Next構文で、R_StartからR_Endまでループします。
15.
mailBodyという文字列の箱に、CreateMailBodyという下のFunctionパートで作成した内容をR_Startから順番に入れていきます。
17-18.
mailItemObj（メール内容の保管場所）というOutlook.MailItemを入れるオブジェクトを用意します。
OutlookObj.CreateItem関数により、mailItemObjで使えるように設定します。
20-22.
件名、宛先、メール本文をそれぞれ、B1セル、B列のR_Startからの情報、mailBodyを入れます。
24-29.
B2セル（Tenp1）に添付ファイルが記入されていれば（空白でなければ：<> “”）、mailItemObjにAttachments.Addという関数を使って、Tenp1ファイルを添付します。
何も記入されていなければ（空白であれば）、添付せずにIfが終わります。
B2、B3セルには、以下のように絶対パスで記入します。
「C:¥Users¥___(user name)¥Desktop¥___(file name).pdf」※デスクトップにファイルを置いた場合はこんな感じ。
31-33.
mailItemObjをDisplay関数で、表示させます（最初の画像のようにPDFが添付され、宛先や本文が入った状態でメールができます）。
私は、一応中身を見れるように、一回Displayで表示させていますが、確認が終了してチェックせずに送信したい場合は、代わりに「mailItemObj.Send」を書いておけばOKです。
処理終わったものに関して、E列のR_StartからR_Endまでのセルに、Completeと処理時間が記載されます。
37-38.
OutlookObjとmailItemObjの中身を空にしています。
40.
メッセージボックスにCompleteと表示させて、本当に実行できたのか確認できるようにしています。
※表示する文字の変更は、Completeを別の文字に変えるだけで可能です。

Function CreateMailBody パートの解説

1.
CreateMailBodyという独自のFunctionを作成します。
上のパートに組み込むことも出来ますが、長くなって、修正したい場合に大変なので、別で分ています。
2-3.
CompanyとNameを文字列として定義しておきます。
5-6.
Companyには、C列の会社名の情報をR_Startから入れていきます。
Nameには、D列の宛名の情報をR_Startから入れていきます。
8-10.
メールのBodyにB4セルに入っている情報を入れます。
※HTMLでの記述例は、以下にまとめています。
その後、文中の{Company}と{Name}を、それぞれ5-6.のCompanyとNameの情報に置換します。
12.
上記で作成したBodyの内容を、CreateMailBodyに入れて完了です。
すると、「Sub メール一斉送信_PDF添付() パート」の15行目のCreateMailBodyに必要な情報が入った状態になります。

メール本文（HTML）の記述例

  1. <font face="Yu Gothic" size="3">{Company}<br>
  2. {Name} 様<br>
  3. <br>
  4. くろむぽんです。<br>
  5. 添付資料を送ることも可能です。<br>
  6. また、このような<a href="https://www.google.com/">リンク</a>を送ることも可能です。<br>
  7. <br>
  8. HTML形式で記入するので、文字の<font color="red">色</font>や<font size="5">大きさ</font>の変更も自由自在。
  9. <br>
  10. </font>

まとめ

添付ファイルの絶対パスを記入しなければいけなかったり、メール本文をHTMLで記載したりと、まだまだ不便なところは多いですが、とりあえずものにはなったかなという感じです。
また、このマクロに、PDF自動作成のマクロもくっつければ、さらに作業を自動化することも出来そうです。
ただ、メールの送信でもっとも気をつけなければならないことが、「送りたい人」に「送りたい内容」を送ることなので、そこが出来ているだけでも大幅に業務が改善できそうです。



VBAマクロの最新記事8件