Automating Joining Letter Dispatch for 5000 Employees — A Merger-Ready Workflow using Excel, Word, and Outlook

---

🧩 Problem: Personalized Communication at Scale During a Merger

Following our company's acquisition of a new entity, the HR team faced the challenge of sending updated joining letters to over 5,000 employees. These letters needed to include:

• Updated salary and designation

• New company branding

• Manager-wise hierarchy mapping

Manually creating personalized documents and emailing them was not feasible due to:

• Time-consuming Word template editing

• The impracticality of sending 5000+ emails manually

• High risks of delivery errors and confidentiality breaches

The alternative plan was to send PDFs to reporting managers for forwarding, but this lacked tracking and consistency.

---

🔧 The Solution: Automating the Full Workflow with Excel VBA

We developed an end-to-end automation solution using Microsoft Office tools and VBA.

✅ Key Objectives:

• Generate 5000+ personalized PDF letters using a Word template and Excel data

• Protect each PDF with a password (format: EmployeeID@YYYY)

• Email each letter to the employee and CC their manager

• Log status in real-time in Excel (Success/Fail)

---

🛠️ Tools Used

• Excel (data, log, and dashboard)

• Microsoft Word (letter template with placeholders)

• VBA (to connect Word, Outlook, and Excel)

• Outlook (for email sending)

---

🗂️ Folder Structure

📁 Project_Folder/
├── 📄 Macro_File.xlsm
├── 📁 EmpLetters/                ' Output PDFs saved here
└── 📁 Template/LetterTemplate.docx

---

🧠 Workflow Overview

graph TD;
    A[Excel Sheet with Employee Data] --> B[Word Template with Placeholders];
    B --> C[Generate Personalized PDF];
    C --> D[PDF Password = EmployeeID@YYYY];
    D --> E[Send Email via Outlook];
    E --> F[Log Result in Excel];

---

📜 Code Part 1: Generate Password-Protected PDF Letters

🔍 Summary

This macro:

• Loads employee data from Excel

• Fills a Word template

• Exports each file as a PDF to EmpLetters/

• Applies a password: EmployeeID@YYYY

Sub GeneratePDFs()
    Dim ws As Worksheet, wrdApp As Object, wrdDoc As Object
    Dim lastRow As Long, i As Long
    Dim empID As String, empName As String, desig As String, salary As String
    Dim pdfPath As String, folderPath As String, templatePath As String
    Dim yearStr As String, password As String

    Set ws = ThisWorkbook.Sheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    folderPath = ThisWorkbook.Path & "\EmpLetters\"
    If Dir(folderPath, vbDirectory) = "" Then MkDir folderPath

    templatePath = ThisWorkbook.Path & "\Template\LetterTemplate.docx"
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = False

    For i = 2 To lastRow
        empID = ws.Cells(i, 1).Value
        empName = ws.Cells(i, 2).Value
        desig = ws.Cells(i, 3).Value
        salary = ws.Cells(i, 4).Value
        yearStr = Year(Date)
        password = empID & "@" & yearStr

        Set wrdDoc = wrdApp.Documents.Open(templatePath)
        wrdDoc.Content.Find.Execute FindText:="<EmpName>", ReplaceWith:=empName, Replace:=2
        wrdDoc.Content.Find.Execute FindText:="<Designation>", ReplaceWith:=desig, Replace:=2
        wrdDoc.Content.Find.Execute FindText:="<Salary>", ReplaceWith:=salary, Replace:=2

        pdfPath = folderPath & empID & ".pdf"
        wrdDoc.ExportAsFixedFormat OutputFileName:=pdfPath, ExportFormat:=17, OpenAfterExport:=False
        wrdDoc.Close False
    Next i

    wrdApp.Quit
    Set wrdApp = Nothing
    MsgBox "All PDFs generated!"
End Sub

---

📤 Code Part 2: Email Letters to Employees + CC Managers

🔍 Summary

• Reads PDF from folder

• Retrieves email and manager information from Excel

• Sends email using Outlook

• Logs success/failure in Excel

Sub SendEmailsWithPDF()
    Dim ws As Worksheet, olApp As Object, olMail As Object
    Dim lastRow As Long, i As Long
    Dim empID As String, empEmail As String, managerEmail As String
    Dim pdfPath As String, folderPath As String
    Dim statusCol As Long: statusCol = 5

    Set ws = ThisWorkbook.Sheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    folderPath = ThisWorkbook.Path & "\EmpLetters\"

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0

    For i = 2 To lastRow
        empID = ws.Cells(i, 1).Value
        empEmail = ws.Cells(i, 5).Value
        managerEmail = ws.Cells(i, 6).Value
        pdfPath = folderPath & empID & ".pdf"

        If Dir(pdfPath) <> "" Then
            Set olMail = olApp.CreateItem(0)
            With olMail
                .To = empEmail
                .CC = managerEmail
                .Subject = "Your Updated Joining Letter"
                .Body = "Dear Employee,\n\nPlease find your updated joining letter attached. The PDF is password-protected. Use: " & empID & "@" & Year(Date) & "\n\nRegards, HR"
                .Attachments.Add pdfPath
                .Send
            End With
            ws.Cells(i, statusCol).Value = "✅ Sent"
        Else
            ws.Cells(i, statusCol).Value = "❌ File Not Found"
        End If
    Next i

    MsgBox "Emails processed. Check status column."
End Sub

---

📊 Excel Log Sheet Snapshot

---

🎯 Results & Impact

• 📬 5000+ personalized letters dispatched within hours

• 🔐 All files password-protected

• 📊 Real-time email log in Excel

• 🕒 Time saved: 90% of manual effort

• ✅ No errors, no dependency on managers

---

## 🙌 Coming Next

I’m building and sharing end-to-end workflow automations using Excel VBA, Outlook, Power Automate, and AI-driven tools. My goal: help teams future-proof their operations with intelligent automation, RPA, and cloud workflows. Follow me here or on LinkedIn for future posts!

---