Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Mail Merge

  1. #1
    snax500
    Guest

    Mail Merge

    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.

  2. #2
    BAM
    Guest

    Re: Mail Merge

    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

  3. #3
    snax500
    Guest

    Re: Mail Merge

    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.

  4. #4
    BAM
    Guest

    Re: Mail Merge

    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!

  5. #5
    snax500
    Guest

    Re: Mail Merge

    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

  6. #6
    BAM
    Guest

    Re: Mail Merge

    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!

  7. #7
    snax500
    Guest

    Re: Mail Merge

    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?

  8. #8
    BAM
    Guest

    Re: Mail Merge

    Hi snax500,

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

  9. #9
    snax500
    Guest

    Re: Mail Merge

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

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mail Merge

    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.

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Tallahassee, Florida, USA
    Posts
    901
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge

    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.
    Karen

  12. #12
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Mail Merge

    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

  13. #13
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Mail Merge

    Hi Folks
    Solved it ..

    Do not need the ' around the {mergefield FieldName}

    Thanks for clues

    Geof

  14. #14
    snax500
    Guest

    Re: Mail Merge

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

  15. #15
    BAM
    Guest

    Re: Mail Merge

    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!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •