Mail Merge

Topic

New Reply

In Word2000, I have a document that I use with Mail Merge. My data source is an Excel spreadsheet that has data like this:

Vendor Invoice# Amount
ABC 210 10.10
ABC 211 15.10
XYZ 212 20.00

I want the mail merge to put on one page all of ABC’s invoices like this:

Company ABC

Invoice Amount
210 10.10
211 15.10
Total 25.10

I can get the company (ABC) and one line (Invoice 210 )of data but I can’t seem to get the other lines of data and a total. Any ideas would be appreciated.

Reply | Quote

Replies

My first suggestion would be to use Access for your database since you are working with a one-to-many relationship: You have one Company with many Invoices

Word doesn’t handle one-to-many mail merges very well.

However there are a couple relatively easy solutions I know of that work. One is to use a Database field and modify the resulting SQL statement. The one drawback to this is that the results are in a Word table format. However based on your description this shouldn

Reply | Quote

Thanks for all the info. I tried what was posted and I am almost there. I am getting all the right Invoices and Amounts for each vendor. The problem is that I am getting a separate page each time a vendor is listed. For example, if there are 3 records for vendor ABC, I end up with 3 pages of correct information. I could throw out the extra 2 but is there a way to have only 1 page per vendor.

Thanks again.

Reply | Quote

Hi snax500,

It sounds like you do not have two data sources for your merge. For this to work correctly you need to have a unique list of Customers and use it as the data source for the Main document and leave the Database field as it is.

In Excel, to generate a unique list of customers from the list you already have use the following macro:

Sub UniqueFilter()
Range(“A1:A100”).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range(“K1”), Unique:=True
End Sub

This will generate a unique list and copy it to a new location. You need to modify the cell addresses in the macro to suit your data.

Then either place the list in another workbook or add Named Range for it so you can change the data source for your Main document in Word.
~~~~~~~~~~~~~
Cheers!

Reply | Quote

Once I have the database inserted, how do I edit the font. I tried right-clicking on the code and changing the font but it keeps changing back to its default after I do the mail merge.

Thanks

Reply | Quote

Hi snax500,

To maintain formats during a merge, add the * MERGEFORMAT switch at the end of the Database field. For example:

