Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hello, again, colleagues all,
    A bit of blatant brain-picking, if I may. I set up a template which consisted of a table with one cell for each label and the address fields set out in each cell. This is the way I used to do it in Word 5 (!) when I started my business many years ago, but obviously I am not remembering it correctly, because it repeats the same name and address in every cell in one page. I have an alternate methodology whereby I read the records in turn, concatenate the address fields with newlines between them and then write the resulting string into the cell, without using any mail-merge technology. This is OK, but for a big run, is fairly clunky. As an aside, I should perhaps explain that I am trying to run all letter-writing and mailing-label printing from within Access, because a lot of temporary staff (its a call-centre) may not be that familiar with the refinements of MS Office nor have the necessary permissions to work in external directories, hence the emphasis on burying the process in VBA.

    I wondered whether there was any functionality in VBA that would simply call up the label printing facility in the Word Mail-merge machine, nominating the label type and so forth ? This might be a bit more elegant and use less system resources than my current idea.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can set up a mail merge document for labels. In recent versions, this is done in the Mail Merge task pane.
    You enter the merge fields and other text in the first label, then click "Propagate Labels". This copies the contents of the first label to the other ones on the page and inserts a "Next record" field - the latter is what makes the mail merge show a different record in each label when executed.
    Save the mail merge document.
    You can then open it from Access using Automation, change the data source if necessary and execute the mail merge.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Thanks for that, Hans.
    I have created the template, putting the various address fields in the top LH cell. Then I propagated the addresses to the other cells as you described. However, nothing happened when I ran the VBA functions to create the output file. These work fine with a standard letter. I then tried to run the mail-merge manually by opening the template file and nominating the data source. I was able to open a window in Word to inspect the data and choose individual records. The stepwise wizard then asked me to replicate the labels which replaced the Next Record field with all of the address fields. The next step - Preview labels - merely showed me all of the fields in each label. The final step - print labels - simply printed out the names of the fields again. I attach a zip file with some relevant information.
    I am wondering whether there is some VBA code to run specifically identifying the file as a mail label file.

    Jim
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The top left hand cell does not contain merge fields but plain text when I view it...

  5. #5
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    That's funny. I cut and pasted the address fields from a letter which works fine. Also, if the data was not in the form of fields, how come it propagated the Next Record field and later replicated the address. It could be that the final output is simply a textual version of the original fields, though I have never seen this phenomenon. Anyway, I will investigate further. One question though. Should the template I submit for VBA processing be the one after propagating (i.e. <<Next Record>> version, or should it be the one after replication with the address in all fields ? Actually, this is where I started from, but I got the same name and address printed in every cell! That is what prompted me to wonder whether VBA needs any special command to alert the mail merge engine to the fact that the template is a set of addresses and not a repetitive single document.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    After clicking Propagate Labels, save the merge document. This is the one you should use in your VBA code. The mail merge process, whether invoked interactively or from code, should place a different address in each cell.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Many thanks, Hans. I will give it a whirl.

    Jim.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    152
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hans, I did as you suggested and after propagation, each cell had the address and in addition, all cells had the additional field <<Next Record>> preceding the first line of the address. When I tried to run the VBA, it stripped out the <<Next Record>> field, but did not populate the remaining fields. When I did it manually, I could see all the data in the data source, but the outcome was the same as in the automatic mode. I then went back and looked at the document from which I had copied the fields and noticed that when you click on a field, it highlights it with a shaded area. This was not the case with the labels template (i.e. they were actually plain text). I then went back to square one, copied the fields again and this time I noticed that they were highlighted in the mailing label template. After that, the system worked perfectly.

    It would be easy to say "doh!" but there is a moral. I have no idea why the fields turned into plain text, but one should always check to make sure that the properties have been maintained when copying. One has to wonder, though, if the so-called fields were actually plain text, why did Word treat them as though they were real fields. On the other hand, the process could be used just for repetitive text labels, but then again, why would they bother when a simple cut-and-paste would have had exactly the same effect.

    Thanks again for your assistance.

    Jim.

Posting Permissions

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