Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Mail Merge

Closed Thread
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-27-2011, 11:00 AM
bh24524 bh24524 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2011
Posts: 10
bh24524 is on a distinguished road
Default mail merge with duplicate names but different dollar amounts


Hello, I am trying to use a mail merge to keep track of donations made to an organization in order to send them letters for tax purposes. I use an excel spreadsheet to keep track of the donor info such as their first/last names, amount donated and a check or reference number to put in the letter. Sometimes I will have people that will make multiple donations in a year. I have one particular donor that does one per month consistently. To default, the mail merge wants to give 12 different letters to that same person. I am wondering how I can make it so it will only give that person one letter which will list all 12 donations. I have heard that it is possible, but it is beyond my knowledge, so any help would be greatly appreciated. Thank you.
  #2  
Old 10-03-2011, 05:28 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

You can use Word's Catalogue/Directory Mailmerge facility for this (the terminology depends on the Word version). To see how to do so with any mailmerge data source supported by Word, check out my Microsoft Word Catalogue/Directory Mailmerge Tutorial at:
http://windowssecrets.com/forums/sho...ngs-(2000-2010)
or
http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip
Do read the tutorial before trying to use the mailmerge document included with it.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
  #3  
Old 10-03-2011, 08:09 AM
bh24524 bh24524 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2011
Posts: 10
bh24524 is on a distinguished road
Default

To be honest, I am having difficulty understanding the tutorial. What is in there seems to be different from what I am looking to do in mine. If there is one in there that I could use, I honestly have no idea which one it is. I am not trying to be dense, I think it just is a little beyond my skill level. Sorry
  #4  
Old 10-04-2011, 01:59 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

From your problem description, it sounds like you're trying to group records by donor. That's exactly the kind of thing the tutorial is aimed at. Word doesn't do this sort of thing out of the box, and either macros or extensive field coding is required to achieve the result. The tutorial primarily uses the latter.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
  #5  
Old 10-04-2011, 07:10 AM
bh24524 bh24524 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2011
Posts: 10
bh24524 is on a distinguished road
Default

I pressed alt f9 to view your formula and pasted it into a new document and changed the fields you had in the tutorial to the ones I had in the spreadsheet. When I hit finish and merge, the only thing that came up on each page was MERGSEQ. It didn't input any data though....
  #6  
Old 10-04-2011, 07:46 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

Without seeing your field coding, at least, and preferably some sample data to go with it, it's impossible to diagnose. Can you attach copies of both?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
  #7  
Old 10-04-2011, 08:01 PM
bh24524 bh24524 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2011
Posts: 10
bh24524 is on a distinguished road
Default

alright, is there a way I can personally message the files to you? they have confidential info and I know that if I attach them here everyone can see them...

Last edited by bh24524; 10-04-2011 at 08:04 PM. Reason: mistake
  #8  
Old 10-04-2011, 08:06 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

Other than your Excel workbook, none of the attachments is valid.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
  #9  
Old 10-04-2011, 08:16 PM
bh24524 bh24524 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2011
Posts: 10
bh24524 is on a distinguished road
Default

let me try again...the excel file is deleted, but if you got it that is what is important, I will try attaching the Word ones again. They at least don't have the confidential info. There are three Word files. The first one, MERSEQ, is the one that I just tried pasting your formula in a separate document and changing the field codes. The other two Word files are the actual letters themselves, I don't know if they might kind of give you an idea or anything. Lastly, you'll see all my headers in the excel sheet. I'm not sure if it mattered or not but I originally had the spreadsheet sorted by date, but now it is sorted by first name/organization. hopefully these are valid ones...
Attached Files
File Type: docx MERGESEQ.docx (11.2 KB, 278 views)
File Type: doc WSM_Equipment_donation_letterhead.doc (37.0 KB, 127 views)
File Type: doc WSM_Monetary_donation_letterhead.doc (34.5 KB, 107 views)
  #10  
Old 10-04-2011, 08:29 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

