error in word VBA to automate creation of mailing labels

dgr7

Board Regular
Joined
Apr 5, 2006
Messages
225
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

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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Forum statistics

Threads
1,225,267
Messages
6,183,950
Members
453,198
Latest member
VB6 Programming

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top