Skip navigation
This discussion is archived

Mail Merge and Excel

1749 Views 5 Replies Latest reply: May 24, 2006 7:18 PM by Geoffrey Lacy RSS
Jose Duarte Calculating status...
Currently Being Moderated
May 20, 2006 9:47 AM
Is there a way to use an Excel table for Mail Merge in a way similar to Word? The hassle I have with using address book as a middle-man is that in Spanish, the salutation is gender sensitive, and so I can't easily add that into Address Book.
iBook G4, Mac OS X (10.4.2)
  • Peggy Level 8 Level 8 (38,085 points)
    Currently Being Moderated
    May 20, 2006 11:07 AM (in response to Jose Duarte)
    I'm going to make a guess that you are wanting to use an Excel document to mail merge in Pages since this is the Pages forum. The answer is no, Pages 2 only does mail merge with Address Book.

    G5 2.3GHz/3GB; Mac mini 1.25/512 MB & 15 G4 Al PowerBook 1.25/1GB; 30GB 5G iPod, Mac OS X (10.4.6), G3/600 iBook, G4/867, G4/400, Apple IIgs, PB 1400, iPod Shuffle, 60GB iPod photo
  • Kurt Weber Level 6 Level 6 (13,945 points)
    Currently Being Moderated
    May 22, 2006 7:34 PM (in response to Jose Duarte)
    Appleworks is much better for the mail merge since it can make use of its own databases and insert any field you wish. It would be easy to create a pulldown menu with the salutation and have that inserted into the page correctly.

    Kurt
    iMac 500 mhz, 9.2.2; iBook G4;, Mac OS X (10.3.9), Firewire ext. HD
  • Geoffrey Lacy Calculating status...
    Currently Being Moderated
    May 23, 2006 7:10 AM (in response to Jose Duarte)
    I have similar situation (only French), and I hated that Mail Merge wasn't smart enough to stick in a Home address if the Work address was blank, and vice versa.

    and a solution...

    e.g.
    1. I wanted to drop ANY contacts on the same template without sorting Work addressees and Home addressees before the Mail merge. I wanted the template to accept any valid address - whichever one was not empty, and with a preference toward Work or Home that I choose.

    2. I wanted to have my contacts in my address book listed normally as "Mr." or "Mrs." Yet during the holidays and anniversaries I want to use "Mr. & Mrs."

    First, I added a tag in the Notes field of my address book like; <altPrefix>.
    Then, I added every addressbook field onto my template.
    Finally, I constructed an applescript to do items 1 and 2 above.

    Once I do a mail merge, I have all of the address data including an <altPrefix> on my template. I run my "Smart Merge" applescript to rebuild the address to my liking with the option of an <altPrefix>.

    You could include formatting the address for a different country code and a postal bar code if you wish (I intend to add these to my applescript as well). You could add other tags to addressbook notes, though I wouldn't go overboard.

    You could alse, of course, import tab/comma delimited files through applescript as well. I think I'll wait for Apple's next release of Pages to see if it has a more robust merge data features.

    applescript code for "Smart Merge"
    -------------------------------------
    (* SMART MERGE - Merges contacts whether Home or Work
    and use an <altPrefix> tag in AddressBook Notes.

    Directions for adding AddressBook links to a template:
    1. Add text boxes, described below as _indexFirst etc., to your Pages.app template;
    2. Type a descriptive name such as 'Firstname' or 'Lastname' in the text box
    3. Select the text ie 'Firstname'
    4. To make 'Firstname' an address book link field;
    Click on the link inspector (the arrow icon left of Quicktime in Inspector pallete)
    Check 'Enable as an Address Book field'
    Select the pop-up's similar to your text box description ie. Name;First
    5. Continue adding text boxes and AB links for each item below (for this script)
    It's not easy to keep track of the text box index values.
    You have to enter the text boxes in order with no mistakes.
    If you make a mistake, don't delete, use undo instead.
    Otherwise the index value order will be different.
    Change the Index values below to fit your template.
    6. Save your template

    Directions to run this script:
    1. Load you template
    2. Open addressbook and drag/drop your contacts; Choose "in new document"
    3. Run this apple script.
    A. Select your preference for Home addresses or Work addresses
    B. Select your preference for Prefix in AB, or altPrefix in Notes.

    If you don't know what applescript is, or how to run from the menubar,
    do some google searches.
    ____________________________________________________________________________
    Copyright © 2006 Geoffrey C. Lacy

    You may incorporate this code into your program(s) without
    restriction. This code has been provided "AS IS" and the
    responsibility for its operation is yours.

    You are not permitted to redistribute this code as your own after having made changes.
    You are not permitted to profit in any way from this code, even after having made changes.

    If you're going to redistribute the code, you are required
    that you make it clear that the code was descended, and originated,
    from Geoffrey C. Lacy, but that you've made changes.
    ____________________________________________________________________________

    *)

    set _indexPrefix to 1
    set _indexFirst to 2
    set _indexLast to 3
    set _indexWorkStreet to 4
    set _indexWorkCity to 5
    set _indexWorkState to 6
    set _indexWorkZIP to 7
    set _indexHomeStreet to 8
    set _indexhomeCity to 9
    set _indexHomeState to 10
    set _indexHomeZIP to 11
    set _indexNote to 12
    set _indexMiddle to 13
    set _badAddress to 0

    set no_error to true
    try
    tell application "Pages"
    set _pageCount to (count each section of document 1)
    end tell

    on error
    display dialog "You must have an open document in Pages."
    set no_error to false
    end try

    if no_error then

    display dialog "(merge " & _pageCount & " addressees...)" & return & return & "Smart Merge: Merge Home or Work address?" & return & " If empty, the other is used." buttons {"Cancel", "Prefer Work", "Prefer Home"} default button "Prefer Home"
    copy the result as list to {button_pressed1}

    display dialog "Smart Merge: Use '<altPrefix>' in Notes?" buttons {"Cancel", "ABPrefix", "altPrefix"} default button "altPrefix"
    copy the result as list to {button_pressed2}
    -- Get "note" text with a delimiter, I use this to get 'Mr. and Mrs.' prefix for holiday cards instead of the 'Mr.' only, in my contact list.


    tell application "Pages"

    if button_pressed1 is not "Cancel" then

    repeat with _pc from 1 to _pageCount

    -- Get name items
    -- ------------------------------------------------------------------------------- ------------------

    if button_pressed2 is "altPrefix" then
    set _prefix to my getNoteText(get object text of text box _indexNote of page _pc of document 1, "<altPrefix>", object text of text box _indexPrefix of section _pc of document 1)
    else
    set _prefix to object text of text box _indexPrefix of page _pc of document 1

    end if
    -- ------------------------------------------------------------------------------- ------------------
    set _first to object text of text box _indexFirst of page _pc of document 1
    set _mi to object text of text box _indexMiddle of page _pc of document 1
    set _last to object text of text box _indexLast of page _pc of document 1

    --Build name portion of label
    set myLabel to ""
    if _prefix is not equal to "" then
    set myLabel to _prefix & " "
    end if

    if _first is not equal to "" then
    set myLabel to myLabel & _first & " "
    end if

    if _mi is not equal to "" then
    if _mi is not equal to "Middle" then
    set myLabel to myLabel & _mi & " "
    end if
    end if

    if _last is not equal to "" then
    set myLabel to myLabel & _last & return
    end if
    -- ------------------------------------------------------------------------------- ------------------
    -- Consider Address case
    --set cc to object text of text box _indexHomeStreet of page _pc of document 1
    --display dialog cc
    if object text of text box _indexHomeStreet of page _pc of document 1 is equal to "" then
    set _isaHomeAddress to "0"
    else
    set _isaHomeAddress to "1"
    end if

    if object text of text box _indexWorkStreet of page _pc of document 1 is equal to "" then
    set _isaWorkAddress to "0"
    else
    set _isaWorkAddress to "1"
    end if

    set _validHomeWorkAddress to _isaHomeAddress & _isaWorkAddress

    if button_pressed1 is "Prefer Home" then
    set _preferredHome to "1"
    else
    set _preferredHome to "0"
    end if

    set _addressCase to _preferredHome & _validHomeWorkAddress
    -- _addressCase choices are;
    -- 000 bad address
    -- 001 format work
    -- 010 format home
    -- 011 format work
    -- 100 bad address
    -- 101 format work
    -- 110 format home
    -- 111 format home

    if (_addressCase = "010") or (_addressCase = "110") or (_addressCase = "111") then
    -- Home address
    set _street to object text of text box _indexHomeStreet of page _pc of document 1
    set _city to object text of text box _indexhomeCity of page _pc of document 1
    set _state to object text of text box _indexHomeState of page _pc of document 1
    set _zip to object text of text box _indexHomeZIP of page _pc of document 1
    end if
    if (_addressCase = "001") or (_addressCase = "011") or (_addressCase = "101") then
    -- Work Address
    set _street to object text of text box _indexWorkStreet of page _pc of document 1
    set _city to object text of text box _indexWorkCity of page _pc of document 1
    set _state to object text of text box _indexWorkState of page _pc of document 1
    set _zip to object text of text box _indexWorkZIP of page _pc of document 1
    end if

    if (_addressCase = "000") or (_addressCase = "100") then
    _badAddress = _badAddress + 1
    end if

    --Build address label
    if _street is not equal to "" then
    set myLabel to myLabel & _street & return
    end if

    if _city is not equal to "" then
    if _city is not equal to "Middle" then
    set myLabel to myLabel & _city & ", "
    end if
    end if

    if _state is not equal to "" then
    set myLabel to myLabel & _state & " "
    end if

    if _zip is not equal to "" then
    set myLabel to myLabel & _zip
    end if

    set body text of section _pc of document 1 to myLabel

    --Delete Text Boxes; ruins script if you run it 2x in a row.
    --delete text boxes 1 thru 13 of section _pc of document 1

    set _pc to _pc + 1
    end repeat
    end if
    end tell
    end if
    --Get any delimited text in AddressBook Notes
    on getNoteText(txtStr, myDelim, saved)
    set OldDelims to AppleScript's text item delimiters
    set AppleScript's text item delimiters to myDelim
    set noteText to text items of txtStr
    set AppleScript's text item delimiters to OldDelims
    set itemCount to count items of noteText
    if itemCount > 1 then
    return item 2 of noteText
    else
    return saved
    end if
    set saved to ""
    end getNoteText

    -------------------------------------------------------

    - creative huh?
    -GCL
    G4 powerbook, Mac OS X (10.4.6)
  • Kurt Weber Level 6 Level 6 (13,945 points)
    Currently Being Moderated
    May 24, 2006 1:16 PM (in response to Geoffrey Lacy)
    More than a little impressive Man, I gotta learn Applescript!

    Much of these problems will be solved if Address Book would allow some fields. Grrr!

    To me, Palm Desktop is still the king of free contact managers: Dirt easy to set up, and chock full of features despite the age of the program. I absolutely love the idea of a universal address book but In reality Address Book's limitations cripple it. I find myself having to use Palm and Address Book on the same machine.
    iMac 500 mhz, 9.2.2; iBook G4;, Mac OS X (10.3.9), Firewire ext. HD
  • Geoffrey Lacy Level 1 Level 1 (25 points)
    Currently Being Moderated
    May 24, 2006 7:18 PM (in response to Kurt Weber)
    Thanks for the feedback!

    This script saves my _ when printing xmas envelopes, event invites and holiday letters. After all, it's "Mrs. Smith" in my AddressBook, but it's "Mr. & Mrs. Smith" for any events.

    In my actual script, I also have a <2ndAddresee> Note tag so that I can print "Mr. Randy Lopez" on line 1 and "Dr. Marie Lopez" on line 2, so as to follow the correct form of address (You can't put 'Mr. and Dr. Randy Lopez' - it just doesn't work any other way).

    Note: You can turn the text white in the Pages template so the construction parts of the address don't print, or you can uncomment the 'delete' line in the applescript (search for 'delete') to delete the text boxes before printing.

    Also, I opened the Metrics palette in the inspector, selected all the address link fields I created in my template, then changed the size and width to .05 inches and x,y to (.25, .25). Now I can easily drop this little patch of address links in any of my templates for the same effect. Uh, don't group them, I think I have to change the applescript for that.

    I highly recommend learning applescript. When a program doesn't do what I want it to –Applescript to the rescue! It's relatively fast concept to development.

    Yeah, you're right, AddressBook is far from a business class contact manager. It has the underpinnings of a powerful contact manager - but then chokes on the interface and inter-application communication.

    But applescript addresses most of my nags. Sadly, I don't think Palm Desktop is inherently scriptable (Now, where did I put that free postal bar-code font so I can enhance my script!?)

    -GCL
    G4 powerbook, Mac OS X (10.4.6)

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.