ET: Using MS Access to fill Word Mail Merge Documents, by Dale Fye - Access ET

When merging information from Access into Word, there are different ways to go about it. Perhaps you have a table in Access with information for a letter and then need to decide which Word template should be used based on what type of letter they should get. Other times, Access may completely generate a document based on data in Access and what needs to be there.
Using MS Access to fill Word Mail Merge Documents
Dale Fye ( dev-soln.com )
19 Feb 2021
Using Word to create and format documents is much easier than trying to format a lot of text on an Access report such as for letters, instruction manuals, and legal documents. Word also makes it easier for the users to control the output since all they need to do is edit a Word document as opposed to giving text they want to see to the application developer, and then waiting for them to set up or change an Access report.
Once a merge document has been created by those who know what it needs to say, merge fields can be used to indicate where information from Access needs to be inserted. Conditionally text can be included based on values in the database. All of this is demonstrated so you can learn how to do it. You can also download the database and supporting files so you can try everything that Dale demonstrates for yourself!
Dale shows you all the steps to become a whiz at using Access to fill Word documents, and the VBA code you need to make that happen from creating the data file for the merge to opening Word, loading documents, merging data, and creating the final document with all the merged information.
INDEX -- click on TimeCode link to jump to a spot in the video
00:28 About Dale Fye
02:11 project types when using Access to populate Word documents to Word is beneficial
05:56 Challenges of working with Word
06:11 can't link from Word (for a merge) to an open Access database
06:37 you can write and use a CSV file for the Word Merge with Access data
06:49 A field Caption displays a different column header than the Name of the field, and this hides the real fieldname, even from the developer
07:11 Create data file from Access for use with Word
07:20 step-by-step explanation of VBA code to generate an output file (CreateMailMerge custom function)
08:07 discussion of VBA code to create a CSV file (ExportAsCSV custom function)
08:55 Word automation (Word object model)
09:12 How to use VBA to open Word, load documents, merge data with document
09:20 GetObject, CreateObject (code at 10:01)
09:53 Loading Word using a custom function called GetWord
10:05 vba GetWordDoc
10:34 Mail Merge examples
11:07 simple name and address example: Select Recipients to pick data file
11:44 select MailMerge.csv for the data Word will use
12:06 MailMerge.csv in Notepad++
13:07 Toggle Field Codes
14:31 Letter 2: Name wrap
15:30 \b in field code means to insert characters if the field isn't blank
16:41 IF statements
19:20 Ctrl-F9 to insert new field
22:36 Rules
24:46 VBA code to do the MailMerge
28:29 Word document is open on their desktop
28:49 Cancel won't save the merged document
29:51 GetWord function was used
31:04 Questions and Answers including an interesting discussion about Speech to Text that David Nealey does in his applications.
** DOWNLOAD **
All of the documents (.accdb, .csv, and .docx) used for today’s presentation are included in a zip file you can download from the meeting page:
accessusergroups.org/et/event...
The Access database contains the presentation and all of the code used.
Prior to running the fnMailMerge() you will need to set a parameter to tell the query which records to include. Setting the value to zero will return all records from the sample data table. Setting it to 1, 2, or 3 will only return a single record.
AccessUserGroups.org
ET chapter lead by Dale Fye
Last Tuesday each month at 9am ET -- join us!
SUBSCRIBE for MORE videos! ► bit.ly/AUGRUclips
REGISTER here ► bit.ly/AUGSQL to our virtual chapter!
✓ Join our online Access User Groups with free membership. Monthly webinars on the latest topics: MS Access, MS Access with SQL Server, etc.
► Facebook ◄
/ accessusergroups
/ msaccesslatinoamerica
/ accessespana
/ augpowerapps
/ auglunchtime
/ augeasterntime
/ accesswithsqlserver
► Twitter ◄
/ accessusergroup
/ augespana
/ msaccesslatino

[@mariabarnes7276]

Nice webinar, Dale. I also like to use Word Templates because then as long as they leave the mail merge fields alone, they can edit their own Word documents.

[@AccessUserGroups]

Good point!

[@LearnAccessByCrystal]

Excellent presentation, Dale! Very thorough outline and great teaching! I've never used the \b switch to specify text to insert before the merge field if it isn't blank, so that was especially interesting. Found out you can use \f to insert the text following the field, which lead me to other cool things you can do in a field code that I didn't know about. Thank you.

[@AccessUserGroups]

thank you, Crystal, glad you enjoyed the presentation

[@belpearson]

Great presentation, this has been a great learning experience. I am trying to work through your code, but when there is only 1 record in the CSV, I keep getting an error file created that says "Record 2 was an empty record." How can I stop that from happening?

[@nodirjonsattarov6296]

" I see we lost Ron somewhere during the mix." Hopefully Ron found his way!