【Outlook VBA】Excelから文字サイズ/色/種類/書式を指定しメールを作成/送信する方法!

Outlook VBAでメール本文にExcelで設定したデータに文字サイズ/色/種類など書式を指定しメールを作成/送信したいときはないでしょうか。

たとえば、自動作成したメール本文に決まった書式を適用しなければならない場合などです。

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

・Outlook VBAでExcelで設定したデータと書式でメールを作成したいが方法がわからない。
・Excelで設定したデータと書式から複数のメールを作成したいが方法がわからない。

ですよね。

今回はそんなお悩みを解決する
Outlook VBAでExcelで設定したデータと書式でメールを作成/送信する方法について
まとめます!

Outlook VBAでExcelで設定したデータと書式でメールを作成/送信するイメージ

Outlook VBAでExcelで設定したデータと書式でメールを作成/送信するイメージについて説明をします。

まず、メール本文に挿入したいExcelの表を用意し、本文に該当するセルに書式を加えます。

Outlook側へVBAを実装します。

メールを新規作成したいときにマクロを実行すると、

VBAに設定した内容に沿ってメールが作成され、Excelで設定した書式で本文にデータが挿入されます!

VBAのコードを変えると、送信もできます!

さらに、複数のメールに対してExcelで設定したデータと書式でメールを作成/送信することもできます!!

テキストのみの自動作成メールに対して装飾できるとなると、読み手が注目されるようなおしゃれな演出ができますね。

それでは早速やってみましょう!

Excelで設定したデータと書式でメールを作成/送信するVBA

メールを作成するデータを用意する

Excel側にOutlookのメールを作成するデータを用意しましょう。

サンプルのデータは以下のように1シート目へA列を件名、B列を本文、C列をTo、D列をCcにしました。

また本文のセルに
太字、アンダーライン、文字サイズ大、文字色、書体などの書式を加えています。

件名 本文 To Cc
業務日報(2021年12月10日) 宛先各位

本日業務内容について以下の通り報告いたします。

■業務内容
9:00:朝礼
10:00:商品納品
13:00:定例会
15:00:定期作業
17:00:日報作成

以上/A部タカヒロ

user1@extan.jp user2@extan.jp

VBAの処理の流れ

次にVBAの処理の流れについて説明をします。

処理の流れは以下の通りです。

 

①Outlook VBA→ このExcelブックのデータを書式も含めてちょうだい→ Excel VBA

②Outlook VBA ←データを書式付で送るよ ←Excel VBA

③Outlook VBA →書式付き貼り付けの機能ないので代わりにやって →Word VBA

④Outlook VBA ←Excelデータを書式付きで貼り付けしたよ ←Word VBA

⑤Outlook VBA →メール作成/送信!

 

タカヒロ
タカヒロ
実行するVBAはExcel、Outlook、Wordの3種類で、Excelのメールデータを取り出しOutlookのメールを作成した上、さらにWordエディッタの機能を利用し表を本文へ挿入し、送信するという処理の流れとなります。

VBAの環境設定

ExcelのデータからExcelで設定したデータと書式でメールを作成/送信するVBAの実装方法について説明をします。

ExcelとWordのオブジェクトを使えるようにする設定を行います。

「開発」タブ>「Visual Basic」を押します。

「Visual Basic Editor」にて、[ツール] > [参照設定]をクリックし、
参照設定ウインドウが表示されたら、「Microsoft Excel **.* Object Library」と「Microsoft Word **.* Object Library」をチェックし「OK」をクリックします。

VBAの準備

今回のサンプルコードは以下の通りです。

