Microsoft Office Forums Multiple File Mailmerge

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2019, 01:04 AM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default Multiple File Mailmerge

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
Reply With Quote
  #2  
Old 04-04-2019, 03:27 PM
macropod's Avatar
macropod macropod is offline Multiple File Mailmerge Windows 7 64bit Multiple File Mailmerge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,483
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 04-05-2019, 06:42 AM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default

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
Attached Images
File Type: jpg Column Data.jpg (155.3 KB, 10 views)
File Type: jpg speadsheet.jpg (267.4 KB, 10 views)
File Type: jpg finished column.jpg (131.0 KB, 10 views)
Reply With Quote
  #4  
Old 04-05-2019, 02:33 PM
macropod's Avatar
macropod macropod is offline Multiple File Mailmerge Windows 7 64bit Multiple File Mailmerge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,483
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 04-05-2019, 11:41 PM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 04-06-2019, 12:40 AM
macropod's Avatar
macropod macropod is offline Multiple File Mailmerge Windows 7 64bit Multiple File Mailmerge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,483
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 04-06-2019, 12:48 AM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default

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
Reply With Quote
  #8  
Old 04-07-2019, 02:03 AM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 04-07-2019, 02:15 AM
macropod's Avatar
macropod macropod is offline Multiple File Mailmerge Windows 7 64bit Multiple File Mailmerge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,483
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You can attach files to a post here, via the paperclip symbol after clicking on 'Go Advanced' below the 'Quick Reply' panel.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #10  
Old 04-07-2019, 09:00 AM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default

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
Attached Files
File Type: xlsx Aztec File 1.xlsx (10.4 KB, 1 views)
File Type: xlsx Aztec 2.xlsx (9.9 KB, 1 views)
File Type: xlsx Aztec 3.xlsx (18.0 KB, 1 views)
File Type: doc Aztec Letter Temp.doc (52.0 KB, 1 views)
File Type: xlsx Aztec Combined File.xlsx (35.2 KB, 2 views)
Reply With Quote
  #11  
Old 04-07-2019, 09:02 AM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default

as promised see attached finished pdf of how file will look
Attached Files
File Type: pdf Aztec Example 1.pdf (358.9 KB, 1 views)
Reply With Quote
  #12  
Old 04-08-2019, 09:23 AM
kevw1983 kevw1983 is offline Multiple File Mailmerge Windows 10 Multiple File Mailmerge Office 2019
Novice
Multiple File Mailmerge
 
Join Date: Apr 2019
Posts: 8
kevw1983 is on a distinguished road
Default

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
Reply With Quote
  #13  
Old 04-10-2019, 01:27 AM
macropod's Avatar
macropod macropod is offline Multiple File Mailmerge Windows 7 64bit Multiple File Mailmerge Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,483
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
MailMerge from txt file - failing when the file path contains a reserved SQL word travb81 Word VBA 7 10-26-2018 04:00 AM
Multiple File Mailmerge Mailmerge invoicing multiple items E__YU Mail Merge 3 01-24-2017 05:55 AM
Multiple File Mailmerge Trouble doing a Mailmerge in Word 2010 with multiple records for the same client Merlot Mail Merge 2 12-07-2015 10:13 AM
Mailmerge certain text using multiple if statements Debs Mail Merge 4 06-19-2014 03:34 AM
Multiple File Mailmerge Mailmerge to a table from a txt file troy23 Mail Merge 7 01-29-2014 04:40 AM


All times are GMT -7. The time now is 06:37 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft