Creating a Group From a File of E-mail Addresses

A

Alan Schaevitz

I would think there is a simple way of doing this but I'm not bright enough
to see it. I have an Excel spreadsheet of members of my homeowners
association. One of the columns is their e-mail address. Is there a way to
create a group list in Entourage from this data in the Excel spreadsheet
(copy and paste, AppleScript, etc.)? Even better, of course, would be a
link between the Entourage group and the Excel column so that any updates to
that column would automatically update the Entourage group, too.
 
N

Narendra

Hi Alan
Excel data can be exported to tab delimited file and then imported to
Entourage using the standard import wizard found on the File menu in
Entourage.

Entourage provides the ability to map fields prior to performing the import.
Some users find it
beneficial to open the exported file in Excel in order to clean up the data
prior to importing. If you chose to do this, be sure to save the file as
tab-delimited text since Entourage will not import an Excel file.

Once you have successfully imported the data you can create the group

I dont think there is way by which you can link Excel column with the group

Naren
 
N

Narendra

Hi!!
try to go through the steps listed earlier by me and let me know about the
status,

Thanks
Naren
 
P

Paul Berkowitz

That method forces you to make full contacts of each entry (Excel row)
before making them into a group. This may be what you want, because you can
get all sorts of information from the various Excel columns, not just email
addresses, of course.

Sometimes people want only the email addresses (and maybe names) as simple
group members, not to make every entry a full contact in the Address Book.
The way to do that is this:

1. Select and Copy the email address column in Excel.
2. Make a new group in Entourage. (Remove top item if any is there already
from a selection in the Address Book..)
3. Paste and Save.

Voilà! There's your group. If the top item was a header, remove it from the
group.

--
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.
 
A

Alan Schaevitz

Paul,

I should have realized a simple copy/paste approach would work to create a
new group. Now let me add a wrinkle. The Excel spreadsheet 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 spreadsheet 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
spreadsheet. 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?

Alan
 
P

Paul Berkowitz

Paul,

I should have realized a simple copy/paste approach would work to create a
new group.
Click to expand...

Not necessarily. I expected myself that I'd at least have to copy the column
to a new sheet, save the sheet 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 spreadsheet 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 spreadsheet 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
spreadsheet. 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?
Click to expand...

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 worksheet, 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 sheet)
set theColumn to range ("$F1:$F" & r) of active sheet
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.
 
A

Alan Schaevitz

There seems to have been a glitch in your posting, Paul. The first script
is missing. Also, not being well versed in scripting, are there parts of
the second script that is in the posting that are meant to be replaced (the
stuff in parentheses, for example)? Sorry to be so obtuse.
 
P

Paul Berkowitz

There seems to have been a glitch in your posting, Paul. The first script
is missing. Also, not being well versed in scripting, are there parts of
the second script that is in the posting that are meant to be replaced (the
stuff in parentheses, for example)? Sorry to be so obtuse.
Click to expand...

No glitch. Why were you expecting two scripts? That's it.

All you need to replace is the column letter if it isn't column F. If the
column is column C then you'd replace

("$F1:$F" & r)

by

("$C1:$C" & r)

As I said you probably won't want to alter your original second column which
has the spouses on the same row as the espoused. Instead, copy and paste
that second column into another worksheet or into a third column, and use
that column - whatever its letter - as the one whose letter you put into the
script. If you don't want to edit the script, use column F.

--
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.
 
A

Alan Schaevitz

Thanks, Paul. The script works like a charm. I modified it by duplicating
the commands so that it did both columns in the same script. Then I just
copy and paste each column into a new group in Entourage. Thanks again for
your help.
 
Top