Need to mail merge in Excel? In this tutorial, I’ll show you how to automatically create multiple client billing statements at once, and generate emails in bulk from Excel using VBA — a powerful alternative to mail merge that works directly from your spreadsheet.
Excel doesn’t have a built-in mail merge feature like Word, but you can achieve the same results by combining Excel formulas, tables, and VBA automation. In this step-by-step tutorial, you’ll learn how to generate multiple client billing statements as PDFs and then automatically create Outlook draft emails with the statements attached.
Template Download - If you'd like to use the exact template shown in this video, it’s available here:
https://sharonsmithhr.etsy.com/listing/4462695852/excel-auto-filling-invoice-billing
The download includes:
• Macro-enabled billing statement template
• Non-macro auto-populating invoice template
• All formulas and VBA code used in this tutorial
This workflow is perfect for freelancers, consultants, accountants, and small business owners who need to send monthly client statements or invoices in bulk without manually creating each one.
In this video you’ll learn how to:
• Build a billing statement template that pulls client data automatically
• Use Excel tables and a spill formula to filter transactions by date range
• Automatically generate PDF statements for each client using VBA
• Use Excel to create Outlook draft emails in bulk with attachments
• Customize the VBA code using AI prompts for your own spreadsheet templates
By the end of this tutorial, you’ll know how to turn Excel into a mail merge-style automation tool that can generate and email statements in minutes.
Related Video - Build an Auto-Populating Invoice Template in Excel and Pull-in Client and Item Data Automatically
https://youtu.be/rBF2RAGsrME
TIMESTAMPS
00:00 Mail Merge in Excel Alternative
01:03 Overview of Billing Statement Template
02:01 Statement Settings Table
02:31 Template Formulas Overview
03:45 Validate Template Formulas Work
04:22 Generating Statements in Bulk with VBA
05:20 AI Prompt to Generate VBA Code
06:00 Creating Draft Outlook Emails with VBA
07:39 Running the Mass Create Statements Macros
08:58 Validating Correct Statements Generated
09:42 Running the Bulk Email Macro
10:46 Running Next Month's Statements
#excelvba #excelautomation #exceltutorial #smallbusinesstools
FREE COURSE - How to Create Fillable Forms in Microsoft Word - A Step-by-Step Guide
Create Fillable Forms, Surveys & Questionnaires in Microsoft Word like a Pro: https: https://youtu.be/438pCPCSuG4
CHANNEL LINK
https://www.youtube.com/@SharonSmith
Visit my Channel page on YouTube to see all my videos, playlists, community posts and more!
TEMPLATES
Check out my helpful list of templates available for purchase: https://creatoriq.cc/43c51cv
Thank you for supporting my channel!
CONNECT WITH ME
Visit my website: https://www.sharonsmithhr.com for more information, tools and resources.
LinkedIn: https://www.linkedin.com/in/sharonsmithhr
Twitter: https://twitter.com/SharonSmithHR
Instagram: https://www.instagram.com/sharonsmithlearning/
Facebook: https://www.facebook.com/SharonSmithLearning
GEAR
Blue Yeti USB Microphone: https://amzn.to/2W4SbzV (Great for recording professional sounding audio for your videos!)
Silent Mouse: https://amzn.to/3pxpc25 (This is a really cool mouse!)
Screen Recording Software: https://techsmith.z6rjha.net/NZG5b
Green Screen: https://amzn.to/2DnHsY2
Camera: https://amzn.to/39KvpQA
Live Stream Tool: https://amzn.to/2VFJyID (Turns your DSLR into a top notch webcam)
Links included here are affiliate links. If you click on these links and make a purchase, I may earn a small commission at no additional cost to you. Thanks for supporting this channel!
SUPPORT THIS CHANNEL
- Hit the "$Thanks" button on any video, or
- Donate through my PayPal link: https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_button_id=AJJ6SXERNDMYA&source=url
If you found this content helpful, please consider donating to my channel. Your donation, no matter what amount, is greatly appreciated and goes towards producing more content that enhances your productivity and elevates your skills.
You can also support my channel just by watching, liking, and sharing all my videos! Thank you so much!
PLAYLISTS
Jotform Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuO84pPbnHdTdgurmxHgZcyD
Creating Fillable Forms: https://www.youtube.com/watch?v=YktZ3B-tyqs&list=PLb80efKTbWuOe1LfFMNExEabkRkB5ehcv
Excel Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuNjHDGgNeuGrGwOvh4JQC90
Excel Tips & Tricks: https://www.youtube.com/playlist?list=PLb80efKTbWuPI7fEWtroXHvJHhx5FnVMW
Word Tutorials: https://www.youtube.com/playlist?list=PLb80efKTbWuMotoYEXBFMlB3PtsBlFn45