Macro execution using MailMerge

[nelsonj]

I have converted a Word 2000 macro over to Word 2003 and recorded a Word
macro to perform a mail merge from a six(6) column table contained in a Word
document. Recording and saving the mail merge steps goes ok ( I used the
'Address Block' format for my lables.)
However, when I execute the macro, the process abends with a MS Visual Basic
run-time error " '509' This command is not available". Clicking on debug
shows me that the macro abended on the line that is calling the

WordBasic.MailMergePropagateLabel routine.

I have looked everywhere for a solution to this abend. I have gone back into
my install of Office 2003 and added in any and all "pieces / parts" of Word
and Office that might have anything to do with Visual Basic, but the macro
always fails. I have a feeling I am missing something in the Office install,
but can not determine what else could be causing this error. Any assistance
/ suggestions are appreciated. J Nelson.

 

[Doug Robbins - Word MVP]

I can't really imagine the circumstance under which I would bother with a
macro to set up a mailmerge main document, but none the less, without seeing
you code it is somewhat hard to tell what is causing the problem, though the
most likely thing is that the mailmerge main document is not, or is not
being recognised as, a label type mailmerge main document.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[nelsonj]

Doug: As you know, macros are used by clerical types to do repeditive tasks
quickly and this macro is used several times a day to generate mailing lables
for our Permitting Department. The code is generated by the "macro
recording" part of WORD, so what you see is what is geerated by WORD. The
routine fails with a 509
runtime error on the line marked with a ++>.

This macro is a re-generation of a working macro in Word 2000 that does not
have this problem. I have tried to enhance the Resources list for VBA in Word
2003 and I have attempted to record this macro using both the Mail Merge
Helper panels and the Mail Merge Help steps, and both come up with the same
error. Here is the code generated by the WORD macro:


Sub APO2()
'
' APO2 Macro
' Macro recorded 06/12/2006 by JNelson
'
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:="C:\list.doc", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
WordBasic.MailMergeOpenHeaderSource Name:="C:\listhdr.doc", _
ConfirmConversions:=0, ReadOnly:=0, LinkToSource:=1, AddToMru:=0, _
PasswordDoc:="", PasswordDot:="", Revert:=0, WritePasswordDoc:="", _
WritePasswordDot:="", Connection:="", SQLStatement:="",
SQLStatement1:="" _
, Format:=0, Visible:=1, OpenExclusive:=0, OpenAndRepair:=0,
SubType:=0, _
DocumentDirection:=0, NoEncodingDialog:=1, XMLTransform:=""
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\list.doc" & ""
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""FirstName"""
Selection.TypeText Text:=" "
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""LastName"""
Selection.TypeParagraph
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""Address1"""
Selection.TypeParagraph
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""City"""
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""State"""
Selection.TypeText Text:=" "
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""PostalCode"""
++> WordBasic.MailMergePropagateLabel
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
ChangeFileOpenDirectory "C:\"
ActiveDocument.SaveAs FileName:="mergelist.doc", FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
End Sub

 

[Peter Jamieson]

The chances are that you are probably encountering the problem covered by
the following KB article:

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765

http://support.microsoft.com?kbid=825765

Peter Jamieson

 

[nelsonj]

Thanks, Peter, but ..."been there; ...done that"....That particular registry
fix was applied to my PC several weeks ago. Any other suggestions why this
particular WordBasic routine can not be found on a full install of Office
2003?
J Nelson

 

[Charles Kenyon]

Word's recorded macros are terrible coding. A number of macros that you
record will not run on the same machine on which they were recorded. That is
a fact of life. In addition, they often contain extraneous and dangerous
material.
--
Charles Kenyon

Word New User FAQ & Web Directory: http://addbalance.com/word

Intermediate User's Guide to Microsoft Word (supplemented version of
Microsoft's Legal Users' Guide) http://addbalance.com/usersguide




--------- --------- --------- --------- --------- ---------
This message is posted to a newsgroup. Please post replies
and questions to the newsgroup so that others can learn
from my ignorance and your wisdom.

 

[Peter Jamieson]

OK, it looks as if this is the situation:

In Word XP/2003 when you record label setup, the only recorded statement is

ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels

if you start with a blank document and execute that statement, it is not
enough to set up a label layout (i.e. with a Word table and so on). If you
have no table layout, Wordbasic.MailMergePropagateLabel fails

FWIW as far as I know, Wordbasic.MailMergePropagateLabel was not available
in Word 2000, and it is interesting that there is no VBA equivalent, and
that the command sometimes fails on Tablet PCs, which puts it in the
"suspect" category as far as I'm concerned.

So...
a. if you are starting with a document without a label layout, that's
probably sufficient explanation and the "solution" is probably to start from
a document that has the layout you want. If you need something more generic
than that, you may need to create the label layout using the lnon-mailmerge
label-related facilities first, and that would need something more than just
VBA macro recording.
b. if not, something else is wrong - any more clues at your end?

Peter Jamieson

 

[Doug Robbins - Word MVP]

I still maintain that starting from scratch with a macro is not the way to
go. If it is just the data source that changes each time, then just change
it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[nelsonj]

Just so Doug has the "big picture" here....Prior to upgrading to Office 2003,
our Permitting Dept. employed two tool bar macro command buttons. The first
button would activate a data selection process against ArcView ( Geodata
info) and pull in property and owner information on certain land parcels in
our county and the macro results ended up in a Word document in a six column
table with LName, FName, Address, City, State, Zip columns.
The second macro command button ( the one causing the problem) takes the
WORD doc with the table and initiates the mailing label routine witihin the
merge process to create mailing lables for the rows in the table.
The first mcaro generated just fine in Word 2003. The second macro fails
on the line shown in a previous reply.
The reason why, Doug, I am trying to build the macro is so the users in
the department don't have to remember 9 or 10 mail merge steps. All they have
to do it click a command button....like they did in Word 2000. They don't
have to go looking for the input file, they don't have to remember to save
the resulting lables in a particular place...the macro is SUPPOSED to do it
for them.
Peter...thanks for your cogent replies. I'll try your suggestion.

 

[nelsonj]

Peter....your suggestion worked. I created the mailing lable format ahead of
time and called the saved format into the macro recording and it works as I
need it. Thanks.
J. Nelson