Background:



I have a simple excel report each day containing 10 - 30 rows of data, and each of those rows contain a email address which that row of data needs to be emailed to. I setup a simple word document to read this report, and have a VBA macro that takes the word document and feeds it into outlook so each recipient receives an email with a custom subject and the data they require.

Problem:

As I have some not so tech savvy colleagues, things are setup so they can open the template excel file, paste in their data, open the word document, hit a button and the macro does everything else for them.

I've found though that if the report one day only contains say 5 rows of data, and after they are done they hit save on the word document rather than closing without saving, the next time they open the word document containing the mail merge, it is expecting only 5 records, so even if the excel file has had 50 lines of data pasted into it, only the first 5 will be read.

I cannot seem to get word to read the additional rows, even if I unlink the data source and relink it again. The only workaround is to save the excel file under a different filename and have my word document read it as a new data source.

Attempted short version of problem:

If a word mailmerge document is expecting an excel file with 5 rows, but it has been recently amended and now contains 20 rows, word will only read the first 5 rows.

I feel like there should be an easy fix for this but cannot for the life of me figure it out, so any help is appreciated!