?
Solved

How smart is mail merge?

Posted on 2011-10-19
30
Medium Priority
?
1,185 Views
1 Endorsement
Last Modified: 2012-06-20
I have designed four post cards on an 8 1/2 X 11 page. It is two sided so it takes two pages. now I want to mail merge. I want to know the sequence I'll need to set up the mail merge in to knw it will continue that sequence. See attached excel file. Alpha, Bravo, Charlie will actually be peoples names and addresss.
Mail-Merge-Post-Cards-4-to-a-pag.xls
1
Comment
Question by:pubbuddy
  • 12
  • 10
  • 5
  • +2
29 Comments
 
LVL 30

Expert Comment

by:flubbster
ID: 37000058
Mail merge is typically taking a mailing list maintained in Excel and merging it into a Word document.

Can you explain in more detail what you are trying to do?
0
 
LVL 7

Expert Comment

by:Dk_guru
ID: 37000333
Simplest for mail merge is to enter your data in four columns per row.  From there, Mail Merge will pull the info and place it where you have set it to go.
For formatting purposes, you may want to use eight column so that you can have two lines on the postcards....
0
 

Author Comment

by:pubbuddy
ID: 37001292
I have an Excel spreadsheet with 8 columns, I'll only be using 5 of the columns. I have done that, successfully prior.
I sent, along, an Excel file that showsa the sequence I'm planning on using. My question is, simply, is Mail Merge up to the task of continuing that sequence?

Another, totally, different issue, my postcards are built with, "Text Boxes". Will that be a problem for Mial Merge?
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37002647
Mail Merge is very smart, so, in theory you could write logic that would increment and decrement the record # counter and instruct Word to Merge Record#, such that a single post-card sized document dynamically adjusts.  
Word has mail merge variables which amount to commands to tell it to advance to the next row of data, so you could have the document put field1, field2 for row 1 on "1 Alpha" and field3, field4 for row 1 on "2.Alpha"
However, as stated, it will be easier to have a single document which consists of 2 8.5x11" pages, and on page 1 is the FRONT of 4 postcards, and page 2 is the BACK of 4 postcards.
I agree with Dk-guru, seems to me your data should then be organized into 4 (four) columns not 8, because in your diagram 3.Bravo and 4.Bravo are the same "bravo", and doubling-up the datapoints in some necessary sequence in the datalist is just one more place where human error could mess-up the sequence.
However I will go even FURTHER and say your data should be a simple list, ONE ROW for each record.
Then, here is what I would do, at the beginning of a set of four postcards (two pages, four fronts followed by four backs on "page 2" of the document, I would have mailmerge CODE that sets a bunch of VARIABLES like this
{ SET Name1 { MERGEFIELD “Name” } }
{ SET Address1 { MERGEFIELD “Address” } }
{ SET City1 { MERGEFIELD “City” } }
{ SET Zip1 { MERGEFIELD “Zip” } }
«Next Record»
{ SET Name2 { MERGEFIELD “Name” } }
... repeating from above but setting another set of variables (mailmerge calls the variable a bookmark)
«Next Record»
{ SET Name3 { MERGEFIELD “Name” } }
«Next Record»
{ SET Name4 { MERGEFIELD “Name” } }
{ SET Address4 { MERGEFIELD “Address” } }
{ SET City4 { MERGEFIELD “City” } }
{ SET Zip4 { MERGEFIELD “Zip” } }

.... and then place the
{ REF Name1 }
{ REF Address1 }
.. and
{ REF Name2 }
{ REF Address2 }
etc and Name3 and Name4 on the Alpha Bravo Charlie and Delta front and backs of postcards on pages 1 and 2 of the two page document as required.
When you mail merge, each merge should go through 4 rows of data from your list thanks to the embedded <<Next Record>> field that increments through the record rows, 4 at a time.

The SET function sets the value of a bookmark, Donor1, to the value of the Donor field in the current record.  The REF function resolves the value of a bookmark.
0
 

Author Comment

by:pubbuddy
ID: 37009899
These are the headings in my Data Source (2 other columns, not in Mail Merge, are hidden at the right end)
Owner(s) First name,   Last Name,   Mail Address,   City,   Zip,   Property Address,   Mail Street Address

Where does this code reside? Is it VB? I typed in =set & = ref in Excel; not recognized.

Row 1:
Field 1 & Field 2 Appear on Front & Back of the post Card. Field 3 - 6 Goes on the Front only. Field 7 on Back only.

You suggested: 2 8.5x11" pages, and on page 1 is the FRONT of 4 postcards, and page 2 is the BACK of 4 postcards.
This is exactly what I have.
I'm trying to edit your code to do what i hope will work. I hope, also, you'll tell me where I made a mess.
Thanks! Later.

Frank
0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37010180
Oh I see where the confusion is coming from, I'll try not to beat a horse on this point.  
Mail Merge is a function of MS Word and word processors, and perhaps publishing and forms design and contact email such as Publisher, Works, and MS Access, and Outlook, but notably not Excel.  Even when you go to built-in help in Excel and put in the help search term "mail merge" it gives you topic "Create a Word mail merge with Excel data"   As @flubster pointed out, Excel can be used to hold the list of data in a .xls, .xlb, .csv, "into a Word document", (or the list of data could also be a plain text, any ODBC data source, etc.)
Although your attachment is .xls, it is stated that it is to illustrate your plan.  The question and Zones and Tags never suggested that you were designing the postcards in only Excel as well, and the question title "How smart is mail merge? implies, everyone will infer, means Word (or third-party equivalent) word processor.
So the answer is to use Microsoft Word.  The embedding of codes as I have suggested them is done in a Word document.
If you've made the postcards in Excel, I hope it's not too difficult to redo them in Word, possibly art and text frames can hold copy/paste from Excel.  
Your Word document will be four postcards, front on page 1, backs on page 2, for double-sided printing.  Be cautious that inserting spaces during merge never causes your document to go to a third page, or the front and backs of postcards will subsequently mismatch.  Suggestion, if you preview, or mail merge to another Word document, you can preview the merged results, make any tweaks, print just selected page ranges, etc.
Here's a video author you may find instructive http://www.youtube.com/watch?v=8Wd1OxFDP3E
0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37010193
Here's 2007 version http://www.youtube.com/watch?v=_9ExKwXQ3KQ , if you're using 2003, 2002 or 2000 those have the older style toolbars and menus rather than the ribbon-bar, slightly different UI, but the idea will be the same.
0
 
LVL 26

Expert Comment

by:Fred Marshall
ID: 37011314
I can't open the Excel file for some reason - so I can't see what you have there.
But, from your description, it seems this should go fairly easily.

Are you using Word Mail Merge or not?  Key question.

If so, you design the post cards in Word and run Mail Merge pointing to your name/address list file.  That's it!
0
 
LVL 26

Expert Comment

by:Fred Marshall
ID: 37011438
OK, I opened the excel file in Windows 7 / Office 2010.

You have not defined what 1.Alpha and 2.Alpha are...  But, the implication is that they both include some list-related data.  So, I think the simple answer is no.

Even things like left/right top/bottom sequencing may be an issue.  But, I think that Word Mail Merge generally goes left/right and then top/bottom.

Here's what I would try (under the assumption that Mail Merge can't do what you want without some help).

