Is there a quicker way to enter every mail merge field when using Word's Mail Merge?

Question

Is there a way to insert every field into a Word mail merge document without clicking Insert Mail Merge for each one. The source data is from an excel document and could potentially have a hundred fields which update each week.

Edit. Example Excel Document.

Name   1   2   3   4   5   6   7
James  85  54  65      36  21  21
John   54  54  14  25  52  54  22
Hamish 41  21  12  35  22      99

I've used this Excel doc as data source in Word's Mail Merge for Emails. I need the following result:

Name: James

1:85
2:54
3:65
5:36
6:21
7:21

I don't know how to automate the process as to insert each field name I have to click on the Insert Mail Merge and select the appropriate field. This is laborious with potentially hundreds of field names.

Macro result

Sub Macro1()
'
' Macro1 Macro
'
'
    Selection.TypeText Text:="Dear "
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Name"
    Selection.TypeText Text:=","
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="M_1"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="M_2"
    Selection.TypeParagraph
    ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="M_3"
End Sub

I can't think of a way to loop through each heading as the real headings aren't in a sequentially named order.

Answer 1

When you look at the macro you recorded you'll see ActiveDocument.MailMerge.Fields. The Fields (plural) means this is a "collection". In many programming languages (including VBA) you can "loop" through a collection - which means you can use each individual item of the group. Recording a macro will give you information about how to address that group.

In the case of mail merge, there are two sets of "fields": those coming from the data source, and those already placed in the document. Your recorded macro give us the second. The designation of the first - what you need - is DataFields.

So in order "to insert each field name", as you ask:

Sub Macro1()
  Dim doc as Word.Document
  Dim dtField as Word.MailMergeDataField
  Dim sFieldName as String

  Set doc = ActiveDocument
  For each dtField in doc.MailMerge.DataSource.DataFields
    sFieldName = dtField.Name
    doc.MailMerge.Fields.Add Range:=Selection.Range, Name:=sFieldName
    Selection.TypeParagraph
  Next
End Sub