One field, multiple e-mail addresses - mail merge?

Hi,

I'm designing a database to keep track of my agency's volunteers - we were
using an outdated Excel spreadsheet before and a hack-job of a Word document
to store email addresses.

I've got pretty much all of the work done on form design and everything else
but now I'm looking to maximize the usefulness of the data. I need to be
able to do occasional mass-electronic mailing from queries but the problem
I'm facing is having one field for e-mail addresses. A small number of
volunteers has listed multiple email addresses in their information, and
currently these multiple addresses are entered in the format:

;

When I go to do a mail-merge in Word 2003, it tells me Outlook does not
recognize this format and skips over these records. I tried formating the
addresses with comas separating them, but Word tells me to replace the comas
with a semicolon.

Is there any way I can do this, or do I have to go through and create extra
fields for extra e-mail addresses?

Ryan

If you "create extra fields for extra email addresses", you might as well
keep using Excel! Because you will never know how many, you face
interminable maintenance using this 'spreadsheetly' approach in Access.

If the data in your situation is related one-to-many (one person has zero,
one, or many email addresses), then consider using another table that holds
valid pairs of [Person] & [EmailAddress]. That way, a person might have
zero, one or many records, reflecting having zero, one or many email
addresses.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Ryan P" Ryan wrote in message
...
Hi,

I'm designing a database to keep track of my agency's volunteers - we were
using an outdated Excel spreadsheet before and a hack-job of a Word
document
to store email addresses.

I've got pretty much all of the work done on form design and everything
else
but now I'm looking to maximize the usefulness of the data. I need to be
able to do occasional mass-electronic mailing from queries but the problem
I'm facing is having one field for e-mail addresses. A small number of
volunteers has listed multiple email addresses in their information, and
currently these multiple addresses are entered in the format:

;

When I go to do a mail-merge in Word 2003, it tells me Outlook does not
recognize this format and skips over these records. I tried formating the
addresses with comas separating them, but Word tells me to replace the
comas
with a semicolon.

Is there any way I can do this, or do I have to go through and create
extra
fields for extra e-mail addresses?

Thanks Jeff,

The extra table seems like it might be a good idea to persue.

I'd like to know though, have I hit a limitation in the Access/Outlook
software or am I just doing something wrong?

-Ryan

"Jeff Boyce" wrote:

Ryan

If you "create extra fields for extra email addresses", you might as well
keep using Excel! Because you will never know how many, you face
interminable maintenance using this 'spreadsheetly' approach in Access.

If the data in your situation is related one-to-many (one person has zero,
one, or many email addresses), then consider using another table that holds
valid pairs of [Person] & [EmailAddress]. That way, a person might have
zero, one or many records, reflecting having zero, one or many email
addresses.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Ryan P" Ryan wrote in message
...
Hi,

I'm designing a database to keep track of my agency's volunteers - we were
using an outdated Excel spreadsheet before and a hack-job of a Word
document
to store email addresses.

I've got pretty much all of the work done on form design and everything
else
but now I'm looking to maximize the usefulness of the data. I need to be
able to do occasional mass-electronic mailing from queries but the problem
I'm facing is having one field for e-mail addresses. A small number of
volunteers has listed multiple email addresses in their information, and
currently these multiple addresses are entered in the format:

;

When I go to do a mail-merge in Word 2003, it tells me Outlook does not
recognize this format and skips over these records. I tried formating the
addresses with comas separating them, but Word tells me to replace the
comas
with a semicolon.

Is there any way I can do this, or do I have to go through and create
extra
fields for extra e-mail addresses?

On May 26, 12:04*pm, Ryan P wrote:
Thanks Jeff,

The extra table seems like it might be a good idea to persue.

I'd like to know though, have I hit a limitation in the Access/Outlook
software or am I just doing something wrong?

-Ryan

"Jeff Boyce" wrote:
Ryan

If you "create extra fields for extra email addresses", you might as well
keep using Excel! *Because you will never know how many, you face
interminable maintenance using this 'spreadsheetly' approach in Access.

If the data in your situation is related one-to-many (one person has zero,
one, or many email addresses), then consider using another table that holds
valid pairs of [Person] & [EmailAddress]. *That way, a person might have
zero, one or many records, reflecting having zero, one or many email
addresses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ryan P" Ryan wrote in message
...
Hi,

I'm designing a database to keep track of my agency's volunteers - we were
using an outdated Excel spreadsheet before and a hack-job of a Word
document
to store email addresses.

