p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Word VBA (http://p2p.wrox.com/forumdisplay.php?f=231)
-   -   error in word VBA to automate creation of mailing (http://p2p.wrox.com/showthread.php?t=53702)

dgr7 February 6th, 2007 03:55 PM

error in word VBA to automate creation of mailing
 
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


dgr7 April 4th, 2007 08:34 AM

Can anyone help me with this error?



All times are GMT -4. The time now is 06:36 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.