[ACCEPTED]-Executing Word Mail Merge-ms-word

If your Word document is already configured 4 with the merge fields, and you are running 3 the macro from the workbook that contains 2 the data you want to merge into the Word 1 document, then try this:

Sub RunMerge()

    Dim wd As Object
    Dim wdocSource As Object

    Dim strWorkbookName As String

    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdocSource = wd.Documents.Open("c:\test\WordMerge.docx")

    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

    wdocSource.MailMerge.MainDocumentType = wdFormLetters

    wdocSource.MailMerge.OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdOpenFormatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `Sheet1$`"

    With wdocSource.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With

    wd.Visible = True
    wdocSource.Close SaveChanges:=False

    Set wdocSource = Nothing
    Set wd = Nothing

End Sub

To get dendarii's solution to work I had 2 to declare Word constants in Excel VBA as 1 follows:

' Word constants
Const wdFormLetters = 0, wdOpenFormatAuto = 0
Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = -16

If your word document is already configured 5 with data source and merge fields layout 4 then it becomes much simpler. In the example 3 below MailMergeLayout.doc is all setup ready to perform a merge. A 2 button in Excel is linked to RunMailMerge() as below. All 1 the code is contained in an Excel VBA module.

Sub RunMailMerge()

    Dim wdOutputName, wdInputName As String
    wdOutputName = ThisWorkbook.Path & "\Reminder Letters " & Format(Date, "d mmm yyyy")
    wdInputName = ThisWorkbook.Path & "\MailMergeLayout.doc"

    ' open the mail merge layout file
    Dim wdDoc As Object
    Set wdDoc = GetObject(wdInputName, "Word.document")
    wdDoc.Application.Visible = True

    With wdDoc.MailMerge
         .MainDocumentType = wdFormLetters
         .Destination = wdSendToNewDocument
         .SuppressBlankLines = True
         .Execute Pause:=False
    End With

    ' show and save output file
    wdDoc.Application.Visible = True
    wdDoc.Application.ActiveDocument.SaveAs wdOutputName

    ' cleanup
    wdDoc.Close SaveChanges:=False
    Set wdDoc = Nothing

End Sub

Private Sub CommandButton1_Click()

 Set wordapp = CreateObject("word.Application")

     wordapp.documents.Open "C:\Documents and Settings\User\Desktop\mergeletter.doc"


    wordapp.Visible = True

    wrddoc = wordapp.documents("C:\Users\User\Desktop\sourceofletters.xls")


   wrddoc.mailmerge.maindocumenttype = wdformletters

   With wrddoc.activedocument.mailmerge

 .OpenDataSource Name:="C:\Users\User\Desktop\sourceofletters.xls", _
           SQLStatement:="SELECT * FROM `Sheet1`"



   End With

End Sub

Above code is to open a word mailmerge document 5 (with its source link and mergefield codes 4 all setup ) all I want is for the message 3 box "Opening the document will run the following SQL command " to be made available to the user , from 2 that point forward the user could either 1 select 'Yes' or 'No'.