I've got pretty much all of the work done on form design and everything
else
but now I'm looking to maximize the usefulness of the data. *I need to be
able to do occasional mass-electronic mailing from queries but the problem
I'm facing is having one field for e-mail addresses. *A small number of
volunteers has listed multiple email addresses in their information, and
currently these multiple addresses are entered in the format:

;

When I go to do a mail-merge in Word 2003, it tells me Outlook does not
recognize this format and skips over these records. *I tried formating the
addresses with comas separating them, but Word tells me to replace the
comas
with a semicolon.

Is there any way I can do this, or do I have to go through and create
extra
fields for extra e-mail addresses?

Create a second table for EMail addresses and include the unique ID
for the Person. Then you can choose one as primary and send to it, or
send to all of them. You can use SPLIT to break the e-mail addresses
into separate values and then append them to your EMail Table...

Person---(1,M)----EMailAddress

CREATE TABLE Person(
PersonID INT IDENTITY,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
REMail varchar(255),
PRIMARY KEY (PersonID));

CREATE TABLE EMailAddress(
ePersonID INT,
Email VARCHAR(30) NOT NULL,
PRIMARY KEY (ePersonID, Email),
FOREIGN KEY ePersonID REFERENCES Person(PersonID));

to do the split is a bit more complicated...

You would have to put this in a new code module, compile and run

Public Sub SplitOutEmails()
Dim rsStudent As DAO.Recordset
Dim rsEMails As DAO.Recordset

Dim varEMails As Variant
Dim intCounter As Integer 'to loop through variant array of e-
mails.


Set rsStudent = DBEngine(0)(0).OpenRecordset("Student",
dbOpenTable, dbReadOnly)
Set rsEMails = DBEngine(0)(0).OpenRecordset("EMails", dbOpenTable,
dbAppendOnly)

Do Until rsStudent.EOF
If Len(rsStudent.Fields("EMailAddresses")) 0 Then
varEMails = Split(rsStudent.Fields("EMailAddresses"), ";")
For intCounter = LBound(varEMails) To UBound(varEMails)
With rsEMails
.AddNew
.Fields("StudentID") = rsStudent.Fields
("StudentID")
.Fields("EMailAddress") = varEMails(intCounter)
.Update
End With
Next intCounter
End If
rsStudent.MoveNext

Loop
MsgBox "done"
End Sub

I think what you may have hit is the kind of a speed bump you create for
yourself if you try to force Access to behave like Excel.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ryan P" wrote in message
...
Thanks Jeff,

The extra table seems like it might be a good idea to persue.

I'd like to know though, have I hit a limitation in the Access/Outlook
software or am I just doing something wrong?

-Ryan

"Jeff Boyce" wrote:

Ryan

If you "create extra fields for extra email addresses", you might as well
keep using Excel! Because you will never know how many, you face
interminable maintenance using this 'spreadsheetly' approach in Access.

If the data in your situation is related one-to-many (one person has
zero,
one, or many email addresses), then consider using another table that
holds
valid pairs of [Person] & [EmailAddress]. That way, a person might have
zero, one or many records, reflecting having zero, one or many email
addresses.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Ryan P" Ryan wrote in message
...
Hi,

I'm designing a database to keep track of my agency's volunteers - we
were
using an outdated Excel spreadsheet before and a hack-job of a Word
document
to store email addresses.

I've got pretty much all of the work done on form design and everything
else
but now I'm looking to maximize the usefulness of the data. I need to
be
able to do occasional mass-electronic mailing from queries but the
problem
I'm facing is having one field for e-mail addresses. A small number of
volunteers has listed multiple email addresses in their information,
and
currently these multiple addresses are entered in the format:

;

When I go to do a mail-merge in Word 2003, it tells me Outlook does not
recognize this format and skips over these records. I tried formating
the
addresses with comas separating them, but Word tells me to replace the
comas
with a semicolon.

Is there any way I can do this, or do I have to go through and create
extra
fields for extra e-mail addresses?

On May 26, 4:05*pm, "Jeff Boyce" wrote:
I think what you may have hit is the kind of a speed bump you create for
yourself if you try to force Access to behave like Excel.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

That's what I was thinking... cleaning up the mess is far more
difficult than not making it in the first place...

In message , Ryan P
writes



When I go to do a mail-merge in Word 2003, it tells me Outlook does not
recognize this format and skips over these records. I tried formating the
addresses with comas separating them, but Word tells me to replace the comas
with a semicolon.

Ignore it. This is a data field not prose. Turn off spelling and grammar
checking.


Is there any way I can do this, or do I have to go through and create extra
fields for extra e-mail addresses?

The above fix might be enough but the extra table solution is a much
cleaner way to fix it.



--
Bernard Peek