Cleverly use Word mail merge to semi-automatically generate activity photo record documents.

This semester, I started to get involved in administrative affairs. Nowadays, teaching, moral education, safety, and other work all emphasize the principle of "leaving a trace after the event". Various activities require "careful planning and detailed records" - including writing proposals, taking photos, writing briefs, and printing activity photos. There are at least 3-4 sets of activity photos to be recorded every week, and the typesetting work is indeed quite tedious.

Use Quicker to generate "Three Tables" every week

Last year, when I tried to use Quicker to automatically generate absence registration, abnormal body temperature, and health condition statistics tables, I thought about finding a method that can semi-automatically or automatically generate activity photo records to improve the efficiency of such affairs.

💡 Sort out ideas#

The activity photo record document mainly consists of titles, photos, and photo descriptions. A template can be built on this basis for repeated use.

Open Word documents in Docx format with compression software

Originally, my idea was that a Docx file is essentially a compressed package. By directly replacing the images in the compressed package and manually modifying the titles, the activity photo record could be completed.

However, this idea was quickly shattered by the "slap on the thigh" format standard from above - under each photo, text must be used to explain the time, location, theme, and organizer.

The authors in the minority group gave a lot of suggestions, such as using the python-docx library and the method of email merging. Power Automate involves the automation flow of Excel and Word, which requires a subscription to the advanced version to use. Because I don't know Python and VBA, I finally chose Word's email merge, and I'll leave Word to solve Word's problems (:

Note: The following steps will create two files - Information Template.xlsx and Email Merge Template.docx, and use F:\Working Files\Photo Collection\ as the folder for storing activity photos. Please change them according to the actual situation.

🎬 Video tutorial#

🎬 Video viewing link: Bilibili / YouTube

📋 Preparation#

Create information template#

Word mail merge can apply the information from the data source to a template with the same format, which is suitable for generating a large number of personalized documents.

In the first step, I created an information field template for the activity photo record in Excel, such as Activity Theme, Activity Date, and Activity Photo Storage Folder. With these basic information, the remaining fixed information can be generated using formulas.

The Excel workbook is divided into two worksheets - Enter Data and Generate List. The former is used to enter relevant information, and the latter generates the information for Word to reference. It should be noted that because the IncludePicture field in Word handles backslashes \, the single backslashes \ in the folder and photo file need to be replaced with double backslashes \\.

The data in the Photo Folder and Photo Path columns can be automatically processed by formulas, such as ="F:\\Working Files\\Photo Collection\\"&B2 to generate the folder path, and =E2&"\\1.jpg" to generate the photo file path (the photos are renamed in the format of 1.jpg, 2.jpg, 3.jpg, etc.).

Enter data table content

Generate list table content

After editing, save the Information Template.xlsx spreadsheet.

Create Word template#

In the second step, create a template for the activity photo record according to the file format requirements. This step can directly use the previously created document.

Create a basic template for the activity photo record document

🔗 Perform mail merge#

In the third step, perform mail merge. In Word, switch to the Mailings tab and enter the Mail Merge Wizard. Use the information field template created just now as the data source. Next, we need to insert merge fields where information needs to be filled in the document.

Mail Merge Wizard operation process

When you click "Finish & Merge" at this step, congratulations, you will get an activity record without photos and not meeting the requirements. To automatically insert photos and meet the requirement of two photos per page, you need to use a little more field codes.

Incorrect format

Field codes#

Word field codes can be seen as dynamically updated content used to insert automation content, functions, or data into a document, such as page numbers, dates and times, mail merge, document properties, cross-references, figure sequences, etc. Here, we need to use the IncludePicture field and the Next Record field.

The IncludePicture field can insert a picture with a specified path into the document. In the Insert tab, find Quick Parts - Field - IncludePicture, fill in the word "Path" as the file name, and click OK to insert it at the specified position in the document.

Because the file path for the picture has not been filled in yet, the picture cannot be loaded at this time. Press Alt + F9 to display the field codes of the current document, and replace the word "Path" in the IncludePicture field with the photo path field.

Insert photo path field in the IncludePicture field

By default, Word generates a new document on a per-page basis. To insert multiple data records into the same document, we need to use the Next Record field.

The usage is simple. In the Mailings tab, find Rules - Next Record, and insert it at the end of the photo description. Then, copy, paste, copy, and paste the photo and its description.

Insert the Next Record field

Generate the document#

After completing the above steps, you will get a template for the activity photo record. In the Mailings tab, click Finish & Merge - Edit Individual Documents. After generating, select all content with Ctrl + A, update the field content with F9, and the generated document photos will automatically update to the photos in the folder.

Effect of generating the document with mail merge

✅ Advanced gameplay#

Note: The following VBA macro code was generated by ChatGPT, and I am responsible for combining, splicing, and carrying it.

In addition to completing mail merge manually, you can also reduce the steps required to generate documents using VBA macros and shortcuts.

First, save Information Template.xlsx and Email Merge Template.docx as Information Template.xlsm (a macro-enabled workbook) and Email Merge Template.docm (a macro-enabled document) respectively. Macros need to be enabled in both Word and Excel, and the document directory should be set as a "Trusted Location" (Word Options - Trust Center - Trust Center Settings - Trusted Locations).

Then open Information Template.xlsm, press Alt + F11 to enter the VBA editor, click Insert - Module, and insert the following code in the editing box. After editing, press Ctrl + S to save and close the VBA editor.

Sub EnterMailMerge()
    Dim objWord As Object
    
    ' Create a Word application object
    Set objWord = CreateObject("Word.Application")
    
    ' Open the Word document (replace with the path of your Word document)
    objWord.Documents.Open "F:\Working Files\Email Merge Template.docm"
    
    ' Make the Word application visible
    objWord.Visible = True
    
    ' Save the current workbook
    ThisWorkbook.Save
    
    ' Quit Excel
    Application.Quit
End Sub

In Excel, switch to the Developer tab, click Insert - Button (Form Control), and draw a button on the worksheet. After drawing, right-click the button, click Assign Macro, and select the EnterMailMerge macro created just now. After clicking the button, the email merge template will be automatically opened, and the Excel program will be closed.

Create a macro in Excel to enter the document and close Excel

Next, open Email Merge Template.docm, press Alt + F11 to enter the VBA editor, click Insert - Module, and insert the following code in the editing box. After editing, press Ctrl + S to save and close the VBA editor.

Sub PerformMailMerge()
    ' Perform mail merge
    ActiveDocument.MailMerge.Execute
    
    ' If needed, you can add other subsequent operations here
End Sub

In Word, you cannot assign a macro to a button like in Excel, but you can run a macro using a keyboard shortcut. Right-click in the Mailings tab, select Customize the Ribbon. In Word Options, click Keyboard shortcuts: Customize, select Email Merge Template.docm for Save changes in, and find Macros in the Categories. Select PerformMailMerge and assign a keyboard shortcut combination. I assigned Alt + Shift + N here, and click Assign after assigning.

Assign a keyboard shortcut to a macro in Word

💭 Conclusion#

In addition to the above methods, you can also use other tools to achieve the same goal, such as Power Automate, the python-docx library, Quicker, etc. Cleverly using some automation tools can help you efficiently complete repetitive work tasks and achieve the goal of "touching fish as soon as possible".