Letter Creator (VBA for Word/Excel)

This was a VBA for word/excel project. There is a template letter that is the same for all clients, except for the address, name and a couple of other fields. The user would input the client name, address, … in an excel worksheet. For each row of data in the worksheet a letter would be generated using the template word document.

 

In the figure below you can see the worksheet, with the following fields:

  • Prefix
  • First Name
  • Last Name
  • Company Name
  • Address

Excel Input Data
Below you can see some other fields:

  • Field 1
  • Field 2
  • Prefix
  • First name and last names when 2 clients are to appear on in the letter

Excel Input Data 2

In the template word file there are certain locations on the text file where each of these values will replace. In the figure below you can see the fields that will be replaced by the values in the worksheet:

Letter and Fields
Prefix: The value in column A would go in the prefix section. There are drop down lists to choose these values from:

Column A Drop Down Lists

Company Name: If the value in column D is not empty it would go in the company name section. If it is empty then the rows would all shift up.

Name 1 and Name 2: The program would check the values in column B and C. If they are not empty, a combined text string would be created and printed in the Name 1 section. There would be no Name 2 in this section, therefore the rows would all shift up. If the values in column B and C is empty then a combined text string from the values in column J, K, L, M, N, O would be created and placed in the Name 1 and Name 2 location. In columns J and K the user has drop down lists to choose prefixes from:

Column J drop down list

 Field 1 and Field 2: The values in columns H and I are custom text strings generated from the other columns. They replace the values in Field 1 and Field 2  of the letter.

In another worksheet the user selects the location of the template word file and the output file location using open file dialogs:

Input and Output Paths
After pressing the run button the word files are generated:

Output Files
Below you can see an example letter generated:

Output Letter

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Live Chat