Code for mail merge to reference saved excel file

macropod · #1 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.

jtemp57 · #2 12-05-2013, 09:02 AM

macropod,

I ran it and it lets me choose my datasource, but I want it to remain with the current word document that has all the merge fields set. The code you gave me opens up a new document with no merge fields. I need the merge fields to remain in my label because the users will not know how to set all this up.

Do I change the .Destination = wdSendToNewDocument to something else?

macropod · #3 12-05-2013, 02:22 PM

Quote:

Originally Posted by jtemp57

I ran it and it lets me choose my datasource, but I want it to remain with the current word document that has all the merge fields set. The code you gave me opens up a new document with no merge fields

No, that's not what's happening. The macro I provided retains all of the mergefields and other content of the document from which it is run. It doesn't simply open a new document. Rather, using the document containing the code, it executes the mailmerge using that document and its mergefields and that process generates a new document containing the mailmerge output.

Quote:

I deleted the .Destination and it works.

Not really; all you've done is killed off the output, which Word will now ask you to specify.

Quote:

Another issue is that it is asking me twice to choose my list from excel before moving on.

To fix that, delete:
.OpenDataSource sPath
Working through the dialog already connects once, so there's no need to do it again. My bad.

Quote:

I also have created a form with a button attached to the code below. How do I get the form to pop up with the Word Document so that I can run the code below?

Without knowing what kind of button, or how you propose to hide it when the output is generated, I can't really say. However, for a more automated approach, move the code to the document's 'ThisDocument' code module and rename it:
Private Sub Document_Open()

jtemp57 · #4 12-16-2013, 09:09 AM

I fixed the issue of it asking me twice to select my list.

I want the rest of the Macro to run automatically after I select my file. I need it to select "OLE DB Database File" and then always select the same list from the excel file say "My_List". Then have it finished ready to be able to select the Print options.

Here is what comes up when I record the macro to do this, but where do I insert this into the code you provided me with? and what do I need to add/delete to make it function properly?

Code:

SQLStatement:="SELECT * FROM `My_List`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VB code: populate combobox from columns in Excel file	billybeach	Outlook	2	04-27-2013 04:38 AM
Mail Merge Code (Default Display)	ochiha_ita	Mail Merge	3	04-22-2013 04:04 AM
Code to stop as "save as" if the mail merge has not been done	tonywatsonmail	Mail Merge	4	04-27-2012 01:57 AM
Mail Merge saved to single .pdf	treetop	Mail Merge	1	08-10-2011 03:28 PM
Mail merge Field Code Manipulation	macjnr	Mail Merge	0	09-10-2009 11:37 AM