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:
- First Name
- Last Name
- Company Name
- Field 1
- Field 2
- First name and last names when 2 clients are to appear on in the letter
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:
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:
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: