MailMerge Label printing from Excel

[Ed]

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

 

[NickHK]

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]

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

Ed

 

[Ed]

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

 

[Ed]

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

 

[NickHK]

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]

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]

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]

Here is the code for the function. Hope there is a modicum of clarity.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sub JOCLabels()
'
' Use Word MailMerge to write out labels from data in the Labels Data
' worksheet of JOCMembership.xls workbook.
'
' NOTE: Uses the LabelLayout.doc file.
'
' Make copy of Label Data to new temporary tempLabelsWks.xls file
' because can't access it from the open JOCMembership.xls workbook
'
Sheets("Label Data").Select
Sheets("Label Data").Copy
ChDir "D:\JOC\Membership"
ActiveWorkbook.SaveAs
fileName:="D:\JOC\Membership\tempLabelsWks.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
'
' Now use Word to write the label images file.
'
Dim wdApp As Word.Application
Set wdApp = New Word.Application
On Error Resume Next
With wdApp
ChangeFileOpenDirectory "D:\JOC\Membership\"
.Documents.Open fileName:="LabelLayout2.doc",
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False,
PasswordDocument:="", _
PasswordTemplate:="", Revert:=False,
WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"D:\JOC\Membership\tempLabelsWks.xls",
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False,
Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data
Source=D:\JOC\Membership\tempLabelsWks.xls;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet
OLEDB:Eng" _
, SQLStatement:="SELECT * FROM `'Label Data$'`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
'
' Delete the temporary workbook file
'
Kill ("D:\JOC\Membership\tempLabelsWks.xls")
ActiveDocument.SaveAs fileName:="testLabels.doc", FileFormat:=
_
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False,
SaveFormsData:=False, _
SaveAsAOCELetter:=False
End With
On Error GoTo 0
'wdApp.Quit
Set wdApp = Nothing

End Sub



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

 

[NickHK]

You still have :
With ActiveDocument.MailMerge

rather than
With .ActiveDocument.MailMerge
Note the addition of the ".", so this object is part of the wdApp hierarchy.

Also, is "ChangeFileOpenDirectory" from wdApp ?
Should it be ".ChangeFileOpenDirectory" ?
etc

NickHK

 

[Ed]

Once again, thanks Nick. I made the "dot" change, although it the
function clearly was
able to tell one application from another, as it did create the wanted
Word file from
the Excel file. However, some other fiddling with where things were
done got me
to the point where Word is now closing. However, the Kill of the
temporary
Excel file still doesn't work.

I have to divert to a more pressing project right now, but will get
back to this in
a week or so. I am so close it hurts to set it aside!

Ed

 

[NickHK]

Ed,
It worked OK without the ".", but was creating another reference to the Word
app.
Until the reference is 0, a COM object cannot be destroyed. hence your
remaining instance of Word.

As for the Kill, try removing the ( ).

NickHK