【VBA】Excelから複数・多種類のOutlookのメールを作成・送信する方法

Outlookのメールを日々利用されている方は、エクセルで宛先や本文などまとめた情報からメールを作成・送信したいときはないでしょうか。

例えば、日報、週報、各書届などがワンクリックで何件もメールが作成され、送信ができるとなるととても楽になりますよね。

けど、そんな中で悩むことは、

・Excelからメール送信するやり方がわからない。
・毎回メールを新規作成するのは面倒なので自動化したいけどやり方がわからない。
・日報、週報のメールをテンプレート化して、報告内容を入力するだけにしたいがやり方がわからない。
・宛先を手入力すると間違えてしまう可能性があるので固定値として定義しておきたいが、どうすればよいかわからない。

ですよね。

今回はそんなお悩みを解決する
【VBA】エクセルから複数・多種類のOutlookのメールを作成・送信する方法について
まとめます!


エクセルからOutlookのメールを作成・送信する機能ついて

今回のVBAはExcelに記載した宛先、本文情報とメールテンプレートをもとに、Outlook側のメールを作成し、送信するといった内容となります。

サンプルデータは企業でよく使う勤怠の諸届(半休、全休、休日出勤など)としています。

それを以下のようにExcelのデータシートへ記載していきます。

続いて、メールテンプレートとなるシートを追加し、以下のようにタグ付きで入力します。

入力が完了したら、メールを作成したい行のA列に「表示」、メールテンプレート名を入力した上、VBAを実行すると、

メールテンプレートに沿った形でメールが作成され、タグの部分は先ほどのデータシートの値に置換されます。

また、A列に「送信」とすることで、表示をせずに送信することができます。

複数送信したい場合は、その分行を増やせばよいですし、

諸届以外にも日報など別のメール形式で出力したいときは、メールテンプレートを追加し、データシートに追加すればいろいろな種類のメールを同時に作成、送信することが可能です。

タカヒロ
タカヒロ
サンプルデータの作成は後半に詳細に説明しますので、今はこんな機能なんだなぐらいに観てもらえばと思います。



エクセルからOutlookのメールを作成・送信する機能の処理の流れについて

今回の機能の処理は以下の通りExcelからOutlookを起動し、Outlookのメール作成、送信が行われる流れとなります。

Excelブック(VBA) → この内容でメールを作成してね → Outlook
Excelブック ← メールを作成したよ ← Outlook
Excelブック(VBA) → メールを送信してね → Outlook
Excelブック ← メールを送信したよ ← Outlook

では、早速実装をして動かしてみましょう!

タカヒロ
タカヒロ
ExcelからOutlookを起動して捜査しているんだなぐらいに思っておいてください。




VBAを実装する

続いてVBAを実装します。

VBAは以下の通りとなります。

Sub メール作成および送信()


    Dim objOutlookApp As Object
    Dim objMail As Object
    Dim Rng As Range
    Dim intintNum As Integer
    Dim strBody As String
    Dim strTag1 As String
    Dim strTag2 As String
    Dim strTag3 As String
    Dim strTag4 As String
    Dim strTag5 As String
    Dim strBr As String
    Dim strSubject As String
    Dim strSTname_Template As String
    strBr = vbLf '改行コード



    '「メール宛先・本文」シートのA列最終行まで処理を繰り返します。
    For intNum = 2 To Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1
    
        '「メール宛先・本文」シートのA列載せるに値があればメール作成・送信処理を継続します。
        If Worksheets(1).Cells(intNum, 1) <> "" Then

            'メールテンプレートを設定します。
            strSTname_Template = Worksheets(1).Range("B" & intNum).Value
        
            'メールテンプレートの指定が無い場合は警告表示および処理を中断します。
            If strSTname_Template = "" Then
                MsgBox "B" & intNum & "にタイトル・本文テンプレートシートを指定してください。"
                Exit Sub
            End If
        
            
            'Outlook.Applicationを呼び出し、セットします。
            Set objOutlookApp = CreateObject("Outlook.Application")
            Set objMail = objOutlookApp.CreateItem(olMailItem)
            
            
            'メール宛先・本文シート記載の値を変数に入れます。
            strTag1 = Worksheets(1).Range("G" & intNum).Text
            strTag2 = Worksheets(1).Range("H" & intNum).Text
            strTag3 = Worksheets(1).Range("I" & intNum).Text
            strTag4 = Worksheets(1).Range("J" & intNum).Text
            strTag5 = Worksheets(1).Range("K" & intNum).Text
        
            
            'テンプレートのメール本文の箇所を読み込み、変数に入れます。
            For Each Rng In Worksheets(strSTname_Template).Range(Worksheets(strSTname_Template).Range("A5").Value)
                strBody = strBody & Rng.Value & vbLf
            Next Rng
            
            'メール本文中のタグを指定データへ置換します。
            strBody = Replace(strBody, "<Tag1>", strTag1)
            strBody = Replace(strBody, "<Tag2>", strTag2)
            strBody = Replace(strBody, "<Tag3>", strTag3)
            strBody = Replace(strBody, "<Tag4>", strTag4)
            strBody = Replace(strBody, "<Tag5>", strTag5)
        
            
            With objMail
                .BodyFormat = 3 ' 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
                .To = Worksheets(1).Range("E" & intNum).Value
                .CC = Worksheets(1).Range("F" & intNum).Value
                
        
                '本文をBodyプロパティにセットします。
                .Body = Worksheets(1).Range("C" & intNum).Value & strBr & Worksheets(1).Range("D" & intNum).Value & strBr & strBr & strBody
        
                
                'テンプレートからタイトルを読み込みます。
                strSubject = Worksheets(strSTname_Template).Range("A2").Value
                
                'タグ部分を置換します。
                strSubject = Replace(strSubject, "<Tag1>", strTag1)
                strSubject = Replace(strSubject, "<Tag2>", strTag2)
                strSubject = Replace(strSubject, "<Tag3>", strTag3)
                strSubject = Replace(strSubject, "<Tag4>", strTag4)
                strSubject = Replace(strSubject, "<Tag5>", strTag5)
        
                .subject = strSubject
                
                
                
                Select Case Worksheets(1).Cells(intNum, 1)
                Case "表示"
                    'メールを表示します。
                    .Display
                Case "送信"
                    .Display
                    .Send
                Case Else
                    MsgBox "値が異なります。「表示」か「送信」を入力してください。"
                End Select
                
                
                
            End With
            'メール本文をクリアします。
            strBody = ""
        
        End If
        
    Next intNum

    MsgBox "メール作成・送信が完了しました。"
    
    Set objOutlookApp = Nothing
    Set objMail = Nothing

End Sub

設定が必要な箇所は特にないですが、メールフォーマットは標準はリッチテキスト形式ですので、
利用しているフォーマットと異なれば適宜変更するようお願いします。

「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
.BodyFormat = 3



サンプルデータを用意する

上記の通りサンプルデータは企業でよく使う勤怠の諸届(半休、全休、休日出勤など)としていますので、
まずは情報を入力するデータシートおよび諸届メールのテンプレートシートを用意しましょう。

①情報を入力するシートを作成し、シート名を「メール宛先・本文」へ変更します。
※このシートはブックの1番左の位置へ配置するようにお願いします。

②1行目に項目名を入力します。

Mail操作 タイトル・本文テンプレート 部署名 氏名 To Cc Tag1 Tag2 Tag3 Tag4 Tag5

③2行目のC2~J2まで以下のように入力します。
各項目の値は業務内容に応じて変更していただいて構いません。

④メールテンプレートとなるシートを追加し、シート名を「諸届テンプレ」へ変更します。

⑤以下をコピーし、A1セルからペーストしてください。

——–ここから——-
■タイトル
【諸届】タカヒロ:<Tag1>:<Tag2>

■本文記載範囲
A8:A16

■本文
以下の通り申請致します。

氏名:タカヒロ
年月日:<Tag1>
区分:<Tag2>
時間:<Tag3>
理由:<Tag4>

——–ここまで——-

タカヒロ
タカヒロ
Tag1-4の部分がHTMLコードと解釈され表示されていなかったようです。
申し訳ありません。
訂正をいたしましたので、再度参照頂ければと思います。

⑥「メール宛先・本文」シートへ戻り、A2に「表示」、B2に「諸届テンプレ」と入力します。
A列に「表示」といれるとその行の値を参照し、メールが作成・表示されることを意味します。

タカヒロ
タカヒロ
A列に「送信」と入れるとその行の値からメールの作成および送信がされます。
B列はメール本文のテンプレートを指定する列となります。
シートを追加することで複数の本文テンプレートを設定することが可能です。

これでデータの準備は完了です!



VBAを実行する

メールを作成・表示する

①「開発」タブの「マクロ」をクリックし「メール作成および送信」を選択し、「実行」をクリックします。

②Outlookからメールが作成されたら完成です!

メールを送信する

①「メール宛先・本文」シートのA列を「送信」に変更します。
サンプルでは行を増やし2つの書届メールを作成、送信します。

②「開発」タブの「マクロ」をクリックし「メール作成および送信」を選択し、「実行」をクリックします。

④Outlookの送信トレイの蓄積、送信済みトレイに送信履歴があれば完成です!

タカヒロ
タカヒロ
「送信」指定の場合はメールが送付されますので、十分に検証をされてから実行をするようお願いします。




今回のVBAについて説明