I got the Excel wb OK. Try adapting the first field in the 'Force Each New Category To a New Page' topic to look like:
{QUOTE{IF{MERGESEQ}= 1 {SET Key ""}}
"{IF{QUOTE{MERGEFIELD FirstOrganization}{MERGEFIELD Last}}<> {Key} "{IF{MERGESEQ}> 1 {QUOTE 12}}{MERGEFIELD FirstOrganization \f " "}{MERGEFIELD Last}{SET Key {QUOTE{MERGEFIELD FirstOrganization}{MERGEFIELD Last}}
Date Donation
"} {MERGEFIELD Date} {MERGEFIELD Donation_Amount}
"}

That should give you a list like:
Pastor Roy Aiken
Date Donation
1/15/2011 $75.00
……… Page Break ………
Barry Hartmann
Date Donation
2/14/2011 HP LaserJet Pro M1536dnf MFP Multi-function printer Serial #CNB9BC8D30
2/14/2011 HP PSC 2110 All-In-One Printer Serial #MY26PC6391
2/14/2011 HP C6656A Black Ink Cartridge
2/14/2011 HP C6657A Color Ink Cartridge
……… Page Break ………
Sharon Herbster
Date Donation
1/15/2011 $75.00
……… Page Break ………
etc
__________________
Cheers,
Paul Edstein
[MS MVP - Word]

Last edited by macropod; 10-04-2011 at 08:45 PM. Reason: Added line breaks to field code representation to make it easier to follow.
  #11  
Old 10-04-2011, 08:42 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

Having now had a chance to look at your 'MERGESEQ' document, it's apparent that you didn't simply copy & paste the tutorial's field code - you apparently tried to replicate it and, in doing so, made some invalid changes. For example, you changed:
{IF{MERGESEQ}= 1 {SET Key ""}
to:
{ IF { MERGESEQ } = "1" SET Key"{ MERGEFIELD FirstOrganization }{ MERGEFIELD Last }"}
You also omitted some important spacing between expressions. For example:
{IF { MERGESEQ }>1
needs to have a space after the '>'.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
  #12  
Old 10-05-2011, 06:17 AM
bh24524 bh24524 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2011
Posts: 10
bh24524 is on a distinguished road
Default

Here is what I tried replacing in the MERGSEQ File. It had the same result When I had selected finish and merge. I am not familiar with the specifics of spacing and stuff, but I tried to get it exactly like yours. I think it is but maybe you'll see something I didn't?
Attached Files
File Type: docx MERGESEQ.docx (11.4 KB, 237 views)
  #13  
Old 10-05-2011, 05:18 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

Try the attached. Do note that, unless you separate the source file into two separate files - one for money and the other for goods/services - there is no practical way of having two separate mailmerges. I've altered the donations document slightly to account for this. Also, since I used Word 2003 for this, I had to sort the Excel workbook by donor so that the filtering would work correctly.
Attached Files
File Type: zip WSM.zip (26.6 KB, 859 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
  #14  
Old 10-05-2011, 06:49 PM
bh24524 bh24524 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2011
Posts: 10
bh24524 is on a distinguished road
Default

Alright, we're onto something. I'm sorry you had to do this for me. This is advanced stuff by many people's standards and I'm really lucky if I'm an intermediate user. I did want to ask though, can we also have a check number field in the letter when it is applicable? Sorry I hadn't mentioned that before. Anyway, what I did was I moved your Word mail merge file into my WSM folder and I linked it to the spreadsheet that was already in there as the data source. I made sure in the spreadsheet to sort it how you had it sorted in the spreadsheet you emailed back to me. I did a test finish and merge and everything was great except for the fact that oddly, on the letter for last donor, Word of Life Ministries it did not have the ending lines of the letter. Here is all that it had up to:

Word Of Life Ministries October 5, 2011
4497 Pennsylvania 136
Greensburg, Pa 15601

Dear Word Of Life Ministries,

The Westmoreland School of Ministry would like to thank you for your generous donation(s) listed below:

Date Donation
1/1/2011 1 Classroom(s) Rent-free


I saw that it did have the closing statement in the merge you generated, but I of course can't use that merge as I wi
ll be adding donations to the one I currently have for the remaining months this year. It is only one letter, so if I have to, I CAN just paste the remaining lines into the last document, but I figured I would check. So anyway if you could just let me know about the check number fields mentioned above that would be great. Thank you so much for doing this for me and again, I'm sorry I was unable to comprehend it.
  #15  
Old 10-05-2011, 07:17 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,666
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 behold
Default

Hi bh,

That field coding is taken from the first example under the tutorial's heading 'Using a Dummy Last Record to Generate the Trailing Text Per Group'. As explained there, you need to create a dummy record with just a 0 in the relevant field (in this case, the 'FirstOrganisation' field). You'll see that I did that with the sample workbook attached to my post.

As for the cheque/check #, you can simply add a field coded as:
{IF{MERGEFIELD "check_"}> 0 "(Check #: {MERGEFIELD "check_"})"}
(along with whatever spacing you need) between the fields {MERGEFIELD Date} and {MERGEFIELD "Donation_Amount"}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Saving INDV mail merges During the mail merge sedain121 Mail Merge 2 10-04-2011 07:52 PM
Mail merge with names in content richard82 Mail Merge 1 07-07-2011 11:28 PM
Selecting Names in New Mail Message jackdashack Outlook 2 06-04-2011 04:19 PM
Duplicate Mail wekhine Outlook 0 06-03-2010 09:08 PM
Convert Dollar amount to text GeorgeLawshe Word 0 03-07-2010 10:17 PM


All times are GMT -7. The time now is 08:21 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft