Re: Creating a Group From a File of E-mail Addresses

From: Paul Berkowitz (berkowit_at_spoof_silcom.com)
Date: 08/20/04 

Date: Thu, 19 Aug 2004 23:23:14 -0700

On 8/19/04 5:54 PM, in article BD4A99D9.249E%spam@comcast.net, "Alan
Schaevitz" <spam@comcast.net> wrote:

> Paul,
>
> I should have realized a simple copy/paste approach would work to create a
> new group.

Not necessarily. I expected myself that I'd at least have to copy the column
to a new ***, save the *** as tab-delimited text, and open the txt file
in a text editor. Even then, I didn't fully expect copy and paste to work, I
thought it might take a repeat loop by script But no - it's got that really
cool implementation not only from a text file but also from Excel directly
(that's probably an Excel/clipboard implementation, not Entourage's). These
things don't happen by accident - someone has been very helpful here.

> Now let me add a wrinkle. The Excel spread*** has two columns
> for e-mail addresses (association members often have a different e-mail for
> husband and wife). No problem. Just copy the second column and paste it at
> the end of the group. However, often the second column entry is blank (they
> only have one e-mail address). Occasionally, both entries are blank (family
> doesn't have Internet).
>
> If I were creating the group once, it would be easy to just go down the list
> of entries after creating the group and deleting the blank ones. However,
> the spread*** is frequently updated by our secretary (I am the webmaster
> and handle group e-mails for the association) and I get a new copy of the
> spread***. I don't know what has changed, so the easiest way to update
> the group is to totally re-create it. Now I have to again go down and
> delete all the blank entries in the group. This gets tedious if done
> frequently.
>
> So, for the challenge. Could a script be created to take care of this
> tedium?

Not in Entourage. You'd have to save the group first, and when you save a
group with blank entries it gets truncated at the first one. But in Excel,
yes.

This is the script for Excel 2004. It would be different in X.

here's it's acting on the second column only, which I'm imagining is column
F. Edit as needed. What you'll want to do is copy and paste your real 2nd
column into a 3rd column, or into another work***, and use that pasted
column. (Or you could copy and paste it to the bottom of the first column
and run the script on the combined first column, which would be a little
slower.)

tell application "Microsoft Excel"
    set r to count (rows of used range of active ***)
    set theColumn to range ("$F1:$F" & r) of active ***
    repeat with i from r to 1 by -1
        set cellValue to value of cell i of theColumn
        if cellValue = "" then
            delete range (cell i of theColumn) shift shift up
        end if
    end repeat
end tell

Due to a bug in the scripting, you can't use a variable for 'cell i of the
Column' in either case. 

-- 
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>
Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.
PLEASE always state which version of Microsoft Office you are using -
**2004**, X  or 2001. It's often impossible to answer your questions
otherwise.