MailMerge a pdf book of abstracts from submitted project titles and abstracts

NewImageIn modules LSM1303 Animal Behaviour, LSM2251 Ecology and the Environment and GEM1917 Understanding & Critiquing Sustainability, students present project results in a symposia. A pdf book of abstracts is prepared for use during the symposia from title and abstracts recruited earlier using a Google Form.

I allow students very close deadlines, such as the Sunday before a Monday symposium, even if some editorial work is required! This is because the pdf book of abstracts can be generated in minutes, based on student submissions through a Google Forms document. Once the deadline is reached, an Excel version of the Google Forms responses is downloaded and processed.

I have been using the Mail Merge > Catalog function in Word 2011 for Mac to do this, but need to teach others to do this. It is likely that some will be using Word 2016 for Mac, and when I took look, it wasn’t all that straightforward. Online resources helped me figure it out, so I’m listing the steps here for my friends, and also for myself, for the next time I prepare this.

  1. Download the Google Forms data as an Excel File and ensure the following field are available:
    • Group Number
    • Full name of person submitting abstract
    • Project Title
    • Project Abstract
  2. Verify the group number using Excel’s VLOOKUP against the module database of group members and project numbers (overcomes submission mistakes)
  3. Use Excel’s VLOOKUP to extract the proper full names of all group members from the module database
  4. Close the Excel file of abstracts
  5. Open a new document in Word 2016 for Mac, and select the “Mailings” ribbon
  6. Select Start Mail Merge > Directory (this provides a continuos flow of records for the book of abstracts, instead of section breaks between records in a letter mail merge)
  7. Start Mail Merge > Directory – to obtain a continuous flow of text
  8. Select Recipients > Use An Existing List… (select the excel file of abstracts)
  9. Select Recipients > Use an Existing List… – select your Excel file of abstracts

    Selecting my Excel file of abstracts

  10. You will be warned to only open a known file, and prompted to open the workbook
  11. Use “Insert Merge Field” to build the output document template, by inserting text and fields. e.g. Group No. «Group_Number»: – inserts the words, “Group No.” before the point at which the number is inserted.
  12. Apply formatting as needed (e.g. bold, change font size)
  13. Appearance of the Mail Merge template, after inserting text and fields
    Screenshot 23

  14. Specify the Merge Range (if the workbook range was not restricted, specify a range to prevent junk at the end of file. E.g. a range of 1 to 40 if there are not more than 40 records (groups)
  15. Finish and Merge > Edit Individual Documents…
  16. Save the Word document, edit as wanted and print as a PDF

The book of abstracts took minutes to generate. You’re on your own for editing, though!Screenshot 24

After using the Mailings ribbon in Word for Mac 2016, I realise the buttons are arranged logically for the task. And instead of “Catalog” what I was wanting was a “Directory”. So once the teething blues are over (and the angst online from 2015 mostly reflects this, I suspect), I’m back in business!

It is a useful practise to only edit submissions at the source, i.e. in the Excel file. If improved abstracts are invited (through a Google Form), or a second round of editing is exercised by a colleague, the new pdf can be quickly generated by the Word template, and the entire outout be cut and paste into a webpage (see Sustainability Symposium in Singapore).

Efficiency means lecturers can maximise the time available to students to write their abstract, which naturally improves significantly, the closer they get to their presentation date. It isn’t easy writing an abstract and further analysis of fresh work will introduce refinements. This applies to participants at higher level symposia too. Since generating the abstract booklet is fast, I always afford speakers the opportunity to provide revised abstracts right up to the day they speak, so that the permanent record on the web is the best possible version they could have wanted.

So the same techniques will be applied at the 13th International Otter Congress, 3rd – 8th July 2016 in Singapore.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s