I maintain a club membership list of about 400 in Excel. With help
from this group (thanks, Ron!) I have been
able to launch Outlook to automate e-mail to the membership. I'd like
to do the same kind of thing with
Word/mailMerge to generate snail-mail labels.

Now, I use VBA in Excel to do the more tedious aspects of formatting
the addresses, then leave
Excel, open a previously created label layout document with Word, and
thus create a file with the
labels. I would like a one step process, i.e., just select "make
labels" from a menu choice in Excel.

I expect it's a lot the same as launching Outlook from VBA, but I
don't know the details. Can anyone
give me some hints?

TIA

Ed

Ed,
You can probably make a start by recording a macro in Word of the MailMerge
actions.
Transfer the code to Excel and adjust reference to the Word application
instead, same as you have done with Outlook.

NickHK

"Ed" wrote in message
...
I maintain a club membership list of about 400 in Excel. With help
from this group (thanks, Ron!) I have been
able to launch Outlook to automate e-mail to the membership. I'd like
to do the same kind of thing with
Word/mailMerge to generate snail-mail labels.

Now, I use VBA in Excel to do the more tedious aspects of formatting
the addresses, then leave
Excel, open a previously created label layout document with Word, and
thus create a file with the
labels. I would like a one step process, i.e., just select "make
labels" from a menu choice in Excel.

I expect it's a lot the same as launching Outlook from VBA, but I
don't know the details. Can anyone
give me some hints?

TIA

Ed

Sounds like a great idea, Nick. I'll give it a try.

Ed

"NickHK" wrote in message
...
Ed,
You can probably make a start by recording a macro in Word of the
MailMerge
actions.
Transfer the code to Excel and adjust reference to the Word
application
instead, same as you have done with Outlook.

NickHK

Nick,

I followed your suggestion but ran into a problem. Here is the code I
put into Excel VBA function, based on
a recorded Word macro:

Sub JOCLabels()
'
' JOCLabels Macro
' Macro recorded November 25, 2006 by Edward F. Sowell
'
Dim wdApp As Word.Application
Set wdApp = New Word.Application
With wdApp
ChangeFileOpenDirectory "D:\JOC\Membership\"
.Documents.Open fileName:="LabelLayout.doc",
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
With ActiveDocument.MailMerge
' Fails at following stmt "Requested Object is not available"
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
ActiveDocument.SaveAs fileName:="testLabels.doc", FileFormat:=
_
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
ActiveWindow.Close
ActiveWindow.Close
End With

End Sub

As I indicate by the comment, it fails at the wdSendToNewDocument. My
guess is it has to do
with not having a needed Type or Object library in the References.
However, I've selected
Office 10 object library, Word 10 object library, and the MSM Merge
Type Library.

Any ideas?

Ed

"NickHK" wrote in message
...
Ed,
You can probably make a start by recording a macro in Word of the
MailMerge
actions.
Transfer the code to Excel and adjust reference to the Word
application
instead, same as you have done with Outlook.

NickHK

I have been running the macro I created in Word, trying to figure out
why it won't run in Excel.
The difference seems to be that my LabelLayout.doc does not open with
MailMerge attributes.
That is, after opening in the Excel VBA function the
ActiveDocument.MailMerge doesn't have
a DataSource name, QueryString, etc. I tried to do this by setting
them:
ActiveDocument.MailMerge.DataSource.Name = .Path &
"\JOCMembership.xls"
but that doesn't work since .Name is read only.

So, how do I open the LabelLayout.doc file in the Excel function so
that it has MailMerge
attributes?

Ed

Word will follow what Excel's tells it to do. So sounds like a problem with
Word rather than Excel.

Maybe because you have not fully qualified all objects. e.g.
With ActiveDocument.MailMerge

Excel does not know what that means. Maybe you need
With .ActiveDocument.MailMerge

NickHK

"Ed" wrote in message
...
I have been running the macro I created in Word, trying to figure out
why it won't run in Excel.
The difference seems to be that my LabelLayout.doc does not open with
MailMerge attributes.
That is, after opening in the Excel VBA function the
ActiveDocument.MailMerge doesn't have
a DataSource name, QueryString, etc. I tried to do this by setting
them:
ActiveDocument.MailMerge.DataSource.Name = .Path &
"\JOCMembership.xls"
but that doesn't work since .Name is read only.

