I have a list of invoices exported to Excel which I want to mail merge with word to produce a monthly statement for approx 500 debtor accounts.
The process as I would like it to happen would be as follows -
1 - The export is saved as 'Core Data' and opened in Excel
2 - The data (export) is sorted into debotor account order
3 - the data for each debtor is copied to another workbook into a worksheet called 'Data'
4 - The copied data will pull the debtor name and address using a lookup on an account number
5 - The transaction data, eg inv dates, amounts, etc are given named ranges, eg A6:A25 is called Range1, B6:B25 is called Range2, etc
6 - A template statement document is opened in word
7 - The named ranges in Excel, ie Range1, Range2, etc are merged into the word template
8 - The 'Data' worksheet holding the named ranges will be cleared and all named ranges will be deleted ready for next set of data to be copied into this worksheet
I have managed to write the code for steps 1-6 & 8, but not sure how to do the mail merge. There will be approx. 500 accounts for which 1 statement per account will need producing and saving as unique filename, eg ACC001 May 06.
Thanks
The process as I would like it to happen would be as follows -
1 - The export is saved as 'Core Data' and opened in Excel
2 - The data (export) is sorted into debotor account order
3 - the data for each debtor is copied to another workbook into a worksheet called 'Data'
4 - The copied data will pull the debtor name and address using a lookup on an account number
5 - The transaction data, eg inv dates, amounts, etc are given named ranges, eg A6:A25 is called Range1, B6:B25 is called Range2, etc
6 - A template statement document is opened in word
7 - The named ranges in Excel, ie Range1, Range2, etc are merged into the word template
8 - The 'Data' worksheet holding the named ranges will be cleared and all named ranges will be deleted ready for next set of data to be copied into this worksheet
I have managed to write the code for steps 1-6 & 8, but not sure how to do the mail merge. There will be approx. 500 accounts for which 1 statement per account will need producing and saving as unique filename, eg ACC001 May 06.
Thanks
