EDIT: This must have been my lucky charm. I figured the whole thing out, and I will post the entire code for it in case anyone else has this issue. I'm taking a break from it today because I worked all night, but will post up the code during the week.


I've been wracking my brain and searching the internet to try to do this with no luck.
I have a bunch of templates I need to design, I'll start with the most complicated one because it has everything. The idea is that the selections from the user form will populate both from an excel document and user defined criteria. So essentially mail merge stuff with user criteria.

I would like a user form to pop up as soon as the document is open. The user form will have the following items:

What is the Property Code? (Drop Down List)
What is the F Score? (User typed Text)
What is the P Score? (User typed Text)
What is the M Score? (User typed Text)
What is the C Score? (User typed Text)

Then a checkbox series, [] indicate a check box
Does the property need Counseling for []F []P []M []C

In the letter/template this is the gist of it:

Property Manager
Property Address
Property Name

Dear Property Manager:

On (FYE) your property scored 1 out of 25 for F, 1 out of 25 for P, 1 out of 25 for M, 1 out of 5 for C. Therefore your property needs counseling.

[F]
[P]
[M]
[C]


--------------------------

What I would like is the drop down list to show the property code from the excel source titled "Source" When the user selects the property code, then the Property Manager, Address, Name, Repeat of Property Manager and their FYE all autopopulate as if it were a regular mailmerge.

The scores would populate from the userform text box, these are not stored in the excel file.

Then if it's checked that they need counseling, an autotext paragraph will populate for the reason they need counseling F,P,M,C.

I have a userform that employs all but the autotext with bookmarks, but I want to make this more automated, so that the only thing the user has to do is type the property code and then all corresponding information like name, address, FYE. Right now I'm having them type in all the information in the userform text field which populates my bookmarks.

Other templates will pull other information like units, etc. I think mail merge fields is the only way to have dependent variables like this. I'd rather them not have to select the excel sheet like mail merge. I used GetObject to pull the information from a closed excel book, but I couldn't coordinate my bookmarks.

Thank you!!!