![]() |
#1
|
|||
|
|||
![]() 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
|
||||
|
||||
![]()
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 [Fmr MS MVP - Word] |
#3
|
|||
|
|||
![]()
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
|
||||
|
||||
![]()
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 [Fmr MS MVP - Word] |
#5
|
|||
|
|||
![]()
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
|
||||
|
||||
![]()
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 [Fmr MS MVP - Word] |
#7
|
|||
|
|||
![]()
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
|
||||
|
||||
![]()
Hi bh,
Other than your Excel workbook, none of the attachments is valid.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
![]()
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...
|
#10
|
||||
|
||||
![]()
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 [Fmr 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
|
||||
|
||||
![]()
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 [Fmr MS MVP - Word] |
#12
|
|||
|
|||
![]()
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?
|
#13
|
||||
|
||||
![]()
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
![]()
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 will 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
|
||||
|
||||
![]()
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 [Fmr MS MVP - Word] |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
sedain121 | Mail Merge | 2 | 10-04-2011 07:52 PM |
![]() |
richard82 | Mail Merge | 1 | 07-07-2011 11:28 PM |
![]() |
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 |