Sub Outlookのメールを新規作成する_Excelデータと書式を取得()

    'Outlook用の定義
    Dim objMail As Object
    
    'Excel用の定義
    Dim appExl As Excel.Application
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim lnContactCount As Long
    

    'Excelのブックとワークシートのオブジェクトを設定します。
    Set appExl = CreateObject("Excel.Application")
    
    'Excelウインドウを表示させます。非表示としたい場合はFalseを設定してください。
    appExl.Visible = True
    
    '指定したExcelブックを開き、オブジェクトに設定します。パスは環境にあわせて変更してください。
    Set wbBook = appExl.Workbooks.Open("C:\Users\***\Documents\***.xlsx")

     'Excelブック1シート目をオブジェクトに設定します。
    Set wsSheet = wbBook.Worksheets(1)

    '取得結果を記述する行番号を指定します。2行目のセルから開始されることになります。
    lnContactCount = 2

    If MsgBox("Outlookメールを作成しますか?", vbYesNo + vbQuestion, "確認") = vbYes Then
    
        '一覧の件数分繰り返します。
        For i = lnContactCount To wsSheet.Cells(1, 1).End(xlDown).Row
            
            'メールを作成します。
            Set objMail = CreateItem(olMailItem)

            With objMail
                
                .BodyFormat = 3 ' 「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。
                .Subject = wsSheet.Cells(i, 1) '件名を指定します。
                '.Body = wsSheet.Cells(i, 2) '本文を指定します。
                .To = wsSheet.Cells(i, 3) 'Toを指定します。
                .CC = wsSheet.Cells(i, 4) 'CCを指定します。

                'Excel本文項目をコピーします。
                wsSheet.Cells(i, 2).Copy
                
                'メールアイテムをWordEditor経由で編集します。
                Set objWRG = .GetInspector.WordEditor.Range(0, 0)
                
                '予定表本文へExcelの書式付で貼り付けます。
                objWRG.PasteExcelTable False, False, False
    
                '宛先が空欄であるか判定します。
                If wsSheet.Cells(i, 3) = "" Then
                    .Display  'メールを表示します。
                Else
                    .Display 'メールを表示します。
                    '.Send  'メールを送信します。
                End If
           End With
    Next
    
    Else
        MsgBox "処理を中断します"
    End If
    
    'Excelウインドウを閉じます。
     appExl.Quit
     
    'オブジェクトを解放します。

    Set olItem = Nothing
    Set wbBook = Nothing
    Set wsSheet = Nothing
    Set objWRG = Nothing


    MsgBox "Outlookメールの作成が完了しました!", vbInformation
    
End Sub

VBAの設定

VBAの設定をおこないましょう。

以下のパスを作成したExcelブックのものへ変更してください。

Set wbBook = appExl.Workbooks.Open("C:\Users\***\Documents\***.xlsx")

本文のフォーマットを指定します。
「3」の場合リッチテキスト型となります。「1」はテキスト型、「2」は HTML型となります。

.BodyFormat = 3

タカヒロ
タカヒロ
「1」はテキスト型ですと書式がクリアされますのでご注意ください。

VBAの実装手順

実装手順は以下の通りです。

「開発」タブ>「Visual Basic」を押します。

「Visual Basic Editor」にて、[Project1] > [Microsoft Outlook Objects] の ThisOutlookSessionをダブルクリックします。

右ペインのコードエリアへ、VBAコードを貼り付けて保存します。

こちらで完了です。

VBAの実行

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

まずはメールが作成されるか確認をします。

「”Outlookメールの作成が完了しました!”」と表示されたら完了です。

作成されたメールを確認してみましょう。

はい、件名、宛先、本文に指定した値が挿入され、
太字、アンダーライン、文字サイズ大、文字色、書体などの書式もついてみますね!

つづいて送信をしてみましょう。

.Send

のコメントを外し、Sendメソッドを有効化します。

実行してみましょう。

はい、送信されていますね!

VBAの説明

新規にアイテムを作成し、オブジェクトにセットします。

Set objMail = CreateItem(olMailItem)

Excelアプリケーションのオブジェクトを設定します。

Set appExl = CreateObject("Excel.Application")

Excelウインドウを表示させます。非表示としたい場合はFalseを設定してください。

appExl.Visible = True

指定したExcelブックを開き、オブジェクトに設定します。

Set wbBook = appExl.Workbooks.Open("C:\Users\***\Documents\***.xlsx")

タカヒロ
タカヒロ
パスは環境にあわせて変更してください。

Excelブック1シート目をオブジェクトに設定します。

Set wsSheet = wbBook.Worksheets(1)

取得結果を記述する行番号を指定します。2行目のセルから開始されることになります。

lnContactCount = 2

件名を指定します。

.Subject = wsSheet.Cells(i, 1)

本文を指定します。

.Body = wsSheet.Cells(i, 2)

Toを指定します。

.To = wsSheet.Cells(i, 3)

CCを指定します。

.CC = wsSheet.Cells(i, 4)

メールアイテムをWordEditor経由で編集します。

Set objWRG = .GetInspector.WordEditor.Range(0, 0)

予定表本文へExcelの書式付で貼り付けます。

objWRG.PasteExcelTable False, False, False

PasteExcelTableの式は以下の通りです。

PasteExcelTable <LinkedToExcel> ,<WordFormatting>,<RTF> )
<LinkedToExcel>:Trueの場合リンク元の Excelファイルに貼り付けられた表を変更するとこちらにも反映されます。
<WordFormatting>:Trueの場合Word 文書の書式を使用します。
<RTF>:Trueの場合リッチ テキスト形式 (RTF) を使用します。

タカヒロ
タカヒロ
今回、<WordFormatting>が該当しそうですが、これをTrueにするとExcelの枠線と表も入るためFalse、<RTF>は書体や文字サイズは引き継がれますが、色は引き継がれないのでFalseとしています。
結果として3つのパラメータがFalseとなりましたが、合っているか微妙なところがあり、使用の際は十分検証をお願い致します。

メールを表示します。

.Display

メールを送信します。

'.Send

Excelで設定したデータと書式で複数のメールを作成/送信するVBA

