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

  • Thread starter Thread starter Ryan P
  • Start date Start date
R

Ryan P

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:

(e-mail address removed); (e-mail address removed)

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

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
 
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 said:
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 said:
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:

(e-mail address removed); (e-mail address removed)

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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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:

(e-mail address removed); (e-mail address removed)

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?
Click to expand...
Click to expand...
 
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 said:
Ryan
Click to expand...
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.
Click to expand...
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
Click to expand...

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...

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
 
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 said:
Ryan
Click to expand...
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.
Click to expand...
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
Click to expand...

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...

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 said:
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 said:
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 said:
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:

(e-mail address removed); (e-mail address removed)

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?
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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:

(e-mail address removed); (e-mail address removed)

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?
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...

That's what I was thinking... cleaning up the mess is far more
difficult than not making it in the first place...
 
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
Click to expand...

That's what I was thinking... cleaning up the mess is far more
difficult than not making it in the first place...
 
In message <[email protected]>, Ryan P
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.
Click to expand...

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?
Click to expand...

The above fix might be enough but the extra table solution is a much
cleaner way to fix it.
 
In message <[email protected]>, Ryan P
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.
Click to expand...

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?
Click to expand...

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