Forum Discussion
Using mail merge in excel without word
I'm writing a response for other users with the same goal as I've done this for clients many times--mail merge in Excel without Word can be so much more streamlined. You do need basic VBA skills though.
1) Create a mapping between the source data and the destination (template) cells. If each record has its own row in the source, for example, you could map column 1 (source name) to cell D1 (template name), column 2 (address) to cell E2 (template address), etc.
2) Select the rows in the source to merge. In VBA, iterate through each selected row.
3) In each iteration, copy every source cell to the mapped destination cell, then save the template as it's own XLS file and publish it to PDF.
Now you have an Excel file and a PDF file for each source record.
If you don't know VBA, you can hire a contractor to put this functionality into your spreadsheet without too much cost. There's a visual of the process attached and at http://wvcrest.com/xl/
Good luck!
I am working to keep all the files in excel and would like to basically merge excel info into an excel template. I know you can merge into an excel document and I see the one that turn the info directly into a pdf, but I do not see one that puts it back into an excel document with a merge. Is that way not possible? It seems to me that I will have to have a template pre-made for each and just connect the worksheets? Thank you for any help you can give me.
crossmountain2005 I use an XLS table of data to generate many XLS files based on a template, and each of these is optionally also converted to PDF. Does that answer your question? This is done through VBA programming however.