So, how do I open the LabelLayout.doc file in the Excel function so
that it has MailMerge
attributes?

Ed

I made some headway, again by recording what it was doing in Word.
Instead of accepting the
previously used data source, I said "no". That left it without any
MailMerge functionality, i.e.,
none of the icons on the MM tool bar were lit except the one to select
a data source. I set
Record Macro and used the Select data source icon. The code clipped
from that was
pasted into my Excel function and things got a lot better.

Another problem I had was not being able to access the worksheet in
the open workbook.
Don't know why. Solved that copying the worksheet to another temporary
workbook, then
closing it. With that change the function is working pretty well. It
actually writes the
Word file with the wanted label images.

Only remaining issue is the function leaves WinWord.exe running in the
background, even though
I do things like wdApp.Quit and Set wdApp = nothing. Also, the
temporary xls file stays in the
directory even though I Kill it. Mysteries, mysteries!

Ed


"NickHK" wrote in message
...
Word will follow what Excel's tells it to do. So sounds like a
problem with
Word rather than Excel.

Maybe because you have not fully qualified all objects. e.g.
With ActiveDocument.MailMerge

Excel does not know what that means. Maybe you need
With .ActiveDocument.MailMerge

NickHK

"Ed" wrote in message
...
I have been running the macro I created in Word, trying to figure
out
why it won't run in Excel.
The difference seems to be that my LabelLayout.doc does not open
with
MailMerge attributes.
That is, after opening in the Excel VBA function the
ActiveDocument.MailMerge doesn't have
a DataSource name, QueryString, etc. I tried to do this by setting
them:
ActiveDocument.MailMerge.DataSource.Name = .Path &
"\JOCMembership.xls"
but that doesn't work since .Name is read only.

So, how do I open the LabelLayout.doc file in the Excel function so
that it has MailMerge
attributes?

Ed

Ed,
At a guess, the remaining instance of Word may be caused by a reference
still be held to that app.
From Excel, you need to make sure that you do not have any unqualified
references, everything should go through your appWord object.
Also, all objects should be closed (if applicable) and set = Nothing in the
correct order.

As for Kill not working, I would assume there is still some connection,
either to Excel or Word.

Maybe these 2 points are related.

Post some code, of the basic functionality.

NickHK

"Ed" wrote in message
...
I made some headway, again by recording what it was doing in Word.
Instead of accepting the
previously used data source, I said "no". That left it without any
MailMerge functionality, i.e.,
none of the icons on the MM tool bar were lit except the one to select
a data source. I set
Record Macro and used the Select data source icon. The code clipped
from that was
pasted into my Excel function and things got a lot better.

Another problem I had was not being able to access the worksheet in
the open workbook.
Don't know why. Solved that copying the worksheet to another temporary
workbook, then
closing it. With that change the function is working pretty well. It
actually writes the
Word file with the wanted label images.

Only remaining issue is the function leaves WinWord.exe running in the
background, even though
I do things like wdApp.Quit and Set wdApp = nothing. Also, the
temporary xls file stays in the
directory even though I Kill it. Mysteries, mysteries!

Ed


"NickHK" wrote in message
...
Word will follow what Excel's tells it to do. So sounds like a
problem with
Word rather than Excel.

Maybe because you have not fully qualified all objects. e.g.
With ActiveDocument.MailMerge

Excel does not know what that means. Maybe you need
With .ActiveDocument.MailMerge

NickHK

"Ed" wrote in message
...
I have been running the macro I created in Word, trying to figure
out
why it won't run in Excel.
The difference seems to be that my LabelLayout.doc does not open
with
MailMerge attributes.
That is, after opening in the Excel VBA function the
ActiveDocument.MailMerge doesn't have
a DataSource name, QueryString, etc. I tried to do this by setting
them:
ActiveDocument.MailMerge.DataSource.Name = .Path &
"\JOCMembership.xls"
but that doesn't work since .Name is read only.

So, how do I open the LabelLayout.doc file in the Excel function so
that it has MailMerge
attributes?

Ed