FCC Admin Tasks - Creating the Directory using Mail Merge

Creating the Directory using Mail Merge

Once you are satisfied that all updates have been made to the “Persons” tab in the FCCUCC Affiliated Names and Addresses Google sheet and placed any new appropriately named images into the Directory Images Google folder, you are ready to create the latest pictorial directory.

NOTE: The Gdrive URL links behind the Word and Excel files referenced below point to files in the Microsoft Office Files for Data Merge Google drive folder. However, the mail merge functions and the macro in step 21 employ functions only available when accessed through the native Microsoft Word application as well as a specific directory structure unique to the Google Drive desktop app for the office@fortworthucc.org user.

Step by step process:

Launch the windows file explorer and take note of the drive letter assigned to the office@fortworthucc.org user.
Paste the Google drive letter into cell on the Control tab.
Launch Query_Directory tab.
Check the "Query_Directory Cull Partner?" checkbox.
Check the "Query_Directory Run?" checkbox.
<Ctrl-A> + <Ctrl-C> to copy all of the data to the clipboard.
Using File Explorer on your PC, proceed to the directory "\My Drive\Utility\FCC Directory\Microsoft Office Files for Data Merge\Directory“.
Open "Pictorial Directory.xlsx” Excel spreadsheet.
<Ctrl-A> + <Delete> to clear all data.
Paste clipboard contents into cell A1.
Save and exit the"Pictorial Directory.xlsx”. Excel spreadsheet.
Open word document “Pictorial Dir Merge Basis.docx”.
If you get a message stating that an SQL command will run, click “No”.

On the “Mailings” menu, click on “Start Mail Merge" and select “Directory”.
On the “Mailings” menu, click on “Select Recipients”, click on the “Use an Existing List...” and browse/open the spreadsheet "Pictorial Directory.xlsx".

On the “Mailings” menu, click on “Edit Recipient List” and then click on the “Filter” link in the “Refine recipient list” section of the dialog that appears.

Filter/include only rows with non-blank Last_Name.

Click on “Finish & Merge” and “Edit Individual Documents”.

Opt to merge all records on the “Merge to New Document” dialog.

In the merged document, <Ctrl-H> to display the “Find and Replace” dialog.
Click on “More>>” and make sure “Use wildcards” is NOT selected. Find and replace all instances of “^p^p” with “^p” to replace extra lines.

Save the document as “merged.docx” then <Ctrl-A> + <Ctrl-C> to copy all of the text to the clipboard.
Exit/save the document “Pictorial Dir Merge Basis.docx”.
Open the macro enabled word document “Pictorial Dir Draft with lead.docm” and click on “Enable Content” in response to the warning message.

Select the old text to be replaced and <Ctrl-V> to paste the contents of the clipboard over the selected text.

On the “Developer” menu, click on “Macros”, select the “Replaceimages” macro and click on “Run”. This will insert all of the personal images into the document.

<Ctrl-S> to save the document.
Pulldown “File>Save As” and save the document as a non-macro enabled “.docx” Word file.
Make final cosmetic changes to the document like resizing images as needed to pretty things up.
When satisfied, pulldown “File>Save As” and save the document as a PDF file named "FCC Church Directory.pdf".
Navigate to the Public View folder in the Church Repository.
Drag the "FCC Church Directory.pdf" file to the Public View folder to upload/publish it.

** END OF PROCEDURE **