Turn Your Excel Forms into Custom PDFs and Email Them with a Click — Here’s How I Built It

April 17, 2025
Uncategorized

Have you ever needed to share a customized form from Excel as a PDF—but wanted more control over the layout, design, and content? That’s exactly the problem I wanted to solve with one of my latest projects. Whether you’re in HR, project management, or running a small business, this Excel solution is your one-stop shop for building smart, automated forms that look great and send themselves.

In this blog post, I’ll walk you through the story of how I created an Excel tool that:

  • Lets you design your own form layout
  • Selects specific rows to appear in the final output
  • Exports the form as a PDF
  • And automatically sends it via Outlook email

No more copy-pasting. No more manual formatting. Just click a button, and let Excel do the heavy lifting.

Why I Built This: The Problem with Traditional Excel Forms

Let’s face it—Excel is powerful, but sharing data from it can get messy. I was working on a project that involved collecting form inputs, filtering responses, and sending them to clients or team members. But every time I had to:

  • Adjust the formatting manually
  • Save the form as a PDF
  • Attach it in an email

…it took way too long.

So I thought: what if I could automate the entire process—form, filter, PDF, email—all from inside Excel? That’s where this tool came in.

How It Works – Excel VBA Form to PDF with Email Functionality

The file is divided into three simple sheets:

  1. Master – Your template layout, fully customizable.
  2. Lists – Your dropdowns and email metadata.
  3. Input – The working sheet that gets generated based on your selection.

Step 1: Build Your Form Layout (Master Sheet)

On the Master sheet, you can design your form however you like:

  • Add your company logo
  • Use custom headers and sections
  • Style it with your branding

You decide what the final PDF will look like.

Only Column B gets exported to PDF—this helps you separate formatting from function. Column A contains a simple X that acts as a toggle for visibility. Only rows marked with X will appear in the final PDF.

Step 2: Set Up Metadata (Lists Sheet)

On the Lists sheet, you can define:

  • The PDF file name
  • Email subject, body, and signature
  • Additional fields for future expansion (e.g., recipients, CC, BCC)

This makes the tool flexible and user-friendly, even for those who don’t write code.

Step 3: Select & Preview (Input Sheet)

When you’re ready to generate a form:

  1. Click Refresh Input
  2. This VBA macro duplicates your Master sheet and names it Input
  3. On the new sheet, select the rows you want to include
  4. Customize any of the values if needed

Step 4: Email the PDF – All with One Click

Once everything’s set, hit the Email PDF button. Here’s what happens:

  • The script filters out non-selected rows
  • It exports the selected data as a PDF
  • It saves the PDF to your desktop
  • Then it creates a new Outlook email, fills in the subject/body from the Lists sheet, and attaches the PDF

And that’s it. You’re ready to hit send.

Behind the Scenes: The VBA Code That Powers It

This tool uses two VBA macros:

  • RefreshInput – Cleans up and prepares the Input sheet
  • EmailPDF – Filters, saves, and emails the PDF

Here’s a sneak peek of what’s happening:

🔄 RefreshInput Macro

vbaCopyEditApplication.DisplayAlerts = False
Sheets("Input").Delete
Application.DisplayAlerts = True

Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Input"

This code deletes any existing Input sheet, duplicates Master, and renames the copy.

✉️ EmailPDF Macro

This one does a lot:

  • Prevents changes to the Master
  • Removes unselected rows
  • Exports the visible portion as a PDF
  • Sends a pre-filled Outlook email with the PDF attached

It even uses the user’s desktop path dynamically, so the PDF always saves in the right place—no hardcoding necessary.

vbaCopyEditSet folder = CreateObject("WScript.Shell").SpecialFolders("Desktop")
filePath = folder & "\" & pdfName & ".pdf"

ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=filePath

The script then creates a new Outlook mail item and fills it in:

vbaCopyEditWith OutMail
    .Subject = listSheet.Range("B3").Value
    .Body = listSheet.Range("B5").Value
    .Attachments.Add filePath
    .Display
End With

Want to add To/CC/BCC? The fields are there—just plug in the values from your sheet.

Use Cases for This Tool

You can adapt this for so many use cases:

  • Client reports: Select the relevant insights and send a polished summary.
  • HR forms: Generate offer letters or onboarding packets.
  • Project assessments: Share custom evaluation checklists.
  • Order summaries: Let sales reps send personalized breakdowns.

Anywhere you need custom PDFs and quick emailing, this tool will save you hours.

Final Thoughts

This project was all about working smarter with Excel. With a little VBA magic, we turned a tedious workflow into a sleek, automated process—one that’s powerful, flexible, and easy to maintain.

If you found this helpful, feel free to share it with a friend or team member who lives in Excel all day. And if you’re ready to go deeper into Excel automation, there’s plenty more where that came from.

Thanks for reading—and happy automating!
— Elad