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

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

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

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

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

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

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

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

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

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

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

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

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

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

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

タカヒロ

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

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

今回の機能の処理は以下の通りExcelからOutlookを起動し、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型となります。

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

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

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

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

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

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

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

タカヒロ

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

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

タカヒロ

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

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

VBAを実行する

メールを作成・表示する

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

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

メールを送信する

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

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

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

タカヒロ

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

今回のVBAについて説明

「メール宛先・本文」シートのA列最終行を取得し、件数分処理を繰り返します。

「メール宛先・本文」シートのA列載せるに値があればメール作成・送信処理を継続します。

メールテンプレートを設定します。

メールテンプレートの指定が無い、もしくは指定テンプレートが存在しない場合は警告表示および処理を中断します。

Outlook.Applicationを呼び出し、セットします。

メール宛先・本文シート記載の値を変数に入れます。

テンプレートのメール本文の箇所を読み込み、変数に入れます。

メール本文中のタグを指定データへ置換します。

本文をBodyプロパティにセットします。

テンプレートからタイトルを読み込みます。

タグ部分を Replaceメソッドで指定した値へ置換します。

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

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

＜追加＞作成したメールの差出人を変更する方法

作成したメールの差出人を変更する方法について説明をします。
サンプルは、デフォルトの差出人が「takahiro1」である場合、2つ目のアドレス「takahiro9」に変更する内容となります。

ソースコードの変更箇所はCase “表示”の中となります。以下のように処理を追加していきます。
■変更前

■変更後

実行してみましょう。

はい、差出人が2つ目のアドレス「takahiro9」に変更されていますね。

SendUsingAccountプロパティを使用して、MailItem「objMail」の差出人アカウントを指定しています。
差出人アカウントはOutlookアプリケーション「objOutlookApp」のSession.Accounts.Itemにありますので、SMTPアドレスで指定しています。またSMTPアドレス以外にも順番をあらわす数値でも指定が可能です。

＜追加＞作成したメールを下書きへ保存する方法

作成したメールを下書きへ保存する方法について説明をします。

同じくCase “表示”のコードを以下のように変更をします。
■変更前

■変更後

VBAを実行してみます。

はい、下書きフォルダへ保存されていますね。

タカヒロ

先に差出人を変更した処理をした場合、変更した差出人の下書きフォルダへ保存されるのでご注意ください。

＜追加＞作成したメールをMSG形式で指定フォルダへ保存する方法

作成したメールをMSG形式で指定フォルダへ保存する方法について説明をします。

保存先のフォルダは「F:\MSG」、MSGファイルのファイル名はメールの件名となるようにしています。

同じくCase “表示”のコードを以下のように変更をします。
■変更前

■変更後

VBAを実行してみます。

はい、指定フォルダへ保存されていますね。

SaveAsメソッドはOutlookアイテムを、指定したパスに、指定したファイルの種類の形式で保存することができます。
書式は以下の通りです。

TypeはMSGタイプの他にolHTML、olRTF、olTemplate、olDoc、olTXT、olVCal、olVCard、olICal、olMSGUnicode などが指定できます。

＜追加＞作成したメールにファイルを添付する方法

作成したメールにファイルを添付する方法について説明をします。

添付ファイルは「F:\test」にある2つのExcelファイルです。

表のQ列目に添付ファイルのパスを記述するスペースを設け、添付したいメールに添付ファイルのパスを指定していきます。

コードを以下のように変更をします。
■変更前

■変更後

VBAを実行してみます。

はい、指定フォルダへ保存されていますね。

タカヒロ

添付ファイルの操作方法（2ファイル以上の同時指定など）については以下の記事にもまとめていますので、よろしければご参考ください。

【Outlook VBA】添付ファイル付きでメールを作成/送信する方法！複数ファイルも可！

＜追加＞メール本文にハイパーリンクをつける方法

メール本文にハイパーリンクをつける方法について説明をします。

基本的にhttpから始まる文字列や＠マーク入りの文字列はリッチテキスト形式であれば貼り付けた段階でハイパーリンクに変換されます。

ただし、「ホームページはこちら」などアドレスと表示内容を別にしたい場合はできません。

そこで、少し技術難易度は上がりますが、HTMLタグを使うことにより対応ができますので、追加説明をしたいと思います。

まず、エクセル側のテンプレートの改行コードをHTMLタグに変更します。

サンプルテンプレートの「諸届テンプレ」を以下のように変更します。

Tag4にHTMLタグ付きの文字列を入れます。

こちらは太字を示します。

hrefでメールリンク先を指定します。

コードを以下に変更をします。

変更した箇所は以下の3点です。

全コード

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

    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 = "<br>" '改行コード



    '「メール宛先・本文」シートの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 = 2 ' 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
                .To = Worksheets(1).Range("E" & intNum).Value
                .CC = Worksheets(1).Range("F" & intNum).Value
                
        
                '本文をhtmlBodyプロパティにセットします。
                .htmlBody = 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

設定が完了したらVBAを実行してみましょう。

はい、リンクが有効になり、太字にもなっていますね。

リンクをクリックするとメールが開きます。

タカヒロ

メールを起動させるのではなく、ブラウザが開くハイパーリンクにしたい場合は、
hrefの横にある「mailto:」を外し、httpから始まるアドレスを入力してください。

＜追加＞メール本文に特定の書式やフォントを設定する方法

メール本文に特定の書式やフォントを設定する方法について説明をします。

まず、使用するコードは上記ハイパーリンクをつける仕様のコードになりますので、
「HTMLメール作成および送信」のコードを実装するようお願いします。
理由は書式設定はHTMLコードで記述するためです。

次に書式を設定したデータを用意します。
今回設定する書式は以下の通りです。

フォントは

を指定します。

まずはタグに設定する範囲で書式及びフォントを設定してみましょう。

全体の設定は以下の通りです。

テンプレートシートの内容もHTMLタグに対応させるため、改行コード<br>を追加します。

設定が終わったら、ハイパーリンクで紹介したコード
「HTMLメール作成および送信」でVBAを実行します。

はい、メールが作成され、
指定タグ内の書式およびフォントが設定されたことがか確認できました。

次は本文全体にフォント指定をしてみたいと思います。

先ほどのテンプレート本文を以下のように指定したフォントタグで囲みます。

VBAを実行してみましょう。

はい、本文全体のフォントが指定されましたね！

タカヒロ

ただ、よく見ると宛先のところがフォント指定が抜けています。
これは本文外で指定しているためで、お手数ですが、
本文データ入力欄ごとにHTMLタグでフォントを指定するようお願いします。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

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

タカヒロ

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

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

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