「メール宛先・本文」シートのA列最終行を取得し、件数分処理を繰り返します。
For intNum = 2 To Worksheets(1).Cells(1048576, 1).End(xlUp).Row + 1

「メール宛先・本文」シートのA列載せるに値があればメール作成・送信処理を継続します。
If Worksheets(1).Cells(intNum, 1) <> “” Then

メールテンプレートを設定します。
strSTname_Template = Worksheets(1).Range(“B” & intNum).Value

メールテンプレートの指定が無い、もしくは指定テンプレートが存在しない場合は警告表示および処理を中断します。
If strSTname_Template = “” Then
MsgBox “B” & intNum & “にタイトル・本文テンプレートシートを指定してください。”
Exit Sub
End If

Outlook.Applicationを呼び出し、セットします。
Set objOutlookApp = CreateObject(“Outlook.Application”)
Set objMail = objOutlookApp.CreateItem(olMailItem)

メール宛先・本文シート記載の値を変数に入れます。
strTag1 = Worksheets(1).Range(“G” & intNum).Text
strTag2 = Worksheets(1).Range(“H” & intNum).Text
strTag3 = Worksheets(1).Range(“I” & intNum).Text
strTag4 = Worksheets(1).Range(“J” & intNum).Text
strTag5 = Worksheets(1).Range(“K” & intNum).Text

テンプレートのメール本文の箇所を読み込み、変数に入れます。
For Each Rng In Worksheets(strSTname_Template).Range(Worksheets(strSTname_Template).Range(“A5”).Value)
strBody = strBody & Rng.Value & vbLf
Next Rng

メール本文中のタグを指定データへ置換します。
strBody = Replace(strBody, “”, strTag1)
strBody = Replace(strBody, “”, strTag2)
strBody = Replace(strBody, “”, strTag3)
strBody = Replace(strBody, “”, strTag4)
strBody = Replace(strBody, “”, strTag5)

With objMail
.BodyFormat = 3 ‘ 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
.To = Worksheets(1).Range(“E” & intNum).Value
.CC = Worksheets(1).Range(“F” & intNum).Value

本文をBodyプロパティにセットします。
.Body = Worksheets(1).Range(“C” & intNum).Value & strBr & Worksheets(1).Range(“D” & intNum).Value & strBr & strBr & strBody

テンプレートからタイトルを読み込みます。
strSubject = Worksheets(strSTname_Template).Range(“A2”).Value

タグ部分を Replaceメソッドで指定した値へ置換します。
strSubject = Replace(strSubject, “”, strTag1)
strSubject = Replace(strSubject, “”, strTag2)
strSubject = Replace(strSubject, “”, strTag3)
strSubject = Replace(strSubject, “”, strTag4)
strSubject = Replace(strSubject, “”, strTag5)

.subject = strSubject

Select Case で実行パターン別に処理をおこないます。

Select Case Worksheets(1).Cells(intNum, 1)
Case “表示”
‘メールを表示します。
.Display
Case “送信”
.Display
.Send
Case Else
MsgBox “値が異なります。「表示」か「送信」を入力してください。”
End Select

最後にメール本文をクリアします。
strBody = “”



さいごに

いかがでしょうか。

今回は、
【VBA】エクセルから複数・多種類のOutlookのメールを作成・送信する方法について
まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。

タカヒロ
タカヒロ
他の種類のメール作成方法をまとめてみました。

【VBA】業務日報をExcelテンプレートからワンクリックで作成、メール送信する方法

【VBA】障害・復旧メールをExcelテンプレートからワンクリックで作成、送信する方法

11 件のコメント

  • いつもは拝見させてもらって参考にさせていいただいてます
    私の設定が悪いのでしょうか?上記コードを実行すると
    うまくTag>の部分が変換されず年月日:
    区分:<Tag>
    時間:<Tag>
    理由:
    と表示されるのですが どのようにしたらよいのでしょうか?

    • いつもご利用ありがとうございます。

      タグにつきまして、こちらページの仕様によりうまく表示がされていないようで大変失礼いたしました。

      カギカッコは半角で、その中身をTag1~Tag4まで割り当てて頂ければと存じます。
      以下サンプルです。

      年月日:<Tag1>
      区分:<Tag2>
      時間:<Tag3>
      理由:<Tag4>

      本文のほうもカギカッコが表示されるよう修正をいたしました。

      VBAとテンプレートサンプルともに再度ご参照頂きたくお願いいたします。

      よろしくお願いいたします。

        • お手数おかけいたします。

          VBAとテンプレートサンプルともに再度ご参照頂ければと存じます。

          • ありがとうございます ほぼ100%完成しました
            あとは 自分なりに少しカスタマイズして
            完成します
            また、よろしくお願いいたします

          • 素晴らしいです 自分で想像したようになっていくことに感動します
            ある程度できる気でいましたが、全然ということに気づかされます
            ありがとうございます
            さて、テキストで保存された内容を続けて確認したく思い
            Open “C:\Users\*****\Documents\” & strSubject & “.txt” For Output As #1
            Print #1, “件名:” & strSubject
            Print #1, “本文:” & strBody
            Close #1 ‘開いたファイルを閉じる

            Shell “notepad”, 1
            を追加してメモ帳で開こうかと思いましたが新規に立ち上がるばかりですね
            わかっているのですが 
            Close #1 の前にいったん表示してと思いましたがどうもうまくいきません
            Close #1 をコメントアウトしてみたのですが。。。
            VB YES NOで YESで Close#1へ NOでそのままアクティブ
            できませんか?
            度々すみません

          • 形になってきたようですね。
            こちらも嬉しく思います。

            テキストで保存されたファイルをShellでメモ帳を使用し開きたいということでしたら、
            以下のように開きたいファイルのパスを引数に指定する必要があります。

            Shell “notepad” & ” ” & “C:\Users\*****\Documents\” & strSubject & “.txt”, 1

            YesNo形式でメモ帳で開くか確認メッセージを出したい場合は、以下のように条件を追加します。

            Dim intYesNo As Integer
            intYesNo = MsgBox(“メモ帳で確認しますか?”, vbYesNo, “確認”)
            If intYesNo = vbYes Then
            Shell “notepad” & ” ” & “C:\Users\*****\Documents\” & strSubject & “.txt”, 1
            End If
            ご確認ください。

          • もう一つ質問させてください このテンプレートで作成したメールの内容をそのまま メモ帳に出力したいのですが 追加でどのように書けばよいでしょう
            同時進行させたいのですが メールの内容を同時にメモ帳として残したいため

          • ご質問の作成したメールの内容をメモ帳として残す処理を加える件につきまして、

            テキストファイルの出力ということでしたら、以下のVBAにて可能です。

            テキストファイル(メモ帳)書き込み処理:
            ※ファイルのパスはご自身の環境にあわせて変更願います。
            ※ファイル名はメールの件名となっています。

            Open “C:\Users\*****\Documents\” & strSubject & “.txt” For Output As #1
            Print #1, “件名:” & strSubject
            Print #1, “本文:” & strBody
            Close #1 ‘開いたファイルを閉じる

            使用例:
            “表示”処理のところにテキスト出力の処理を加えました。
            送信時にテキスト出力したい場合は、”送信”の方へ追加してください。

            Select Case Worksheets(1).Cells(intNum, 1)
            Case “表示”
            ‘メールを表示します。
            .Display

            ‘#テキストファイル(メモ帳)書き込み処理 追加
            Open “C:\Users\*****\Documents\” & strSubject & “.txt” For Output As #1
            Print #1, “件名:” & strSubject
            Print #1, “本文:” & strBody
            Close #1 ‘開いたファイルを閉じる

            Case “送信”
            .Display
            .Send
            Case Else
            MsgBox “値が異なります。「表示」か「送信」を入力してください。”
            End Select

            よろしくお願いいたします。

          • ありがとうございます うまくいきそうです
            今後も参考にさせていただきます

  • ほんとに度々ひつ濃くてすみません また躓きました
    今作成中のものが一つのブックで複数シートがあります それぞれのシートは
    月ごとにわかれております 1行に数字が並んでいるわけなのですがA~GPまでです
    それぞれ<Tag1~最終まで埋め込み メールを立ち上げることができます 成功です
    感謝します
    これで成功なのですが ファイルが膨大なため極力軽くすましたいので
    コメントなど一つのセルで済ませようとしていますが そのセルの値を変換して テンプレートに反映させたいのですがうまくいきません

    ‘メール宛先・本文シート記載の値を変数に入れます。
    strTag1 = Worksheets(1).Range(“B” & intNum).Text ‘
    strTag2 = Worksheets(1).Range(“D” & intNum).Text
    strTag3 = Worksheets(1).Range(“E” & intNum).Text ‘
    strTag4 = Worksheets(1).Range(“H” & intNum).Text ‘
    strTag5 = Worksheets(1).Range(“AD” & intNum).Text ‘
    strTag6 = Worksheets(1).Range(“AG” & intNum).Text ‘
    ここまで問題なしです ここから取得できません
                セル指定であ書いてみたのですが できません
    strTag8 = Worksheets(1).Range(“D44”).Value & intNum.).Text

    ‘ strTag8 = Worksheets(1).Range(“D45” & intNum).Text
    ‘ strTag9 = Worksheets(1).Range(“D46” & intNum).Text ‘
    ‘ strTag10 = Worksheets(1).Range(“D48” & intNum).Text
    ‘ strTag11 = Worksheets(1).Range(“D49” & intNum).Text
    ご教授ください

  • コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です