error in word VBA to automate creation of mailing labels fro

[dgr7]

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

 Dim Directory, Filename As String
    
    Documents.Add DocumentType:=wdNewBlankDocument
    
    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.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.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
    'change the font size to 9
    'can I take the left 5 characters from the .xls to use in place of Jan07? left(filename,5)
    'ChangeFileOpenDirectory "C:\My Documents\MonthEndMailingLabels\"
    'ActiveDocument.SaveAs Filename:="TRUSTJan07Labels.doc", FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
End Sub

 

[dgr7]

can anyone help me with this?

thanks,
david