Need VBA macros to mail merge data from Excel into label RRS feed

  • Question

  • I need to write VBA macro in Word to generate word file with labels merged with data from excel. I like to have the label measurements and the data fields defined within the code itself.  Does anyone have a code that does it? Thanks.
    • Edited by Aboalia Sunday, October 1, 2017 12:15 AM
    Sunday, October 1, 2017 12:12 AM

All replies

  • The question that needs to be asked is why?

    Why build the labels from scratch when Word includes built-in templates for just about every label on the market. Surely it would make more sense to call the required label.

    Then as the macro would presumably be dedicated to the particular worksheet (otherwise how do you propose to determine which fields to include) why use VBA to create the label document. It would be far simpler to create the label document in advance with the fields you require and then use your macro to call the document and merge your data into it.

    Graham Mayor - Word MVP

    Sunday, October 1, 2017 3:32 AM
  • If you are talking about envelop labels for mass mailing I agree with Graham.  Use the templates.  You can adjust the templates if it isn't suitable.  I had to adjust one template because it didn't align with the printer.  If you are talking about labels for folders or one of envelop labels you should look at a label printer.  I did have to create a custom template for this.  I used bookmarks to position the data and VBA to fill in the content.  I've never had much luck with using sheet labels for one of labels.

    Sunday, October 1, 2017 1:47 PM
  • Thanks:  As I said answering Graham's question, once I have the macro build, I would feel more comfortable using the macro, and modifying it as need to fit various needs, than having to follow the Word routine.  I developed an extensive array of macros that fit most of my work need, however, I did not do any for label merge. that is why I am asking for help.  Thank you.  
    Sunday, October 1, 2017 7:44 PM
  • Thank you for asking the question: Well, often times I have to use labels from local market that is not listed in Word.  I have to measure the dimensions with the ruler and try to figure out which label in Word that matches it.  Recently, I had to scroll through the list of labels on Word until I figured out Avery A4/A5 (#3653) to fit. It is a 2 labels across by 7 down.  When I printed it, found the last row to be shifted to the next page.  When I reduced the label high from 1.67 to 1.669 it fixed the problem.  That is why, I would feel more comfortable using the macro that I can modify to fit my various needs than I would using the Word templates.  It should not be necessary to dedicate the macro to a specific worksheet, instead, the macro can be build to include an InputBox (Message, Title, Default) codes that for the user to enter the name of the data source worksheet.  Beside the section in the macro for adding the data fields can  readily be modified to fit various needs.  From my experience I found: Once you have the macro that fits your specific need build, it becomes more easier to use the macro than to follow the build-in Word routine.  That is why.  Hope this answers your question.  Thanks.
    Sunday, October 1, 2017 9:15 PM