+ Reply to Thread
Results 1 to 3 of 3

Attempting to automate mail labels

  1. #1
    Registered User
    Join Date
    05-14-2015
    Location
    Dallas, Texas
    MS-Off Ver
    2010
    Posts
    2

    Attempting to automate mail labels

    Running Win7 and Word 2010

    So I am familiar with VBA in Excel and I know it is the same for Word, but I have no idea how to go about trying to accomplish this project.

    Essentially right now a label template is used in Word (3 columns x 10 rows) and the top line of the address has to be typed in manually. Now it may have to be 4 labels that are the same, so she will type the address and the copy/paste it into the next three labels and then go to the next. It will be a variable number each time depending on how many invoices are going to each place.

    Format of each label, only the top line will change.

    (Variable top line)
    c/o Company
    1000 Example Address #100
    City, State Zip

    I want to find a way to be able to type in the variable top line and the quantity then autofill that number of labels. Then be able to type in the next top line and autofill quantity.

    Thanks in advance for any help!

  2. #2
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,667

    Re: Attempting to automate mail labels

    There are two possible approaches to this problem that immediately spring to mind, use a:
    1. mailmerge; or
    2. macro

    With a mailmerge, you'd use an Excel workbook as the data source. To populate the Excel workbook, you might type the variable data into a cell, then copy down as far as needed, or you might pre-populate the workbook with a formula like =A2 in cells A3 and copied to all cells below (row 1 is the mailmerge header row), then simply type the variable data into cell A2, go down however many rows you need, type another variable data into the next cell, and so on.

    With a macro, you'd need it to do something like: solicit the variable, then the number of labels, populate those labels, then go to the next label and repeat the process until the user exits. This gets a bit complicated if you use label stationery that calls for a table in which there are gaps between the cells that get populated and/or you need more than a single page of labels, or if you want to start anywhere other than the first label. The user also needs to learn how to run macros (not at all difficult).
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    05-14-2015
    Location
    Dallas, Texas
    MS-Off Ver
    2010
    Posts
    2

    Re: Attempting to automate mail labels

    Thanks for the response Macropod.

    I worked out a way to do it, but I will post my steps just in case anybody else comes across this.
    • In excel I created a Userform that has all the spaces Customer, C/O, Address, City, State, Zip.
    • Made it to where the userform inputs the data into rows when you hit the submit button (that way if they need multiple of the same they can just hit the button X times)
    • Set up a button to create the mail merge.
    • The button opens up the word file that has the addresses set up they way I want them and creates the labels in a new word document.

    Should be easy for them to work since its just buttons and userform, so they really don't have to know how to do a macro.
    Last edited by wwilder; 06-08-2015 at 09:33 AM. Reason: correction

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Attempting to automate single rows of data into a template
    By hurw0 in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 11-30-2014, 07:41 AM
  2. Replies: 2
    Last Post: 07-12-2012, 08:11 PM
  3. attempting to automate an import with variable data
    By Excelcanberra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2008, 12:15 AM
  4. automate e-mail attachments in Calypso
    By mrdata in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-30-2007, 06:05 PM
  5. Automate Send To/Mail Recipient
    By in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-17-2005, 11:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1