Multiple File Mailmerge

[kevw1983]

Hi, first time poster hopefully someone can help and advise on this

I have a mailmerge to do for a client which is the equivalent to rent statements

the data has been supplied over 3 excel files and one word file with the placeholders there, it is the first time i have done this job and seems very complex but must be possible as someone (another company) has done previously

files - word template a 2pp document

excel file 1 - customer information data i.e. name, address, contact details, account number
excel file 2 - there rent type and basic cost information, data is sorted by keyfield however there are more records than file 1 but key field still exsists


excel file 3 - rent cost for the year, key field again supplied but rent information for one person goes over multiple lines and needs to display in table

help would be massively appreciated on this

[macropod]

See: https://www.msofficeforums.com/mail-...-multiple.html. Although the example there uses a single Excel file and the data are on two sheets, the same principles apply for using multiple Excel files.

[kevw1983]

thanks Paul, after looking at this i just cant get it work. i have transfered all into one spreadsheet using a VLOOKup but my main problem is now i have a table of data which needs to be repeated based on a key field number.

i have attached a screenshot of the word file with the merge fields where they should be placed and a PDF of how the finished item should look. I have also shown the Excel records so to the right of this it has everyone details 13 times in the record

after searching google/youtube i think an SQL code may solve this but is totally beyond me

thanks for your help

[macropod]

If you want to create a merge with grouped data, check out the 'Sticky' thread at the top of this forum for my Microsoft Word Catalogue/Directory Mailmerge Tutorial:
https://www.msofficeforums.com/mail-...-tutorial.html
The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

Depending on what you're trying to achieve, the field coding for this can be complex. However, since the tutorial document includes working field codes for all of its examples, most of the hard work has already been done for you - you should be able to do little more than copy/paste the relevant field codes into your own mailmerge main document, substitute/insert your own field names and adjust the formatting to get the results you desire. For some worked examples, see the attachments to the posts at:
https://www.msofficeforums.com/mail-...html#post23345
https://www.msofficeforums.com/mail-...html#post30327

Alternatively, you may want to try one of the Many-to-One Mail Merge add-ins, from:
Graham Mayor at http://www.gmayor.com/ManyToOne.htm; or
Doug Robbins at https://onedrive.live.com/?cid=5AEDC...43615E886B!566

[kevw1983]

Hi Paul, thanks again for your guidance on this. I am on a bit of a tight schedule on this ATM. I know this is really cheeky if i sent you some sample data would you be able to drop the SQL code in where required?

[macropod]

All you need to do is copy the appropriate set of field codes from the tutorial, then edit it (e.g. by substituting your own mergefield names & text).

[kevw1983]

thanks Paul, will give it a try later on. Really appreciate your expertise.

based on info provided do you think i am heading down the correct path with how i have set the data

[kevw1983]

Hi Paul

Unless i am missing something in the tutorial and my head i now fried with this, i have been on since thursday AM GMT. I cant see how you designate the Keyfield, it has a section on the keyfield but doesnt show you how to input the code into that field.

I am now desperate for this to be sorted, is there any way if i sent you a word file and small amount of data you could set the document with appropriate fields please as i have totally hit a brick wall, this is something that is way beyond my level of experience and i have no idea what i am doing to be honest. this job has came into my company and i need to get it produced urgently.

any help would be greatly appreciated and i wouldnt mind compensating you for your time via paypal

[macropod]

You can attach files to a post here, via the paperclip symbol after clicking on 'Go Advanced' below the 'Quick Reply' panel.

[kevw1983]

Hi Paul

thank you very much for looking at this for me, i have attached five files

word file - shows the place holders and field names from each of the original spreadsheet files

i have however used a vlookup formula to bring everything into one spreadsheet (combined file) in the hope that this would bring in the rent lines which need to go in the table on P2 and this is where i am stuck.

I will attach a PDF of how the finished letter should look

[kevw1983]

as promised see attached finished pdf of how file will look

[kevw1983]

Hi Paul

real sorry to be a pest, just wondering how you are getting on with this if you have had a chance to look at it?

Kev

[macropod]

Aside from the fact your document lacks even a single mergefield, its layout really isn't conducive to a mailmerge - especially insofar as you have multi-row table headings with merged cells. In any event, the approach taken in my tutorial is only suitable for generating a single set of grouped/repeated data, whereas your document contains two such tables. If you were to re-arrange the tables for the grouped/repeated data so that no merged cells were used, the approach linked to in post #2 could be employed. Otherwise, you'll need to use a macro to do all the processing.