次はExcelのデータから複数のExcelで設定したデータと書式でメールを作成/送信するVBAについて説明をします。

タカヒロ
タカヒロ
すでに機能は備わっているのでコードの編集は不要です。

複数メールを作成するデータを用意する

Excel側にOutlookのメールを作成するデータを用意しましょう。

以下のように1シート目へ1行増やして2行にしA列を件名、B列を本文、C列をTo、D列をCcに値を入力します。

VBAの実行

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

「”Outlookメールの作成が完了しました!”」と表示されたら完了です。

作成されたメールを確認してみましょう。

複数のメールが作成されていますね!

Sendメソッドを有効化しメールを送信してみましょう。

はい、送信されていますね!

<追加>修正版VBAコード

宣言文が足りないなどコードに一部不具合がありましたので、修正版コードを掲載します。

Sub Outlookのメールを新規作成する_Excelデータと書式を取得_修正版()

    'Outlook用の定義
    Dim objMail As Outlook.MailItem
    Dim objOutlook As Outlook.Application
    Dim objWRG As Word.Range  ' Wordの範囲オブジェクトの宣言
    
    'Excel用の定義
    Dim appExl As Excel.Application
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim lnContactCount As Long
    
    'ループ用変数
    Dim i As Long

    'Outlookアプリケーションを開始します。
    Set objOutlook = New Outlook.Application

    'Excelのブックとワークシートのオブジェクトを設定します。
    Set appExl = CreateObject("Excel.Application")
    appExl.Visible = True
    Set wbBook = appExl.Workbooks.Open("E:\temp\テンプレ.xlsx")
    Set wsSheet = wbBook.Worksheets(1)
    lnContactCount = 2

    If MsgBox("Outlookメールを作成しますか?", vbYesNo + vbQuestion, "確認") = vbYes Then
        For i = lnContactCount To wsSheet.Cells(1, 1).End(xlDown).Row
            Set objMail = objOutlook.CreateItem(Outlook.olMailItem)
            With objMail
                .BodyFormat = Outlook.OlBodyFormat.olFormatRichText
                .Subject = wsSheet.Cells(i, 1)
                .To = wsSheet.Cells(i, 3)
                .CC = wsSheet.Cells(i, 4)

                wsSheet.Cells(i, 2).Copy
                Set objWRG = .GetInspector.WordEditor.Range(0, 0)
                objWRG.PasteExcelTable False, False, False
    
                If wsSheet.Cells(i, 3) = "" Then
                    .Display
                Else
                    .Display
                End If
           End With
        Next i
    Else
        MsgBox "処理を中断します"
    End If
    
    appExl.Quit
    Set wbBook = Nothing
    Set wsSheet = Nothing
    Set objWRG = Nothing
    Set objMail = Nothing
    Set objOutlook = Nothing

    MsgBox "Outlookメールの作成が完了しました!", vbInformation
End Sub

修正箇所

コードの修正箇所は以下の通りです。
・i はループのための変数として Long 型で宣言
・objWRG は Word の Range オブジェクトとして宣言
・objOutlookをOutlook.Application オブジェクトとして宣言
・Set olItem = Nothing を削除

さいごに

いかがでしょうか。

今回は、
Outlook VBAでExcelで設定したデータと書式でメールを作成/送信する方法について
まとめました。

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



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








6 件のコメント

  • 度々、利用させて頂いております。
    私も、↑上記の井上正文様と同様にobjWRGで止まってしまいます。
    Outlookに実装しており、参照設定も確認しましたが、、、
    思ったのは、objWRGの宣言が無いのかな?と。
    不躾な質問で恐縮ですが、御教示願います。

  • 本文に色を付けているのですが、出来上がったメールは色が付きません。「Microsoft Excel **.* Object Library」と「Microsoft Word **.* Object Library」も参照するよう設定していますし、出来上がったメールはリッチテキストになっています。何か考えられる原因はありますでしょうか。(F8でひとつずつ実行すると、Microsoft Wordが起動しているように見えないのは気になっています。)

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

      メールに色がつかない件ですが、
      Excel側の別のセルの値を参照してしまっている可能性がありますので、
      複数行ある場合は1件のみにし再度確認抱けますでしょうか。

      あとは当方の検証で使用しているOfficeのバージョンは2016以降ですが、
      バージョンによる挙動の違いがある可能性がありますので
      2016以降のバージョンであるかご確認いただきたくお願いいたします。

      なお、Wordが起動しない件について編集機能のみ利用しているため
      実行してもWord本体は立ち上がりませんので問題はありません。

  • お世話になります。 試しに丸ごとコピーして使ってみましたが、 色々エラーでout lookが立ち上がりません。 現在
    Set objWRG = .GetInspector.WordEditor.Range(0, 0)で止まってしまいます。何かアドバイスが有りませんか?

  • コメントを残す

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