I would like to activate a mail merge operation from an excel 2010 vba application. The user should be able, activating a macro in excel, start the printout of a form letter using a selected record. I know that this particular task could be implemented using the bookmarks in word, replacing the bookmarks with the correct fields of the selected record; however, having around 15 or more fields to use, the solution with the mail merge feature seems (to me, at least) more appropriate and more estetically pleasing... So I am experimenting with this possibility. I found some code on the net, adapted it, and I cannot make it work. Here is the code I am using:
==============================
Sub PrintLetter2(recordnumber As Integer)
On Error GoTo ErrorHandler
' open template in Word
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Set WordApp = CreateObject("Word.Application")
With WordApp
.Visible = True
Set WordDoc = .Documents.Open("c:\.....\testform21mar13\formletter.docx")
End With
'MailMerge selected records from table to Word document
WordDoc.MailMerge.OpenDataSource Name:="C:\...\testform21mar13\form.xlsm", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="", SQLStatement:="SELECT * FROM `sheet1$` WHERE((`Record ID`= recordnumber))", SQLStatement1:=""
'send merged document into new Word document
With WordApp
.ActiveDocument.MailMerge.Destination = wdSendToPrinter
.ActiveDocument.MailMerge.Execute
.......
=========================================
The problem is that when the statement in italics is executed the application bombs out (after a while) with the error message "... MS word has stopped working" and so on "trying to recover..." and finally " a problem prevented Word from working correctly, Microsoft will try to find a solution for your problem". Now, this tells me nothing about what I am doing wrong. I don't know if VB7 (of excel2010) implements changes that prevent the code from working correctly, or if there is some known problem in office 2010 about this feature. Are there any information around? I could not find any.
Thanks
Robert, Italy
Bookmarks