Macro to open Word and Start Mail Merge

[chetnik73]

Hi guys

I have a excel spreadsheet which is used to populate data in a Word document as a a Mail merge.

Does anybody know what code I could use to

a) Open the Mail merge document in Word and
b) Run the Mail Merge

Finally if possible i Would like it to print the merged data.

Ideally I would like this to run from a single macro. This would mean the user could press one button and have the data and send it to the printer without doing anthing else.

Any help on this would be appreciated.

[Killian]

Hi there,

Assuming you just want to click a button in Excel, the code below should be a start. You'll need to set a reference to the Word Object Model in the VBEditor Tools|References first.
However, I know nothing about mailmerging so I guess you'll have to do something with the dataset first to get this to work

Hope it helps...

Sub Main()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim myRange As Range
Dim myMerge As Word.MailMerge
'set up references to a new word doc
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add
'code to pass the excel data into the new word doc
'i've just copied a range in
Set myRange = ThisWorkbook.ActiveSheet.Range("A1:D12")
myRange.Copy
wdDoc.Content.Paste
'do some mailmerge stuff
'this fails - I guess the mailmerge records need to be set up first
Set myMerge = wdDoc.MailMerge
With myMerge
.Destination = wdSendToPrinter
.Execute Pause:=False
End With
wdDoc.SaveAs ("C:\test.doc")
wdDoc.Close
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

[mdmackillop]

Hi,
The simplest way is to add the merge and print code into your mailmerge document
as a document open macro

Private Sub Document_Open()
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveDocument.Close
End Sub

You can open the Word document simply using a hypertext link

Sub DoMerge()
ActiveWorkbook.FollowHyperlink Address:="C:\Atest\MPrint.doc", _
NewWindow:=True
End Sub