Solved: Excel to Word mailmerge problem

[lambic]

Hi everyone,

This is my first post so please bear with me!

I'm using Office 2003

I'm trying to automate a mailmerge to Word from Excel. I have a button which poplulates a range in Excel & then opens Word to merge the range data into some labels.

The Excel part works fine & the Word merge document opens, but the merge fails. I have some code in the Word DocumentOpen event, which looks like this:

[vba]With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute[/vba]

However, the code fails at the:

[vba].Destination = wdSendToNewDocument[/vba]

line with the following error:

Run-time error '5852'
Requested object is not available

I'm tearing whats left of my hair out with this, has anybody got any ideas?

Many Thanks

[austenr]

In you code do you have a reference set to this:

Microsoft Word 11.0 Object Library. Not saying this is your whole problem but it might get you a little closer. Also, could you post all of your code?

[lambic]

Hi,

Thanks for the reply. Yep, got the reference, but still have the problem

[mdmackillop]

Is this any help?
http://vbaexpress.com/kb/getarticle.php?kb_id=122

[lambic]

Hi,

Cheers for the suggestion.

I tried the sample code, but it's still failing on the

.Destination = wdSendToNewDocument

line with the 'Requested object not available' error

[lambic]

Sorted it!

I changed the way I was opening the Word doc from Excel from:

.documents.open
etc

to the hyperlink method:

ActiveWorkbook.FollowHyperlink Address:="C:\ etc", NewWindow:=True

as suggested in one of your other threads.

Thanks to mdmackillop & austenr for all your help.

[mdmackillop]

Hi Lambic,
Glad you've got the solution. Can you post your final code for others to see?
Regards
MD

[lambic]

Sure - code in Excel is:

[VBA]
stPathName = Worksheets("Label Generator").Range("Path").Value
stDocName = Worksheets("Label Generator").Range("Document").Value
stDocAndPath = stPathName + stDocName

'Start Word...

ActiveWorkbook.FollowHyperlink Address:=stDocAndPath, _
NewWindow:=True
[/VBA]

and in Word (which has been set up as a mailmerge main doc), I have:

[VBA]
Public Sub MergeDoc()

Dim DocName As String

DocName = ActiveDocument.Name

'Display datasource to check ...

If ActiveDocument.MailMerge.DataSource.Name <> "" Then _
MsgBox ActiveDocument.MailMerge.DataSource.Name

'Perform mail merge ...

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
[/VBA]

Which is called on the Document - Open event when the doc is opened in Excel. The MsgBox bit just displays the datasource & was there to aid my checking.

Thanks again for your help.