Excel VBAとテンプレートで作る!動的メール自動生成ツールの構築方法

メールはビジネスコミュニケーションにおいて最も強力なツールの一つです。メールの生成を自動化できれば、時間を大幅に節約できるだけでなく、表現の揺れを防ぎ、一貫性のあるやり取りを実現できます。Excel VBAを活用すれば、テンプレートをもとに宛先ごとにパーソナライズされたメールを自動生成する「動的メールジェネレーター」を簡単に構築できます。

この記事では、Excelでテンプレートを使用した動的メールジェネレーターを作成する手順を、初心者にもわかりやすく解説します。

前提条件

• Microsoft Excel(2016以降)
• Excelの基本的な操作知識
• VBAエディターへのアクセス([開発]タブが有効になっていること)

ステップ1:Excelブックのセットアップ

データシート(Data)の作成

• 「Data」という名前の新しいシートを追加します。
• 受信者情報のための列を作成します。
  • 名(First Name)
  • 姓(Last Name)
  • メールアドレス(Email)
  • 会社名(Company)
  • 部署(Department)
  • 役職(Role)
  • 商談トピック(Meeting Topic)
  • アクション項目(Action Item)
  • 差出人名(Sender Name)

テンプレートシート(Template)の作成

• 「Template」という名前の別のシートを作成します。
• 以下の列を作成します。
  • テンプレートID(Template ID)
  • テンプレート名(Template Name)
  • 件名(Subject Line)
  • メール本文(Email Body)

ステップ2:メールテンプレートの作成

{{FirstName}} や {{Company}}、{{Department}}、{{Role}} などのプレースホルダーを使ったメールテンプレートを作成すると、送信時に実際の値へ自動的に置き換えられます。

テンプレート1:オンボーディングメール

まずは、入社時の案内を自動化するオンボーディングメール用のテンプレートを作成しましょう。

Dear {{FirstName}},
Welcome to {{Company}}! We're excited to have you on board.
Your account has been set up with the following details:
Department: {{Department}}
Role: {{Role}}
Best regards,
HR Team

このテンプレートは自動オンボーディングメール向けです。送信前にプレースホルダーが実際の値に置き換えられるため、受信者ごとにパーソナライズされたメッセージが届きます。

各プレースホルダーの意味:

• {{FirstName}}:従業員の名(ファーストネーム)
• {{Company}}:会社名
• {{Department}}:新入社員が配属される部署
• {{Role}}:従業員の役職・職務

テンプレート2:商談後のフォローアップメール

次に、打ち合わせ後のフォローアップ用テンプレートを作成します。

Hi {{FirstName}},
Thank you for your time during our discussion about {{Meeting Topic}}. As discussed, I'm following up on {{Action Item}}.
Let me know if you have any questions.
Best regards,
{{Sender Name}}

ステップ3:VBAコードの挿入

VBAエディターを開く手順は以下の通りです。

• [開発]タブ >> [Visual Basic] を選択します。
• [挿入] >> [標準モジュール] をクリックします。
• 以下のコードをコピーして貼り付けます。

Option Explicit
Public Sub GenerateEmails()
 Dim ws As Worksheet
 Dim templateWs As Worksheet
 Dim lastRow As Long
 Dim i As Long
 Dim emailBody As String
 Dim subjectLine As String
 Dim templateID As Long
 
 ' Set references to worksheets
 Set ws = ThisWorkbook.Sheets("Data")
 Set templateWs = ThisWorkbook.Sheets("Templates")
 
 ' Find last row with data
 lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
 ' Get template ID from user
 templateID = InputBox("Enter the Template ID number:", "Select Template")
 
 ' Get template text
 subjectLine = GetTemplate("Subject_Line", templateID)
 emailBody = GetTemplate("Email_Body", templateID)
 
 ' Convert special characters to proper line breaks
 emailBody = Replace(emailBody, "\n", vbNewLine)
 
 ' Create Outlook items
 Dim outlookApp As Object
 Dim emailItem As Object
 
 Set outlookApp = CreateObject("Outlook.Application") 
 ' Loop through each row of data
 For i = 2 To lastRow
 ' Create new email
 Set emailItem = outlookApp.CreateItem(0)
 
 With emailItem
 ' Replace placeholders with actual data
 .Subject = ReplaceFields(subjectLine, i, ws)
 .Body = ReplaceFields(emailBody, i, ws) ' Changed from .HTMLBody to .Body
 .To = ws.Cells(i, 3).Value ' Email address in column C
 .Display ' Display email (change to .Send to send automatically)
 End With
 Next i
 
 Set outlookApp = Nothing
End Sub
Private Function GetTemplate(field As String, templateID As Long) As String
 Dim templateWs As Worksheet
 Dim templateRow As Range
 
 Set templateWs = ThisWorkbook.Sheets("Templates")
 
 ' Find the template row
 Set templateRow = templateWs.Columns(1).Find(What:=templateID, LookIn:=xlValues, LookAt:=xlWhole)
 
 If Not templateRow Is Nothing Then
 Select Case field
 Case "Subject_Line"
 GetTemplate = templateWs.Cells(templateRow.Row, 3).Value
 Case "Email_Body"
 GetTemplate = templateWs.Cells(templateRow.Row, 4).Value
 End Select
 End If
End Function
Private Function ReplaceFields(text As String, rowNum As Long, ws As Worksheet) As String
 Dim result As String
 result = text 
 ' Replace all field placeholders with actual data
 result = Replace(result, "{{FirstName}}", ws.Cells(rowNum, 1).Value)
 result = Replace(result, "{{LastName}}", ws.Cells(rowNum, 2).Value)
 result = Replace(result, "{{Company}}", ws.Cells(rowNum, 4).Value)
 result = Replace(result, "{{Department}}", ws.Cells(rowNum, 5).Value)
 result = Replace(result, "{{Role}}", ws.Cells(rowNum, 6).Value)
 result = Replace(result, "{{Meeting Topic}}", ws.Cells(rowNum, 7).Value)
 result = Replace(result, "{{Action Item}}", ws.Cells(rowNum, 8).Value)
 result = Replace(result, "{{Sender Name}}", ws.Cells(rowNum, 9).Value)
 
 ReplaceFields = result
End Function

ステップ4:メールジェネレーターの実行

• [開発]タブ >> [マクロ] をクリックします。
• GenerateEmails を選択 >> [実行] をクリックします。

• 表示されたメッセージボックスにテンプレートID:「1」を入力します。
• テンプレート1に基づいたすべてのメールが生成されます。

• 再度コードを実行します。
• 今度はメッセージボックスにテンプレートID:「2」を入力します。

すると、テンプレート2に基づいたすべてのメールが生成されます。

• 送信前に、生成されたメールの内容を必ず確認しましょう。
• 確認が不要な場合は、.Display の代わりに .Send プロパティを使うことで、すべてのメールを自動送信できます。

カスタマイズのヒント

新しいプレースホルダーフィールドの追加

• Data シートに新しい列を追加します。
• VBAコード内の ReplaceFields 関数を更新し、新しいフィールドを組み込みます。
• メールテンプレートには、{{FieldName}} の形式で新しいプレースホルダーを追記します。

HTML形式での装飾

メールテンプレートにはHTML形式の書式を含めることもできます。

<p style="color: blue;">This text will be blue</p>
<strong>This text will be bold</strong>

まとめ

このチュートリアルでは、事前定義済みテンプレートとVBA自動化を組み合わせて、Excel上で動的なメール生成環境を構築する方法を解説しました。この仕組みは、ビジネスコミュニケーション、顧客へのフォローアップ、リマインダーの自動送信など、さまざまな場面で活用できます。今回のメールジェネレーターは土台となるものなので、自分のニーズに合わせて自由にカスタマイズしてください。テンプレートを作成し、データシートとVBAコード内のプレースホルダーを更新するだけで拡張できます。本番環境で使用する前には、十分なテストを行うことを忘れないようにしましょう。