{ DATABASE d “C:MergeDatasource.xls” c “Entire Spreadsheet” s “SELECT * FROM C:MergeDatasource.xls WHERE Company = ‘{ MERGEFIELD Company}'”h* MERGEFORMAT}
~~~~~~~~~~~~
Cheers!

Reply | Quote

Thanks so much for your help. I am still having problems with the font. The data area still comes in as Times New Roman. I am using Comic Sans MS in my memo. I tried your advice * MERGEFORMAT and it still does not bring over the font. Any other ideas?

Reply | Quote

Hi snax500,

Did you use “* MERGEFORMAT” without the quotes? You might want to recheck your syntax because this should work.

Reply | Quote

I did not use quotes and I am still getting the default format.

Reply | Quote

I’ll assume that Times is the “Normal” font and Comic Sans was applied manually. Trying to change the field code internally probably has no effect because you want to format the result. Turn off the viewing of field codes (Alt+F9), and try selecting the entire document and applying Comic Sans again.

Reply | Quote

I agree that most likely the Comic Sans is direct formatting. To check, in your main document select a word go to Format / Styles / select default paragraph font and apply. If it turns to TNR, then you should edit the normal style to change the font. Nonetheless, * MERGEFORMAT should have held the direct formatting. Another thing to check, before merging, select the mergefield and verify that the font box still reflects Comic Sans.

Reply | Quote

Thank you so much. It was my Format Style Default Font that needed changing. It now works perfectly.

Reply | Quote

One more time… Now when I do the mail merge my font is correct but I keep losing the lines/borders in my table. I put them in the main document, do the merge and then the lines are gone, both on the main and the merged document. I have the mergeformat statement but I am still losing my table lines.

Reply | Quote

Hi snax500,

Well, borders are another story.

As I indicated in my first post there are drawbacks to using this method, namely formatting.

You need to add one of the Table/AutoFormats to obtain your borders. However you can not add the AutoFormat directly. It needs to be added using another switch (l “FormatNum” )or when inserting your Database field you can select an AutoFormat at that time and the proper switch will be added for you.

To add it after the Database field is inserted,

Find an AutoFormat that will work for you and count the position number starting with 0 (zero) from the top of the list starting with (none). So for example if you want to use “Grid 1” the corresponding number is 16.

You would add l “16” to the Database field. Note that this doesn’t include any additional formats – just the borders. For those you need the b “Num” switch.

I have found that the easiest method (with the exception of using Access for this instead ) is to insert a Database field again and add the AutoFormat at that time to use as an example. Then just modify the previous Database field you inserted with the additional switches that were added to the example field.
~~~~~~~~~~~~
Cheers!

Reply | Quote

Hi Bam
I am trying this approach with the datasource as MS Access 97.

Things fall over with the Where clause inserted.
Error reports Word unable to re-establish DDE connection.

Database contains 2 related tables.

Without the where clause the entire related table is successfully inserted into the doc so the syntax is fine til then I guess.

Any thoughts !
Thanks

Geof

Reply | Quote

Hi Folks
Solved it ..

Do not need the ‘ around the {mergefield FieldName}

Thanks for clues

Geof

Reply | Quote

Hi Geof,

Its a problem with the syntax.

Did you include a single quotes (‘) around the MergeField?
‘{ MERGEFIELD strCompanyCode }’

If that isn’t it, can you provide the syntax you are using?
~~~~~~~~~~~~
Cheers!

Reply | Quote

Hi Bam
The ‘ was not needed.
I suspect because the linking data in the Where clause was numeric. I seem to remember something about “qualifying data” in SQL expressions and Access. ???
I dont fuly understand but I have found on occasions when querying from Word textual data has needed ‘, numeric data nothing and on yet other occasions use of + has helped.
It confuses me

Thanks for your help. You have put me onto an alternative solution which represents a big shortcut.

Geof

Reply | Quote

Hi Geof,

Yes you are right, I didn’t consider a data type other than text – sorry.

As for what to use when, the data type for your criteria needs to be declared. With the exception of a number data type, you surround the criteria with additional operators. In an Access query, Access usually determines this for you and adds the necessary operators. But outside of a query builder you need to add them yourself.

Here are a couple of examples:

Number: 123456
Text: “USA”
Dates: #2/10/2000″

Note that if your criteria is already nested in quotes then you need to use a single quote around the criteria such as in the SQL statement of the Database field.
~~~~~~~~~~~~
Cheers!

Reply | Quote

I am almost there. Everything is now working great but I still have one problem. When I do the first mailmerge, everything is great. I then change the date on the main document and in the Excel data source. For some reason the output changes correctly but not the amount of pages. For example if I put in 3/20/01, I get 10 pages of merged data. If I change the date to 4/19/01 I should get only 5 pages of data but I still get the 10 pages. 5 are the form with no data in the address or data field. If I close both files it works again. I tried refreshing the data field but it does not work. What else should I be doing?

Thanks again for all your help. I couldn’t have gotten this far by myself.

Reply | Quote

I’m guessing that you are using the AutoFilter in Excel?

If that is the case then in Tools/Mail Merge select the Query Options command in Step 3 and Filter the records by one field using the “Is not Blank” criteria. Preferably this field be one that would always contain an entry such as a key field.

If this isn’t it, then perhaps you can provide further details for us.
~~~~~~~~~~~~~
Cheers!

Reply | Quote

YOU DID IT!!! It now works perfectly.
Thanks again for all you help.

Cheers!

Reply | Quote

I’m glad all is working for you!

As I indicated in my first response on this, Word doesn’t handle one-to-many merges very well. Now you can see why . There are too many “hoops” you have to jump through to obtain the end result.

Since you already have your database broken down into two tables, you might want to consider placing them in Access in the future since this what Access was designed to do.

You might want to open the Northwind sample database and take a look at it. It uses a Customers and Orders database model and has several examples of what you can do with Access, including an Invoice report.
~~~~~~~~~~~
Cheers!

Reply | Quote