1) Create a database (i.e. list) that has entries like this:
1. Alpha
2. Alpha
3. Bravo
4.Bravo

NOTE that each addressee has two entries in the list - one for the front Mail Merge information and one for the back Mail Merge information.

Now add a column next to the list that simply numbers each row.
1
2
3
4
..

Now add another column with a formula that operates on the column above:
The result you want is this:
1>1
2>6
3>2
4>5
5>3
6>8
7>4
8>7
etc.

Then COPY this new column and PASTE SPECIAL VALUES ONLY into yet another new column.

Now sort the list on this last column in Ascending order.

The result is a list that will print in Mail Merge in the order you want.

The only question then is: "What is the formula?"  Here is a really simple approach to that:

Fill the first 8 rows with the order you want (assuming that the column is A and the data starts at row 2... so A2 is the first entry cell:
A2: 1
A3: 6
A4: 2
A5: 5
A6: 3
A7: 8
A8: 4
A9: 7

In the 9th row (A10) , enter:  +A2+8
Copy the contents of this cell down the column to generate all the ordering numbers you need.

I've attached an example.  You can add all the data columns you want.
You may have to name the columns the SAME for the back as for the front so the mail merge won't get confused.

Example:
The "real" data is:

Date First Last Address1 City State ZIP

The first column would go on the front and the last 6 on the back.
So you might have:
First Last Address1 City State ZIP for the front with the date information under "First" for the front and the others empty for the front.

Or, you might have:
Date First Last Address1 City State ZIP
for both with the appropriate cells empty for front and back.
PostCardSort.xlsx
0
 

Author Comment

by:pubbuddy
ID: 37015601
This for ocanada:

1. My Mail Merge file is in MS Word
There are (2) 8 X 1/2 inch pages which make up the Front & Back sides of four Post Cards
There are six fields on the front side & 3 fields on the back side of the Post Card
Two of the fields are common to Front & Back


2. My Data Source is MS Excel
There 7 Columns on the Excel file

FRONT PAGE                                                                        
{ SET Owner First1 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name1 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Address1 { MERGEFIELD “Mail Address” } }                                                                        
{ SET City1 { MERGEFIELD “City” } }                                                                        
{ SET Zip1 { MERGEFIELD “Zip” } }                                                                        
{ SET Property Address1 { MERGEFIELD “Property Address” } }                                                                        
«Next Record»                                                                        
{ SET Owner First2 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name2 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Address2 { MERGEFIELD “Mail Address” } }                                                                        
{ SET City2 { MERGEFIELD “City” } }                                                                        
{ SET Zip2 { MERGEFIELD “Zip” } }                                                                        
{ SET Property Address2 { MERGEFIELD “Property Address” } }                                                                        
«Next Record»                                                                        
{ SET Owner First3 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name3 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Address3 { MERGEFIELD “Mail Address” } }                                                                        
{ SET City3 { MERGEFIELD “City” } }                                                                        
{ SET Zip3 { MERGEFIELD “Zip” } }                                                                        
{ SET Property Address1 { MERGEFIELD “Property Address” } }                                                                        
«Next Record»                                                                        
{ SET Owner First4 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name4 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Address4 { MERGEFIELD “Mail Address” } }                                                                        
{ SET City4 { MERGEFIELD “City” } }                                                                        
{ SET Zip4 { MERGEFIELD “Zip” } }                                                                        
{ SET Property Address4 { MERGEFIELD “Property Address” } }                                                                  
                                                                  
                                                                  
Owner First & Last Name are common to Front & Back of Post Card                        
                                                                        
BACK PAGE                                                                        
{ SET Owner First1 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name1 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Street Address1 { MERGEFIELD “Mail Address” } }                                                                        
«Next Record»                                                                        
{ SET Owner First2 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name2 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Street Address2 { MERGEFIELD “Mail Address” } }                                                                        
«Next Record»                                                                        
{ SET Owner First3 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name3 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Street Address3 { MERGEFIELD “Mail Address” } }                                                                        
«Next Record»                                                                        
{ SET Owner First4 { MERGEFIELD “Owner First” } }                                                                        
{ SET Last Name4 { MERGEFIELD “Last Name” } }                                                                        
{ SET Mail Street Address4 { MERGEFIELD “Mail Address” } }                                                                        








Please review attached code.

Thanks!

Frank



0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37015695
Nope.
You do NOT <<Next Record>> "between cards" on the output.
What you do is read four records worth into a collection of "variables" (aka Bookmarks)
then you put those variables out as the four postcards twice (once front once back)
then the 2 page document ends
then the mail merge continues back at the beginning of the document, which has "code" to set four groups of variables, each of the four reads one record then <<Next Record>>
all of that code that "SET"s a variable (bookmark) goes at the top of page one BEFORE the first postcard even, and you do not have to set the same variable more than once (don't set Owner_First_1 two different times)
by the way I don't think you can have spaces for bookmark (variable) names so put underscores (notice mine were Name1 Name2 Address1 Address2 no spaces, I think you CAN have spaces in data merge source fieldnames since they get enclosed in quotationmarks.
AND the part you seem to be missing, you then use { REF Owner_First_1 } (and 2 3 and 4) on the postcards instead of using MERGEFIELD (you already used MERGEFIELDs to set the values of the four sets of variables.
At least not the way I am saying you should do it, if you want to follow others ideas and repeat your data over and over in your excel spreadsheet list, well that would be a different approach.
Understand?  I don't know how to make it any clearer sorry to say.
So let's recap, instead of trying to put mergefields on the postcards directly, which is extremely complicated when you are trying to skip forward and back between four records of data on a single two page document as far as Word is concerened, it's easier to read 4 records into a bunch of variables / aka bookmarks / ie placeholders, and then have those REF placeholders on the four front and four backs of cards on the document.  And since you <<Next Record>> three times when reading and populating the placeholders at the top of the document, when that document is merged and it goes to merge the next document, the next one starts at record 5 instead of record 2.
0
 

Author Comment

by:pubbuddy
ID: 37017767
Thank you:
don't set Owner_First_1 two different times)

This statement is in your response. "Owner First", appears on the front and back of the Post Card, as does, "Last name". Do I have to call it something else?
How does the code tell it to arrive twice?

Also, is there a book you can recommend for this code? I can see I'll make a pain in the butt, of myself, before I get it. What is it? Is it VB?

Thanks!

Bob
0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37020543
Bob and Frank

You can call your { SET variablenames } anything you want with the caveat I don't think you can have spaces in the variablename.  You must call your { MERGEFIELD "whateverthenameofthefieldis" } exactly whatever the name of the field is coming from the database list, which in an Excel list is usually the column headings contained in row 1 (so the actual row 1 "data" technically starts in row 2)

In our case we are { SET variablenames { MERGEFIELD "whateverthenameofthefieldis" } }

Think of it this way
curly braces is one of the signs that tells Word this is some embedded "codes" that Word must evaluate, with commands like SET and REF

When you tell Word to mail merge it is going to read one record (and normally just one) from the mail-merge datalist file you tell word to connect to this mail-merge document
Normally, you are right,... you would just put { MERGEFIELD "fieldname at top of column a" } { MEREGFIELD "fieldname at top of column b" } and even { MERGEFIELD "fieldname at top of column a" } several times on the document if you want it several times on the document, those fields are typically names and addresses,
and when Word finishes that document, it then skips to the next record and if there is another record it's not the end of the list, it does the document again, and over and over until the end
BUT (!) what you are doing in this case is telling it to { SET variable_alpha_a  ((to be equal to whatever)) { MERGEFIELD "fieldname a from list" } ((works out to be)) } and { SET variable_alpha_b  ((to be equal to whatever)) { MERGEFIELD "fieldname b from list" } etc
for however many fields (ie name, address, zip, birthdate, whatever) you need from the record to go anywhere on the postcards front and/or back
AND THEN you put { REF variable_alpha_a } { REF variable_alpha_b } on the actual postcards
SO it is two steps instead of one, first read the mergefields from a record of the list BUT instead of right on the card, use the mergefield to SET a handful of variables (bookmarks / placeholders) and THEN use whatever value the variable has ( { REF whatever } ) as what to put on the appropriate postcard in the appropriate places by putting { REF variable_alpha_a } where it goes on the document.

WHY?  Why the extra step of reading all the fields of a single record and sticking them into placeholders just so you can immediately turn around and plunk them on the document?  Ahhhh, BECAUSE there's a twist, you're going to read a bunch of fields from record 1 (alpha) into variables, AND then a <<Next Record>> advances to record 2, which you are going to read a bunch of fields from record 2 (bravo) into different variables for the second postcard of the same two page mail-merge single document, AND then a <<Next Record>> advances to record 3 set a bunch of similar (name address etc) variables but different ( it's 3 charlie ones ) and <<Next Record>> one more time and set the fourth cluster of variables from the very same mergefields as the first three times but thanks to the three next record commands between we're at record row 4 (delta).
NOW all this code and we haven't even printed a single line of anything on the document yet.  Now that we have four clusters of variables set from four rows of reading mergefields, now we use { REF placeholdername } all over the document on the postcards in the staggered pattern we need for double-sided printing.  Word's mailmerge gets to the end of page 2 of postcards, then proceeds to mail merge some more if there are still records, which should be row records 5 6 7 & 8, then 9 10 11 & 12, and so on.

So in a single record scenario you WOULD put { MERGEFIELD "this" } and { MERGEFIELD "that" } multiple times around the document, BUT that's not our scenario here because you also need to repeat fields from SEVERAL records on the fronts and backs of postcards, so you SET each record's fields (whatever they're called ) into a bunch of placeholders ( call them whatever you want, meaningful names is better obviously, no spaces for those names though ) but four (4) different sets of placeholders, four clusters, or in programmer's lingo, an "array" of sorts *** with the three <<Next Record>> between making it possible to SET four cards worth of placeholders with { SET commands and then the { REF placeholdername } commands make it possible to put them all on the cards in whatever mixed-up order you need.

*** (because Word bookmarks don't directly support constructing subscript arrays )

SO, in your reply above 10/23/11 06:18 PM, ID: 37015601
A)
everything after FRONT PAGE  and before BACK PAGE is almost right **

** almost because MERGEFIELD names can have spaces, but SET names cannot, so it would be
{ SET Owner_First_1 { MERGEFIELD “Owner First” } }    
or
{ SET OwnerFirst1 { MERGEFIELD “Owner First” } }    
but not
{ SET Owner First1 { MERGEFIELD “Owner First” } }    

B)
all of that goes at the top of the document before any postcards

C)
now on the postcards don't put any MERGEFIELD, just put a bunch of:
{ REF Owner_First_1 } { REF Last_Name_1 }
{ REF Mail_Address_1  }                                                                        
{ REF City_1 } { REF Sate_1 }                                                              
{ REF Zip_1 }                                                                        
{ REF Property_Address_1 }  

and lastly
D)
you'd better use "tab stops" and "tabs" in your document, so your ruler will have a
........L...........................................................................................................L..................................
<tab> { REF Owner_First_1 } { REF Last_Name_1 } <tab> { REF Owner_First_2 } { REF Last_Name_2 }
<tab> { REF Mail_Address_1  } <tab> { REF Mail_Address_2  }

because if you don't, longer addresses on alphas' are going to shove bravos' further to the right but you want them perfectly aligned on the postcards

      Joe Smith                                                                                             Mary Brown
      123 Main Street                                                                                   204 Brown Street

not this
   Fred Wowthisisareallylongnamedude                                                                          John Doe
   100 Elm Ave                                                                            1 John's Place
0
 
LVL 26

Expert Comment

by:Fred Marshall
ID: 37021163
The reason I like the Excel database solution is that you can directly see an interim result that has meaning.
Then the merge in Word is pretty direct.
But if you can deal with the code then that's fine too.  For me it's an added layer of complication.

Fred
0
 

Author Comment

by:pubbuddy
ID: 37021885
This is for ocanada.
Thank you, so  much, for what you did. It is beginning to penetrate.
This is copied from your latest:
B)
all of that goes at the top of the document before any postcards

Does this mean, then, that all of the code could be on page 1 of the Word documnent and page 2 would be the 4 front pages and page 3 would be the 4 back pages?

Can I send you my Word file with the Post Cards? If you saw them it would be golden.
One other thing. Each Postcard is made up of 3 Text Boxes. One big one for the front and one big one for the back and then a small one for the mailing address that sits on top of the big one on the front. This avoids the alignment problems you mentioned.

Thanks again.
You're great.

Frank

P.S. Now I have three websites saved in Favorites which are dedicated to MS Word Mail Merge & some Code. You lit a fire.
0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37022351
@pubbuddy no the code and front postcards should both fit on page 1, the backs page 2.
If you have 3 pages it is not going to double-side correctly now is it?

@fmarshall, I agree there is another way to do it, exactly as you suggest, which as you pointed out is to do some trickery with the data list, and certainly the copy / paste values is a likely trick to use, so that the list ends up going from this

Name,Address,City,Zip <CRLF>
"Joe Alpha","123 Main St","St. Louis","123456" <CRLF>
"Mary Bravo","204 Brown St.","Milwakee","41345" <CRLF>
"Charlie","3 Charles St.","Charleston","33333" <CRLF>
"Delta Burke","44 Delta","Mississipi","44444" <CRLF>
"Echo Edward","5 East Way","East Rutherford","55555" <CRLF>
etc
**** where <CRLF> represents CR carriage return LF line feed and/or <EOL> end-of-line

to something like this
Name1,Address1,City1,Zip1,Name2,Address2,City2,Zip2,Name3,Address3,City3,Zip3,Name4,Address4,City4,Zip4
"Joe Alpha","123 Main St","St. Louis","123456","Mary Bravo",204 Brown St.","Milwakee","41345","Charlie","3 Charles St.","Charleston","33333","Delta Burke","44 Delta","Mississipi","44444" <CRLF>
"Mary Bravo",204 Brown St.","Milwakee","41345",","Charlie","3 Charles St.","Charleston","33333","Delta Burke","44 Delta","Mississipi","44444","Echo Edward","5 East Way","East Rutherford","55555" <CRLF>
"Charlie","3 Charles St.","Charleston","33333","Delta Burke","44 Delta","Mississipi","44444","Echo Edward","5 East Way","East Rutherford","55555","Fred Flintstone","6 Sixth St","Scranton","66666" <CRLF>
**** where <CRLF> represents CR carriage return LF line feed and/or <EOL> end-of-line / end of row
(sorry this ExpertsExchange box word-wraps )

because if you do this above example of quadrupling of the columns by adding to the right side from the next rows below in the data list, probably in Excel as suggested, then in the Word you could do
 
front
{ MERGEFIELD Name1} <tab> { MERGEFIELD Name2}
{ MERGEFIELD Address1} <tab> { MERGEFIELD Address2}

{ MERGEFIELD Name3} <tab> { MERGEFIELD Name4}
{ MERGEFIELD Address3} <tab> { MERGEFIELD Address4}

back
{ MERGEFIELD Name2} <tab> { MERGEFIELD Name1}
{ MERGEFIELD Address2} <tab> { MERGEFIELD Address1}

{ MERGEFIELD Name4} <tab> { MERGEFIELD Name3}
{ MERGEFIELD Address4} <tab> { MERGEFIELD Address3}
<<Next Record>>
<<Next Record>>
<<Next Record>>
<EOF> (end of file / document)

and that too should accomplish the task.

OR after copying rows to the right so there are four times as many coulmns if you can also figure out a way to delete 3 out of 4 rows in Excel / data list then even the three <<Next Record>> are not necessary.  It depends how much of the "smarts" are you going to execute in Excel (data list manipulation) and how much in Word (document / mail-merge smarts).
0
 

Author Comment

by:pubbuddy
ID: 37085453
This is for ocanada:

@pubbuddy no the code and front postcards should both fit on page 1, the backs page 2

MY post cards are 4.25 in X 5.5 in and they are smaller than standard. The information needed is just squeezed in. There is not 1/100 of an in on the page on either side. Where can I put the code?
0
 
LVL 26

Expert Comment

by:Fred Marshall
ID: 37085730
You can put the database in Excel and never put it into Word.
But, you use the Word mail merge and point at the Excel file.
That should work as suggested above.
I'm not sure how you format front vs. back using the method I suggested.  If that becomes an issue then I can think about it some more and try to help with that.
0
 

Author Comment

by:pubbuddy
ID: 37088019
This is in response to fmarshall:

1. The Post Cards are in MS Word. The take up ALL of the space on the 8 1/2 X 11 inch paper.
2. The mailing list is in MS Excel.


These questions need to be cleared up before I can proceed. With what you suggest, do I have to have code on page one? Does the fact that the Page One & Page Two of my Word document are, "Text Boxes", interfere with the strategy? Can I send you the files? Nobody understands what they are up against. This, really, comes down to four, individual, merges combined into one. I'm hoping to trick the Mail Merge function into not noticing.
Please let me send you my stuff so we can both talk about the same thing.

0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37088281
Hmmm, could it be you are typing the commands in as if they are "plain text" instead of inserting the field codes so they get interpreted as "code"?  Could it be that that is resulting in a whole bunch of "text" at the top of page 1 "pushing" your cards down further on the page?  
I think there are two things that can help you:
1. To show or hide field codes for all fields in the document, press ALT+F9.
That should allow you to see the codes in curly braces with shading that are being inserted.
Also, the other part depends if you're using Word 2007/2010 with the revambed user interface and ribbon bars, or the older Word 95, 97, 2000, 2002(XP), 2003 with toolbars and menus.  In either case
2. you should be able to choose the Insert menu and choose to Insert, Field.... .  This brings up the Insert Field dialog box with is fairly exhaustive in its own right, notice you can narrow the choice of fields to the Mail Merge Category, and you may also find it helpful to toggle the Field Codes/Hide Codes button wich alternates between a pick-and-choose and a type the code in the textbox type interface.

Cursor at top of the mail-merge document.  click Insert, Field... and in the Mail Merge category choose SET and then click the Field Codes button, and next to the resulting field codes box type SET FirstName_1 "blahblahblah" and click OK

Now if you press Alt-F9 you should see the resulting code appear and disappear.

Now just for practice, highlight the "blahblahblah" (or "temporarytext" in my illustaration) and do another Insert, Field... and this time choose MERGEFIELD and the name of one of the columns from the database list.

You should get something that looks like the third image.

Now that there is some actual CODE embedded in your document, you should notice that as soon as you put your cursor on any part of a code segment, the entire segment is shaded grey, and you can alter or change the code, copy and paste, and, like with the third step from above, insert code inside of code.


If you have  \* MERGEFORMAT in there?  No problem, that was the "preserve formatting" checkbox and you can take those out, or leave them, it just tells word that if you bold or underline stuff to retain that formatting step1 step2mail-merge-show-fields-2.JPG
0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37088299
Now you should be able to have all the needed code at the beginning of the document, which shows when you press Alt-F9 so you can work on the code, and then when you press Alt-F9 to hide it, voila it should be as if the code is not even there and the cards should fit on page 1 nicely.

Careful not to type some plain text / carriage returns mixed in amongst the code or else when you hide the code the plain text carriage returns are still going to be there taking up space at the top of your document.  If you make syntax errors or you codes are not recognized as code, well that'll just end up as unwanted text at the top of the document instead of the code it needs to be, and would be your clue something is wrong with your code.  Another clue is does your code get shaded grey.
0
 
LVL 26

Expert Comment

by:Fred Marshall
ID: 37091038
Go ahead and post the files and I'll take a look.

For my suggestion (which may be incomplete for your situation/data) there is no "code".  Just a straight mail merge.
0
 

Author Comment

by:pubbuddy
ID: 37093995

Hi fmarshall:

I'm glad you O.K'd sharing these files. It will make success much more certain. In the post card file, it is essential that you set the zoom to 56%. There you should see The front side of 4 cards on the left & the back side of 4 cards on your right; (2) 8 1/2 X 11 inche pages. Look at the gap beetween the pages. Imaine it is a pivot and the right page revolves around it back into the screen to become congruent with the left page. Then fronts will be on top pf backs.

Cards for fmarshall

Spreadsheet for fmarshall
0
 

Author Comment

by:pubbuddy
ID: 37104712
This is for ocanada:

Am I warm?

FRONT PAGE
{ SET Owner First1 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name1 { MERGEFIELD “LastName” } }
{ SET Mail Address1 { MERGEFIELD “MailAddress” } }
{ SET City1 { MERGEFIELD “City” } }
{ SET Zip1 { MERGEFIELD “Zip” } }
{ SET Property Address1 { MERGEFIELD “PropertyAddress” } }

{ SET Owner First2 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name2 { MERGEFIELD “LastName” } }
{ SET Mail Address2 { MERGEFIELD “MailAddress” } }
{ SET City2 { MERGEFIELD “City” } }
{ SET Zip2 { MERGEFIELD “Zip” } }
{ SET Property Address2 { MERGEFIELD “PropertyAddress” } }

{ SET Owner First3 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name3 { MERGEFIELD “LastName” } }
{ SET Mail Address3 { MERGEFIELD “MailAddress” } }
{ SET City3 { MERGEFIELD “City” } }
{ SET Zip3 { MERGEFIELD “Zip” } }
{ SET Property Address3 { MERGEFIELD “PropertyAddress” } }

{ SET Owner First4 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name4 { MERGEFIELD “LastName” } }
{ SET Mail Address4 { MERGEFIELD “MailAddress” } }
{ SET City4 { MERGEFIELD “City” } }
{ SET Zip4 { MERGEFIELD “Zip” } }
{ SET Property Address4 { MERGEFIELD “PropertyAddress” } }
                                                                 
                                                                 
{ REF  OwnerFirst1 }
{ REF  LastName1 }
{ REF MailAddress1 }
{ REF City1 }
{ REF Zip1 }
{ REF PropertyAddress1 }
«Next Record»
{ REF OwnerFirst2 }
{ REF LastName2 }
{ REF MailAddress2 }
{ REF City2 }
{ REF Zip2 }
{ REF PropertyAddress2 }
«Next Record»
{ REF OwnerFirst3 }
{ REF LastName3 }
{ REF MailAddress3 }
{ REF City3 }
{ REF Zip3 }
{ REF PropertyAddress3 }
«Next Record»
{ REF OwnerFirst4 }
{ REF LastName4 }
{ REF MailAddress4 }
{ REF City4 }
{ REF Zip4 }
{ REF PropertyAddress4 }

BACK PAGE

{ SET Owner_First1 { “OwnerFirst” } }
{ SET Last_Name1 { “LastName” } }
{ SET Mail_Street_Address1 { “MailStreetAddress” } }

{ SET Owner_First2 { “OwnerFirst” } }
{ SET Last_Name2 { “Last Name” } }
{ SET Mail_Street_Address2 { “MailStreetAddress” } }

{ SET Owner_First3 { “Owner First” } }
{ SET Last_Name3 { “Last Name” } }
{ SET Mail_Street_Address3 { “MailStreetAddress” } }

{ SET Owner_First4 { “Owner First” } }
{ SET Last_Name4 { “Last Name” } }
{ SET Mail_Street_Address4 { “MailStreetAddress” } }


{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
«Next Record»
{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
«Next Record»
{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
«Next Record»
{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
0
 

Author Comment

by:pubbuddy
ID: 37104791
This is for ocanada:

Am I warm?
I did this earlier but it didn't take.

FRONT PAGE
{ SET Owner First1 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name1 { MERGEFIELD “LastName” } }
{ SET Mail Address1 { MERGEFIELD “MailAddress” } }
{ SET City1 { MERGEFIELD “City” } }
{ SET Zip1 { MERGEFIELD “Zip” } }
{ SET Property Address1 { MERGEFIELD “PropertyAddress” } }

{ SET Owner First2 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name2 { MERGEFIELD “LastName” } }
{ SET Mail Address2 { MERGEFIELD “MailAddress” } }
{ SET City2 { MERGEFIELD “City” } }
{ SET Zip2 { MERGEFIELD “Zip” } }
{ SET Property Address2 { MERGEFIELD “PropertyAddress” } }

{ SET Owner First3 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name3 { MERGEFIELD “LastName” } }
{ SET Mail Address3 { MERGEFIELD “MailAddress” } }
{ SET City3 { MERGEFIELD “City” } }
{ SET Zip3 { MERGEFIELD “Zip” } }
{ SET Property Address3 { MERGEFIELD “PropertyAddress” } }

{ SET Owner First4 { MERGEFIELD “OwnerFirst” } }
{ SET Last Name4 { MERGEFIELD “LastName” } }
{ SET Mail Address4 { MERGEFIELD “MailAddress” } }
{ SET City4 { MERGEFIELD “City” } }
{ SET Zip4 { MERGEFIELD “Zip” } }
{ SET Property Address4 { MERGEFIELD “PropertyAddress” } }
                                                                 
                                                                 
{ REF  OwnerFirst1 }
{ REF  LastName1 }
{ REF MailAddress1 }
{ REF City1 }
{ REF Zip1 }
{ REF PropertyAddress1 }
«Next Record»
{ REF OwnerFirst2 }
{ REF LastName2 }
{ REF MailAddress2 }
{ REF City2 }
{ REF Zip2 }
{ REF PropertyAddress2 }
«Next Record»
{ REF OwnerFirst3 }
{ REF LastName3 }
{ REF MailAddress3 }
{ REF City3 }
{ REF Zip3 }
{ REF PropertyAddress3 }
«Next Record»
{ REF OwnerFirst4 }
{ REF LastName4 }
{ REF MailAddress4 }
{ REF City4 }
{ REF Zip4 }
{ REF PropertyAddress4 }


BACK PAGE

{ SET Owner_First1 { “OwnerFirst” } }
{ SET Last_Name1 { “LastName” } }
{ SET Mail_Street_Address1 { “MailStreetAddress” } }

{ SET Owner_First2 { “OwnerFirst” } }
{ SET Last_Name2 { “Last Name” } }
{ SET Mail_Street_Address2 { “MailStreetAddress” } }

{ SET Owner_First3 { “Owner First” } }
{ SET Last_Name3 { “Last Name” } }
{ SET Mail_Street_Address3 { “MailStreetAddress” } }

{ SET Owner_First4 { “Owner First” } }
{ SET Last_Name4 { “Last Name” } }
{ SET Mail_Street_Address4 { “MailStreetAddress” } }


{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
«Next Record»
{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
«Next Record»
{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
«Next Record»
{ REF OwnerFirst }
{ REF LastName }
{ REF MailStreetAddress }
0
 
LVL 11

Expert Comment

by:ocanada_techguy
ID: 37106043
Close, but not quite, you're still missing the important concepts
you can't use different spellings for the variablename and expect it to work, you have to be consistent
you are reading four records all at the beginning, not between cards

FRONT PAGE
{ SET Owner_First1 { MERGEFIELD “OwnerFirst” } }    ** notice the underscore, spaces not allowed in bookmark name
{ SET Last_Name1 { MERGEFIELD “LastName” } }{ SET Mail_Address1 { MERGEFIELD “MailAddress” } }
{ SET City1 { MERGEFIELD “City” } }{ SET Zip1 { MERGEFIELD “Zip” } }{ SET Property_Address1 { MERGEFIELD “PropertyAddress” } }  ** they can all be on one line even
«Next Record»  **  here, here is where you need to advance to the next record before setting the 2nd set of variables
** and make sure you use the insert field of the mailmerge category "next record" command so the actual code commands are going in not just plain text angle bracket angle bracket n e x...
{ SET Owner_First2 { MERGEFIELD “OwnerFirst” } }  ** again, spaces not allowed in Owner First2
{ SET Last_Name2 { MERGEFIELD “LastName” } }
{ SET Mail_Address2 { MERGEFIELD “MailAddress” } }{ SET City2 { MERGEFIELD “City” } }{ SET Zip2 { MERGEFIELD “Zip” } }{ SET Property_Address2 { MERGEFIELD “PropertyAddress” } }
«Next Record»
{ SET Owner_First3 { MERGEFIELD “OwnerFirst” } }
{ SET Last_Name3 { MERGEFIELD “LastName” } }
{ SET Mail_Address3 { MERGEFIELD “MailAddress” } }
{ SET City3 { MERGEFIELD “City” } }
{ SET Zip3 { MERGEFIELD “Zip” } }
{ SET Property_Address3 { MERGEFIELD “PropertyAddress” } }
«Next Record»
{ SET Owner_First4 { MERGEFIELD “OwnerFirst” } }
{ SET Last_Name4 { MERGEFIELD “LastName” } }
{ SET Mail_Address4 { MERGEFIELD “MailAddress” } }
{ SET City4 { MERGEFIELD “City” } }
{ SET Zip4 { MERGEFIELD “Zip” } }
{ SET Property_Address4 { MERGEFIELD “PropertyAddress” } }
                                                                 
 ON THE FRONT OF THE CARDS IN THEIR RESPECTIVE PLACE                                                                
{ REF Owner_First1 }  ** why did you have this variable typed differently than when you "set" it??
{ REF Last_Name1 } ** and again use the insert field command to insert actual code
{ REF Mail_Address1 }
{ REF City1 }
{ REF Zip1 }
{ REF Property_Address1 }
                            **  no, there's no next record going here
{ REF Owner_First2 }
{ REF Last_Name2 }
{ REF Mail_Address2 }
{ REF City2 }
{ REF Zip2 }
{ REF Property_Address2 }

{ REF Owner_First3 }
{ REF Last_Name3 }
{ REF Mail_Address3 }
{ REF City3 }
{ REF Zip3 }
{ REF Property_Address3 }

{ REF Owner_First4 }
{ REF Last_Name4 }
{ REF Mail_Address4 }
{ REF City4 }
{ REF Zip4 }
{ REF Property_Address4 }


BACK PAGE
** you don't need to set bookmarks you already set them at the top of page one

{ REF Owner_First2 }  ** again, you have to use the variable (aka bookmark) name exactly the one you set, not some different spelling or syntax of it, you had space, I put underscore, this one didn't have space and didn't have the number 1 on the end?? fixed
{ REF Last_Name2 }
{ REF Mail_Address2 }
               ** there's no next record here either, you only do that when reading IN the data for the SETs from the MERGEFIELD commands, not spitting it out with the REF commands
{ REF Owner_First1 }
{ REF Last_Name1 }
{ REF Mail_Address1 }
             ** I am assuming because of the double-side flip on edge, that 2 if left of 1 on the backs, and 4 left of 3, positioned inside the frame for accurate alignment
{ REF Owner_First4 }                                           { REF Owner_First3 }
{ REF Last_Name4 }                                             { REF Last_Name3 }
{ REF Mail_Address4 }                                         { REF Mail_Address3 }

END OF DOCUMENT ** (if there are more mail merge records left, it will repeat with the next four records)

Equally IMPORTANT to understand is for this stuff to be seen as "code" and not just "text", you have to use the Insert Field command to insert the NEXT RECORD, all of the SET, REF, and MERGEFIELD commands, and when you Alt-F9 you will see them, and they show as "greyed".  Notice the curly braces happen automatically when you Insert Field , whether it is a REF, SET, MERGEFIELD or NEXT RECORD command you're inserting.  Then you can copy/paste and adjust them as long as they keep being interpreted as codes and not plain text they will work, if you accidentally remove the F in REF it won't think that's a valid code anymore and you'll have to fix it and re-insert the actual Field code REF followed by the correct arguments.
0
 

Author Comment

by:pubbuddy
ID: 37115961

Hey ocanada: One more time!


C:\Documents and Settings\Frank\My Documents\4 Double Sided Post Cards\Mail Merge\ocanada

Thaks for your sticktoitiveness!

Frank
0
 

Author Comment

by:pubbuddy
ID: 37118181
This for ocanada:

I'm coming to understand some of this but one thing keeps getting in the way. It has been stated a couple of times that it would be preferrable to have four columns. I thought that the column headings in the DataSource determines that. I have six, conservatively. How are the other two handled?
I can get four rows with six columns of info which define one post card, front & back. So four rows would make a double sided sheet with four post cards.

Thanks, Frank




Thanks, Frank
0
 
LVL 11

Accepted Solution

by:
ocanada_techguy earned 1000 total points
ID: 37119790
There are two posts to answer.
First of all, you have not shared any files with anybody.  In your post 11/07/11 06:35 AM you have two links that are no use to us, and in 11/10/11 10:05 AM there is similarly the pathname where the file is located on your hard disk.  We don't have access to your hard disk Frank.

Think of it this way, if you sent a letter to someone, and you wrote, please find enclosed the documents, and then inside the letter there was a post-it-note that says, find the document you need in my filing cabinet under the xyz folder.  How is the recepient of the letter supposed to open your filing cabinet from clear across town?  See the paperclip icon and the word File just under the reply box? That is where you upload a file with your reply.

*shakes head*
Frank, Frank, Frank, what are we going to do?  I don't wish to offend, but you seem to be in beyond your depth, and it shows throughout this thread of posts.  You need to hire someone who knows just a little bit more about computers to help you in these cases.  Everything you need to know -- a person who knows how to mail merge needs to know -- to accomplish your task, has been explained in this set of posts above. Explained several times.  But your replies miss the mark in fairly fundamental ways every time.  You seem to want us to do your job for you, okay, please also send your pay. *grin

Um, I'll cushion the blow here with a bit of comizerating, but I worry this is going to seem condacending.  It's like math, to do high school math it is taken for granted you can do elementary school math, and to do more advanced computer stuff it becomes very frsutrating when there is fundamental stuff you don't know how to do.  But hey, if you never needed to know this stuff, or particle physics, or advanced biology, or whatever subject, if you've never had need to use it you can quite comfortably go through life without ever needing to know eh.  *nod

This tricky card thing is sort of approaching "college level" Word techniques.  I hope by now you know some "high-school" Word techniques, such as what are MailMerge fields, how do you embed them in a document (hint: Insert Field), how do you view them hiding within the body of a document (hint: Alt-F9 aka show fields, they are grey with curly braces or angle brackets), we're covering the "high school" level so we can do the "college-level" advanced class here.

So, w.r.t. second post follow-up question, I am sorry there has been some discussion that there are two alternative approaches to this whole problem of multiple mail merges on a single document, since, discussing the different ways has added to the confusion, and it seems it is too confusing already.   There is no way that thing should keep "getting in the way" since it is a different solution.  The three approaches would be a) code the mail merge document so it reads four rows of data from the mailing list at-a-time and then places ("ref"erences) those four sets of data on the document, repeat. b) change the mailing list so there are four sets of names and addresses on each row, that way the mail merge can read four cards worth of names from just one row, repeat c) (there is a third way, where the document is one postcard not four and the list is one name per row not four, but not all printers can print on post-card size much less double-side them)

Your follow-up question you're letting the "number of columns" confuse you.  Wether the mailing list has five columns to each name, or six, or whatever, the point was to "quadruple" it by putting the next three records worth into additional columns to the right (in Excel most likely) so that one row is actually four rows worth of names and addresses.  Instead of quadruple one might say "have the list in four columns", as in four columns of columns.  When an army marches in four columns there's actually quite a few rows and columns of soldiers in each larger "column".  Get it?

If you go back to my last post and consider it carefully, you should be able to make a document with those codes in it, leave out the ** comments, position all the { REF where they need to go, and that should do it.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you work with Word a lot, you probably use styles. If you use styles a lot, you've probably balled your fist more often than not when working with the ribbon. In Word 2007/2010, one of the things that I find missing when using styles is a quic…
The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question