Automation · March 2026
VBA Email/PDF Letter Automation
A VBA macro system that automates DIN letter creation and circulation for utility notifications, replacing a manual process that ate roughly 35 hours a year.
This macro system automates the creation and circulation of DIN letters for utility notifications, a process that used to mean manually populating templates and sending each email out one at a time, close to 300 emails and about 35 hours a year of pure data entry. The system compiles project data, populates Word templates, exports the results as PDFs, and generates Outlook drafts with subjects and attachments already customized per recipient.
Three macros carry the workflow. compile_worktype consolidates multiple work types spread across columns into a single readable string per project. pop_template opens the Word templates, fills in content control placeholders with the right contact and project data, applies formatting, and exports each one as a PDF. CreateDraftEmails_OneEmailPerPDF_WithCustomSubject loops through contacts, matches each one to its PDF, pulls the road name out of the filename, builds a standardized subject line, and creates an Outlook draft with the right attachment already in place. Built in about a month, start to finish.
