MailMerge Macro Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 16 Dec 2003 22:02 GMT

Greetings,

I am having a little trouble with a macro I recorded to
complete a mail merge. Normally, I work in Access and am a
little ignorant of Word. Any help here would be great.
Here is the code;

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/16/2003 by DBW1
'
ActiveDocument.MailMerge.MainDocumentType =
wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource
Name:="C:\Address-data.doc", _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="",
SQLStatement:="", SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
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"""
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""Address1"""
Selection.TypeBackspace
Selection.TypeBackspace
Selection.TypeBackspace
Selection.TypeParagraph
Selection.TypeBackspace
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""Address1"""
Selection.TypeParagraph
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""City"""
Selection.TypeText Text:=" "
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
End Sub

The problem seems to be generated from the last
line "WordBasic.MailMergePropagateLabel". I have checked
my references and they look normal. The error states the
command is unavailable.

Any ideas?

Thank you all in advance,
David

Reply to this Message

Doug Robbins - Word MVP - DELETE UPPERCASE CHARACTERS FROM EMAIL ADDRESS - 16 Dec 2003 23:48 GMT

Hi David,

I am puzzled as to why you would try and use a macro to create a label type
mailmerge main document, rather than just create the main document, save it,
and then use it in your macro.

There is nothing wrong with the

WordBasic.MailMergePropagateLabel

by itself. What I do not see anywhere in your code is where the type of
label is selected. I believe that is why you are getting the "command not
available" messsage. Having just tested it, the macro recorder does not
pick up the step where you select the type of labels that you are going to
use. However, If you use the recorder when using Tools>Letters and
Mailings>Labels>Select Label Type>New Document, it does generate code such
as the following:

Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="AOne 28171",
Address:="" _
, AutoText:="ToolsCreateLabels3", LaserTray:=wdPrinterTractorFeed, _
ExtractAddress:=False, PrintEPostageLabel:=False, Vertical:=False

If you insert the code that you generate in this way, at the beginning of
the code that you already have, then it will work. The following did:

Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="AOne 28171",
Address:="" _
, AutoText:="ToolsCreateLabels3", LaserTray:=wdPrinterTractorFeed, _
ExtractAddress:=False, PrintEPostageLabel:=False, Vertical:=False

ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\DKR\My Documents\My Data
Sources\desidata.rtf" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
_
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:="", SubType:=wdMergeSubTypeOther
ActiveDocument.Fields.Add Range:=Selection.Range,
Type:=wdFieldMergeField _
, Text:="""FirstName"""
WordBasic.MailMergePropagateLabel

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

> Greetings,
>
[quoted text clipped - 67 lines]
> Thank you all in advance,
> David

Reply to this Message

H L - 29 Jul 2005 22:17 GMT

I, too, need to automate mailmerge to labels (the reason for this is that the
data file with which the labels are associated varies substantially in size.
Sometimes it has 6 records, sometimes 2000. The label document is not
automatically creating the correct number of labels based on the number of
records but rather just creating the same number of labels as the number of
records in the original dataset. If there is a way to change this, I'd be
happy with that option.)

I implemented the createnewdocument method you indicated below, and this got
me further than I've gotten thus far, but now at the tail end it tells me
that the field codes are not valid for the data set: "This merge field is in
use in the main document but does not exist in the data source." Does this
have anything to do with the fact that there is no header in the data source
& that it uses comma delimiters? I don't see a way to set that in the VBA
code.

<CODE>
Application.MailingLabel.DefaultPrintBarCode = False
Application.MailingLabel.CreateNewDocument Name:="5161"

ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:="O:\ProLaw\label.csv", _
ConfirmConversions:=True, 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=O:\ProLaw;Mode=Read;Extended Properties=""HDR=NO;"";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database
Password="""";Jet OLEDB:Engine Type=96;Jet OLEDB:Database Loc" _
, SQLStatement:="SELECT * FROM `label#csv`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeOLEDBText
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField
_
, Text:="""F1"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField
_
, Text:="""F2"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField
_
, Text:="""F3"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField
_
, Text:="""F4"""
ActiveDocument.Fields.Add Range:=Selection.Range, Type:=wdFieldMergeField
_
, Text:="""F5"""
Selection.MoveLeft Unit:=wdCharacter, Count:=5, Extend:=wdExtend
Selection.Font.Name = "Arial"
Selection.Font.Size = 11
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.ParagraphFormat.TabStops.Add Position:=InchesToPoints(3.73), _
Alignment:=wdAlignTabRight, Leader:=wdTabLeaderSpaces
Selection.ParagraphFormat.TabStops(InchesToPoints(3.73)).Position = _
InchesToPoints(3.88)
Selection.TypeText Text:=vbTab
Selection.MoveRight Unit:=wdCharacter, Count:=4
Selection.TypeParagraph
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Font.Bold = wdToggle
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeParagraph
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
Selection.TypeParagraph
Selection.MoveUp Unit:=wdLine, Count:=2
Selection.TypeParagraph
Selection.MoveDown Unit:=wdLine, Count:=2
Selection.MoveLeft Unit:=wdCharacter, Count:=4
Selection.TypeText Text:=vbTab
Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Font.Size = 9
Selection.MoveRight Unit:=wdCharacter, Count:=1
WordBasic.MailMergePropagateLabel
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub
</CODE>

>I am puzzled as to why you would try and use a macro to create a label type
>mailmerge main document, rather than just create the main document, save it,
[quoted text clipped - 15 lines]
> Application.MailingLabel.CreateNewDocument Name:="AOne 28171",
>Address:="" _

Reply to this Message

Rate this thread: