Create simple mailmerge

Ease of Use


Version tested with


Submitted by:



Show a userform which creates a mailmerge document from a pre-selected list of DataSource files, showing available mergefields and data values. 


Simply switch between alternate data sources or create/modify documents or templates 


instructions for use


Option Explicit Option Base 1 'Set path to MergeData and file extension Const MergeFolder = "C:\Amerge\" Const FileType = ".rtf" Private Sub UserForm_Initialize() Dim i As Integer, DataFiles, DF 'List MergeData files in array (Form limit is 5 unless more option buttons are added) DataFiles = Array("MergeData1", "MergeData2", "MergeData3") 'Add names to option buttons For Each DF In DataFiles i = i + 1 Controls.Item("OptionButton" & i).Caption = DF Controls.Item("OptionButton" & i).Visible = True Next End Sub Private Sub SetSource(MySource) 'Change merge souce of active document to option button selection ActiveDocument.MailMerge.OpenDataSource Name:=MergeFolder & MySource & FileType, _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _ :="" 'Fill Listbox with field names GetText End Sub Sub GetText() 'Get field names from MergeData file Dim afield For Each afield In ActiveDocument.MailMerge.DataSource.DataFields ListBox1.AddItem afield.Name Next afield End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 'Add listbox item to document ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _ , Text:=ListBox1 End Sub Private Sub ListBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 'Add listbox item to document If KeyAscii = 13 Then ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField _ , Text:=ListBox1 End If End Sub 'Shows highlighted mergefield value Private Sub ListBox1_Click() Label1.Caption = " " & ActiveDocument.MailMerge.DataSource.DataFields(ListBox1).Value End Sub 'Changes datasource to caption value Private Sub OptionButton1_Click() ListBox1.Clear Label1.Caption = "" SetSource OptionButton1.Caption End Sub Private Sub OptionButton2_Click() ListBox1.Clear Label1.Caption = "" SetSource OptionButton2.Caption End Sub Private Sub OptionButton3_Click() ListBox1.Clear Label1.Caption = "" SetSource OptionButton3.Caption End Sub Private Sub OptionButton4_Click() ListBox1.Clear Label1.Caption = "" SetSource OptionButton4.Caption End Sub Private Sub OptionButton5_Click() ListBox1.Clear Label1.Caption = "" SetSource OptionButton5.Caption End Sub 'Hyperlink from logo to VBAExpress Private Sub Image1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) ActiveDocument.FollowHyperlink _ Address:="http://www.vbaexpress.com/", _ NewWindow:=True End Sub

How to use:

  1. Create a Userform with a Listbox, a Label and 5 Option Buttons.
  2. Attach the code to the userform
  3. Create a macro to open the userform, Modal must be False
  4. MFieldSelect.Show False
  5. For general use, copy the MFieldSelect form to the Normal project, or save in appropriate template with DataSource names set accordingly.

Test the code:

  1. Save the attached files into a folder called "C:\Amerge"
  2. Open MergeFieldSelect.doc and open the form with the button
  3. Select an option button.
  4. Select a position for the MergeField to be selected.
  5. Double-Click or Enter selected Mergefield to insert in the document.
  6. Select the next position and repeat.

Sample File:

MergeFieldSelector.zip 31.38KB 

Approved by mdmackillop

This entry has been viewed 219 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express