Outlook Template Engine (VBA)

A template engine that pulls outlook templates and replaces values with prompts from VBA

What is it?

A need arouse at my partners business to send regular templates to customers with varying fields. I could have manually programmed all of these templates however I decided to build an engine that could grow with them and allow them to add their own templates.

User journey

New template journey

Inside template outlook

File structure

Prompt example

How does it work?

On launch the vba module scans folder C:\temp\emailtemplate\ for all “.oft” files and presents them in a list to the user. When a list clicks on the list we use the list_click to search the templatefolder for the .txt document, inside of this is the templates human-readable name and various details such as toemail(to – email not toe mail). When the user clicks “launch template” the code scans the oft file and pulls out any %variable% inside for example “Dear %firstname%” it will add this to a list of questions to ask the user.

The user is then shown prompts to fill out, which will replace those variables in the email. by using this method of counting the % we allow users to create a template with an unlimited amount of variables and do not create artificial limits.

After the user completes all of these inputs the code runs that against mail.htmlbody using the VBA “replace” command. 

Then this is shown to the user as a email using this command which saves to drafts then displays the email.
NewMail.Close olSave ‘drafts