How to do the Excel equivalent of a mail merge?

[Mark Patey]

Hello, all!

I'm trying to figure out the best way to accomplish something in Excel that's similar to a mail merge in Word. I have a spreadsheet that is essentially a form letter in Excel format... contains empty fields which I want to fill with the the records from another spreadsheet (which is basically a mailing list with some additional personal info fields). For example, first name, county of residence, etc. Let's say I have 250 records in the second spreadsheet. The goal is to make 250 versions of the first spreadsheet (the form letter), each filled out with the info of a given record (or row) from the second spreadsheet.

This sounds a lot like a mail merge in Word... but the government entity that provided the form requires that it be filled out as is (in Excel form), or else I'd just recreate it in Word and do a mail merge.

Any idea how to do this in Excel? Thank you in advance for any light you can shed!

-Mark

[#2 · Accepted solution]

Hi Mark,

That's with VBA. The idea is here https://www.tek-tips.com/faqs.cfm?fid=4223, first I found (believe you may find more samples). Instead of printing in your case you shall save the form as new file on each loop step.

[#3]

Wow, great find, Sergei! I will give that a go. Thank you so much for your help.

-Mark

[#4]

Hi Sergei,
 
Can you please help me with below question?
I do have same question and very time whenever new data is updated in workbook 1(My data) I want to see same data in workbook 2(My form).
I have a quick question can you please explain me in detail, but i am unable to follow point C and point D. 
 
I have attached my data and form (workbook 1 & 2) for reference.
 
Thank you in Advance for your help!

[#5]

The Excel Mail Merge works but I am trying to figure out how to "save the form as new file on each loop step" instead of MergePrint() or wsForm.PrintOut?