# Word Mail Merge from Excel problem



## Keith24249 (Oct 22, 2009)

Hi All,

I'm a Mail merge newbie and am trying to figure out the following. I have built an Excel spreadsheet to enter data for custmer quotations see attached I have also built a Word document to merge this data into, again see attached. I can get mail merge to input the first line of data into the word document but sometimes a custoimer will ask for a price on more than one item. The problem is I don't know how to get mail merge to input the extra rows of data. Can anyone help with this, please. Thanking you in anticipation.

regards
Keith


----------



## raremind (Oct 22, 2009)

you're trying to do a "multiple items per condition of a key field" mail merge which, "out of the box", Word can not do. You would need to write a back end macro to complete what you want.


----------



## macropod (Apr 11, 2008)

Hi Keth, raremind,

Word can indeed do this "out of the box", without the need for a macro, using a Catalogue/Directory Mailmerge (the terminolgy depends on the Word version). To see how, check out my Word 97-2007 Catalogue/Directory Mailmerge Tutorial at:
http://www.wopr.com/index.php?showtopic=731107
or
http://www.gmayor.com/Zips/Catalogue Mailmerge.zip 
Do read the tutorial before trying to use the mailmerge document included with it.


----------



## Keith24249 (Oct 22, 2009)

Hi Guys,

Many thanks for your replies. I have downloaded the two tutorials that you have suggested but I'm afraid I don't have sufficient skill to get it to solve my problem. I have attached a sample Excel spreadsheet and the Word doc that I am trying to merge to. If anyone can point me in the right direction I would appreciate it.

Regards

Keith


----------



## macropod (Apr 11, 2008)

Hi Keith,

I've had a look at your files and have noted numerous issues:

Your Excel data file has the Enquiry Nos only on the first row for each group. You must replicate this on every row. You also have numerous empty rows, and a cell with double quotes (ie 2" for 2in), both of which will cause output errors.

Your Word document has everything in the 'Normal' Style, but the default attribute for that Style has been over-ridden all over the place. That's simply bad practice. Your logo is also missing and both that and your other standard info really should go into the page header. Your mailmerge fields also bore no relationship to your Excel data file. With what you posted, you couldn't even produce a standard letter merge.

Attached is a zip file containing a re-worked Excel worksheet, a mailmerge main document and a sample output file. In setting up the mailmerge main document, I noticed that your Excel file has a 'Carriage' field, but there was no provision for anything from it to be carried over into the mailmerge, so I included provision for that also. In the sample output file, you'll see a small gap between each row in the tabulated data. That's because there's actually an empty paragraph in there. If you don't like that, the table joiner macro from the tutorial can be used to remove those gaps.


----------



## Keith24249 (Oct 22, 2009)

Hi Macropod,

Many thanks for taking the time to produce the zip file this is exactly what I was trying to achieve. Thanks also for your advice on my attachments I would never have achieved anything as it was.

I note you have removed the formulas from the Excel spreadsheet. Does that mean we cannot use any formulas in our data source?

I cannot see how you have got the data from the spreadsheet onto the Word document. Are you allowed to reveal that information?

Many thanks once again and I look forward to hearing from you so that I can expand my data source.

Regards
Keith


----------



## macropod (Apr 11, 2008)

Hi Keith,

The is no problem with formulae in an Excel workbook used for a mailmerge. The only formulae in the workbook you posted were in the two black cells, and I left those intact. One issue with your workbook that I forgot to mention in my previous post was that the column header rows weren't on the first row of the worksheet - they should be (I fixed that in the one I returned with my post), though you can get around this if you use a named range spanning the whole of the mailmerge data range.

The process for getting the data from the Excel worksheet onto the letter is the Directory/Catalog mailmerge, combined with the mailmerge field coding I've used in the mailmerge main document (ie Quote Template.doc). If you open that document and press Alt-F9, you'll see the field coding - it's based on what was in the tutorial, under the heading 'Calculating Table Totals'.


----------



## Keith24249 (Oct 22, 2009)

Hi Macropod,

The sample out put is exactly how I hoped it would appear. However I have tried to merge the data that is in my Excel spreadsheet into the Quote Template and it doesn't seem to work correctly.
I have done as you suggested and Pressed Alt F9 to reveal the field coding but it appears to be out of place. Could you give it another go and see what I'm not doing or perhaps I am doing something wrong. Many thanks for help and patience so far.

Regards
Keith


----------



## macropod (Apr 11, 2008)

Hi Keith,

Other than a change in the shading of the 'Total' row in the table, I can't see that there's any real difference between the files I returned to you and your latest sample output. I do note, though, that your 'Quote Template' has two unwanted paragraph marks after the field code, though. Although the sequence of the table parts etc in the field code is not what you might have expected, it is correct for this type of mailmerge.

Perhaps you could explain in more detail the problems you're having.


----------



## Keith24249 (Oct 22, 2009)

Hi Macropod,

Thankyou for your continued support on this. The problem is I cannot merge the data from the Excel sheet onto the Quote Template and get it to look like the Sample Output you previously sent. When I edit the recipients and select the same Quote Number then click on finish mail merge it splits the requested entries up instead of putting them on one page. I am unsure how I delete the unwanted paragraph marks without doing permanent damage. Once again I welcome your comments on this.

Regards
Keith


----------



## macropod (Apr 11, 2008)

Hi Keith,

Did you set your document up for a Catalog or Directory merge, as described in the tutorial? The output you're describing is a form letter merge.

With your 'Quote Template', you must also delete the last two paragraph marks after the field code. Otherwise there'll be extra gaps between the merge records. If you press Alt-F9 to expose the field codes, you'll see where they end, as indicated by the final '}'. As you'll see, those last two paragraphs are safely outside the field code.


----------

