Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Auto Mail Merge (2003)

    Hi,

    Ive recorded a macro to mail merge data from excel. When the macro is
    run it only gets as far as the end of step 4, and displays the
    following on the labels -

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail Merge (2003)

    Welcome to Woody's Lounge!

    Step 5 isn't really necessary when performing a mail merge in a macro. The instructions for step 6 are:

    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .Execute
    End With

    This will merge to a new document. If you want to print directly, use wdSendToPrinter instead of wdSendToNewDocument.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto Mail Merge (2003)

    Hi Hans,

    Thanks for that. I tried it out and it produced two label documents, one was blank, the other displaying just '

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail Merge (2003)

    You need an additional instruction to propagate the label setup. Try this version:

    Sub Merge()
    With Application.MailingLabel
    .DefaultPrintBarCode = False
    .CreateNewDocument _
    Name:="AOne 28171", _
    Address:="", _
    AutoText:="ToolsCreateLabels3", _
    LaserTray:=wdPrinterTractorFeed, _
    ExtractAddress:=False, _
    PrintEPostageLabel:=False, _
    Vertical:=False
    End With
    With ActiveDocument.MailMerge
    .MainDocumentType = wdMailingLabels
    .OpenDataSource _
    Name:="C:test.csv", _
    LinkToSource:=True, _
    AddToRecentFiles:=False, _
    Revert:=False, _
    SubType:=wdMergeSubTypeOther
    ActiveDocument.Fields.Add _
    Range:=Selection.Range, _
    Type:=wdFieldAddressBlock, _
    Text:="f ""<<_TITLE0_ >><<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>" & _
    Chr(13) & "<<_COMPANY_," & Chr(13) & ">><<_STREET1_" & Chr(13) & _
    ">><<_STREET2_" & Chr(13) & ">><<_CITY_" & Chr(13) & ">><<_STATE_" & _
    Chr(13) & ">><<_POSTAL_>><<" & Chr(13) & _
    "_COUNTRY_>>"" l 2057 c 2 e ""U"
    WordBasic.MailMergePropagateLabel
    .Destination = wdSendToNewDocument
    .Execute
    End With
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto Mail Merge (2003)

    Thanks.

    Ive pasted that version in and its showing a syntax error in the following part of your script somewhere;

    ActiveDocument.Fields.Add _
    Range:=Selection.Range, _
    Type:=wdFieldAddressBlock, _
    Text:="f ""<<_TITLE0_ >><<_FIRST0_>><< _LAST0_>><< _SUFFIX0_>>" & _
    Chr(13) & "<<_COMPANY_," & Chr(13) & ">><<_STREET1_" & Chr(13) & _
    ">><<_STREET2_" & Chr(13) & ">><<_CITY_" & Chr(13) & ">><<_STATE_" & _
    Chr(13) & ">><<_POSTAL_>><<" & Chr(13) & _
    "_COUNTRY_>>"" l 2057 c 2 e ""U"

    Im not sure whats wrong with the syntax though.

    Thanks for your help with this. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Cheers.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail Merge (2003)

    I'm not sure what's going on here - that is just the code you posted split into multiple lines. It doesn't cause an error on my PC. I have attached a Word document with the complete code. Download it to your PC and open it (I wouldn't open it directly from this post). See if you get the same error.
    Attached Files Attached Files

  7. #7
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto Mail Merge (2003)

    Thanks.

    The script is error message free now, but, unfortunately the intial probelm still exists (

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail Merge (2003)

    The code should produce two documents - and it does so for me when I run it (I created a small .csv file to test on).
    1) A document with merge fields; this is the one you mention.
    2) A document filled with data from the .csv file.
    Check the Window menu to see if the second document is hidden behind the first one.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto Mail Merge (2003)

    Yes, there are two documents, one behind the other.

    One document has blank labels and the other has '

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail Merge (2003)

    One possible explanation is that VBA becomes confused about which document it is processing. The attached version attempts to control this better.
    Another explanation would be that the .csv file doesn't contain valid data, but then the mail merge would also fail if you tried it manually, so that doesn't seem likely.
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto Mail Merge (2003)

    Im afraid the amended script you sent hasnt made a difference. Both documents are as detailed in my previous posting.

    Like you, I did start to suspect the csv file, but this does perform as it should when done manually, and even when running the macro the actual merge from the csv does work as I get the 'Header Record Delimiters' box come up with the relevant data in.

    Its certainly stumping me !

    Cheers.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail Merge (2003)

    Could you attach a zipped copy of the .csv file? Leave only two or three records in the copy, and change real names to fake ones.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto Mail Merge (2003)

    Hi,

    Here's a copy as requested with the data changed to some more well known characters.

    As its a small size Ive changed it to a text file rather than zip, please change back to csv, before opening.

    Thanks for your help.

    Lee
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Mail Merge (2003)

    Your csv file doesn't have a header row, i.e. a row with field names. Because of this, the macro doesn't recognize any of the data. I have attached your file (again renamed to .txt) with an appropriate header row. The merge should work with this version.
    Attached Files Attached Files

  15. #15
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Auto Mail Merge (2003)

    Yahoo !!!!

    It works.....Thanks so much for your help its really appreciated !

    Cheers.

    Lee <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •