Excel VBA Mail Merge, Output Multiple Records (Pages) in One Word File

  • I'm still very new, but I'm making daily progress on my learning project.

    I've been learning from videos how to do a mail merge with VBA, but I'm stuck on one aspect. The instructional videos I've found assume I want the merged form letters to be saved separately, like you would with letters to different people. But that's not what I need. I need all the separate pages to stay in one Word Document, like you would if you were making a detailed record of all of a company's equipment.... 50 (dynamic length) different pieces of equipment in an excel spreadsheet, 50 identical pages with specific data about each piece of equipment, 1 Word document.

    I have some issues with the Word commands near the bottom, but I speculate it is because I need to change the approach because of a newer version of word than the video lesson I was learning from. Looking ahead though, I have no idea how to generate multiple records/pages in one word file... and that's what I'm hoping to learn from coming here.

    Here's where I'm at so far for the mail merge module:

  • This whole project idea was a way to automate a report we provide to our customers on their equipment (Preventative Maintenance Report). Each piece of equipment has a separate sheet, which up until now has been manually entered on a word document from data collected in the field. I've been working on a way to remove most of the effort in producing the report by using a tablet in the field, with a user form in Excel and voice recognition software (Dragon). I've gotten quite far in making this work. Except for some small details, all I have left is creating the mail merge reports from the active sheet, and then write a program to mine the data from existing sheets to create the index worksheet.

    Each customer has somewhere between 3 and 150 PM Sheets in their annual PM check, and I alone collect data on over 1,500 pieces of equipment per year. This is why I've invested so much time in learning VBA to make this project work.

    For those who are interested, here are some screen captures that illustrate what I'm doing.

  • Hmmm... there's a paradigm shift.

    I'll have to think about that for a while. I don't know enough to "see" it in my head yet. I like the use of Excel spreadsheets because there are a lot of redundant entries that can be easily filled in while looking at the worksheet. And looking back on previous years' data was as easy as checking previous Sheets.

    I'll chew on that while I'm working today. Thanks!

    (But the original question is still pertinent if someone has an answer.)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!