Populating multiple Content Controls after mail merge

[guiltyvictim]

Hi guys, first time poster!

So I'm helping a colleague optimise some electronic forms. Basically it's currently setup as this:

1. People fill in a form to bid for some charitable funds
2. The forms' data are imported into a single spreadsheet
3. The data is then mail merged into a word template, for scoring purposes. Each document will contain all the bids, and the documents will be sent out to the charity's trustees to do the scoring
4. Finally all the scored word documents' are imported back into excel to summarise the scores amongst all trustees before decisions are made

Now my colleague's templates basically carry no content control information (title or tags), so he's literally just dumping each word document's content control data into a single line per document.

This means despite there being say 20 bids for each trustee to score, the final step generates 1 line of data, which he'd then copy and paste into another worksheet. All his calculation formulas therefore use direct cell references, and thus mistakes were made and it was impossible to read the formulas and find the errors.

So here's my plan: at step 3, add a bid reference number as a content control for each bid. During import, the VBA code in my excel will look for the reference number field, and start a new line of data.

The problem is:

• I can't mail merge the reference number into the content control directly, so the most obvious solution is out.

• I can bookmark the mergefield, and I found the code to get the bookmarked range's text. I also found the code to populate content controls, but I don't see how I can do this after a mail merge, ensuring that each bid's ref no will be populated to the content control within the same bid.

• I'm also looking at whether I can just merge the ref number into the header, as I know there's VBA code to grab the header / footer text. But I don't know how to get the content control to grab the header text for the section that it belongs to.

So any suggestions on how I can best tackle this would be greatly appreciated!

Thank you in advance!