Microsoft Office Forums - View Single Post - [Solved] Code for mail merge to reference saved excel file

macropod · 12-04-2013, 02:57 PM

To be able to choose a datasource, your document need to be a 'normal' one, not a mailmerge one; otherwise the datasource is already chosen. Only after that can the rest of your code come into play. Accordingly, try:

Code:

Sub RunMailmerge()
Dim sPath As String
'Kill off any previous datasource connections
Application.DisplayAlerts = wdAlertsNone
'Let the user select a datasource
With Application.Dialogs(wdDialogMailMergeOpenDataSource)
  If .Show = -1 Then
    sPath = .Name
  Else
    GoTo NoMerge
  End If
End With
If sPath = "" Then GoTo NoMerge
'Run the merge
With ActiveDocument
  With .Mailmerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource sPath
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    'Disconnect from the datasource
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Saved = True
End With
NoMerge:
Application.DisplayAlerts = wdAlertsAll
End Sub

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.