Mail Merge from Excel

[kopy]

I've created a command button in an Excel spreadsheet to execute a Mail Merge with a Word document using the spreadsheet as the data source. When I execute the procedure, the Word file opens and then when it trys to find the datasource for the mail merge, it attempts to open another copy of the spreadsheet and then hangs.

Any help with this problem will be greatly appreciated.

Thanks,

Kopy

-----------------------------------------------------------

Sub Button12_Click()

Dim WordApp As Object

Set WordApp = CreateObject("Word.Application&quot
WordApp.Documents.Open ("C:\Comp\2002 Supervisor Planning Worksheet Merge.doc&quot
WordApp.Visible = True

WordApp.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
WordApp.ActiveDocument.MailMerge.OpenDataSource Name:="C:\Comp\2003MeritToolMikeTest.xls", ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:="Merge", SQLStatement:="", SQLStatement1:=""

With WordApp.ActiveDocument.MailMerge
WordApp.Destination = wdSendToNewDocument
WordApp.MailAsAttachment = False
WordApp.MailAddressFieldName = ""
WordApp.MailSubject = ""
WordApp.SuppressBlankLines = True
With WordApp.DataSource
WordApp.FirstRecord = wdDefaultFirstRecord
WordApp.LastRecord = wdDefaultLastRecord
End With
WordApp.Execute Pause:=True
End With

End Sub

 

[RobCPA]

You may want to set the datasource in the Word document itself rather than through the Excel code. After that is set, you can simply open the Word file from Excel code and with that active document, run the mail merge. Below is some code that I've used:


ObjWord.Documents.Open mergedoco

Set docword = ObjWord.ActiveDocument
With ObjWord.ActiveDocument.mailmerge
.destination = wdsendtonewdocument
.suppressblanklines = True
.Execute
End With

docword.Close (0)
Set docnew = ObjWord.ActiveDocument