hello,
I'm trying to automate the creation of mailing labels where the source data in an .xls worksheet.
I have the below VBA code that I created first with the macro recorder in word then edited some to put in the Directory and Filename code that I've used successfully in some Excel VBA and VB 6.0 code.
Now I'm having trouble getting the code to work. I run it and I get the error:
Run-time error '509':
This command is not available
and the code execution stops on the line:
WordBasic.MailMergePropagateLabel
Can anyone help me get past this error so the code will run successfully.
thanks in advance,
david
I'm trying to automate the creation of mailing labels where the source data in an .xls worksheet.
I have the below VBA code that I created first with the macro recorder in word then edited some to put in the Directory and Filename code that I've used successfully in some Excel VBA and VB 6.0 code.
Now I'm having trouble getting the code to work. I run it and I get the error:
Run-time error '509':
This command is not available
and the code execution stops on the line:
WordBasic.MailMergePropagateLabel
Can anyone help me get past this error so the code will run successfully.
thanks in advance,
david
Code:
Dim Directory, Filename As String
Directory = "C:\My Documents\MonthEndMailingLabels\"
Filename = Dir(Directory & "*.xls")
'MsgBox Directory & Filename
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
Directory & Filename, _
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=Directory & Filename;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password" _
, SQLStatement:="SELECT * FROM `Untitled$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.Fields.Add Range:=Selection.Range, Type:= _
wdFieldAddressBlock, Text:= _
"\f ""<<_COMPANY_" & Chr(13) & ">><<_STREET1_" & Chr(13) & ">><<_STREET2_" & Chr(13) & ">><<_CITY_>><<, _STATE_>><< _POSTAL_>>"" \l 1033 \c 0 \e """""
WordBasic.MailMergePropagateLabel
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Selection.WholeStory