I am attempting to create an email merge that will send a memo to each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and includes a
column with the manager's name.

I need to create a single email for each manager that includes a table with
all staff currently reporting to them (name, employee number, training
location, date of training). There is a column for each of these values in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date
------------ ------------ -------------------- ---------- -----
Joe Smith 1234 Vanc May 15
Fred Brown 2345 Tor May 18

I thought I was on the right track when I found Cindy's explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the creation of
Customer Invoices, but after spending quite a bit of time looking at the
example, it still wasn't clear to me how to make this work with an Excel
spreadsheet.

Please help?

Word isn't very good at this kind of thing. You may be able to use the
DATABASE field approach described on Cindy Meister's pages at

http://homepage.swissonline.ch/cindymeister/

- see Mailmerge FAQ, Special Merges, Multiple Items per condition.

Not sure whether that will work with a merge to e-mail.

Peter Jamieson

"KarenB" wrote in message
...
I am attempting to create an email merge that will send a memo to each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and includes a
column with the manager's name.

I need to create a single email for each manager that includes a table
with
all staff currently reporting to them (name, employee number, training
location, date of training). There is a column for each of these values
in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date
------------ ------------ -------------------- ---------- -----
Joe Smith 1234 Vanc May 15
Fred Brown 2345 Tor May 18

I thought I was on the right track when I found Cindy's
explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the creation of
Customer Invoices, but after spending quite a bit of time looking at the
example, it still wasn't clear to me how to make this work with an Excel
spreadsheet.

Please help?

As I indicated in my original post, I have already looked at Cindy's page,
but her instructions don't really articulate where to put that code, and what
the difference is between Access and Excel.

"Peter Jamieson" wrote:

Word isn't very good at this kind of thing. You may be able to use the
DATABASE field approach described on Cindy Meister's pages at

http://homepage.swissonline.ch/cindymeister/

- see Mailmerge FAQ, Special Merges, Multiple Items per condition.

Not sure whether that will work with a merge to e-mail.

Peter Jamieson

"KarenB" wrote in message
...
I am attempting to create an email merge that will send a memo to each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and includes a
column with the manager's name.

I need to create a single email for each manager that includes a table
with
all staff currently reporting to them (name, employee number, training
location, date of training). There is a column for each of these values
in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date
------------ ------------ -------------------- ---------- -----
Joe Smith 1234 Vanc May 15
Fred Brown 2345 Tor May 18

I thought I was on the right track when I found Cindy's
explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the creation of
Customer Invoices, but after spending quite a bit of time looking at the
example, it still wasn't clear to me how to make this work with an Excel
spreadsheet.

Please help?

Sorry, missed the last bit of your message.

The basic approach is the same, but the text inside the DATABASE field has
to change, and precisely how depends on your version of Word and what is in
your Excel sheet.

Let's suppose your Excel Sheet is called emp.xls and has columns with the
following names:

Employee Number
First Name
Last Name
Location
Date
ManagerName

then, unlike the sample you are following which uses a table or queryfor the
data source for the merge, and a query inside a database field to provide
the invoice details, you are using the same worksheet as the data source for
the merge and to provide the details for each managername. Suppose you have
a very simple case with 5 records:

Employee Number,First Name,Last Name,Location,Date,ManagerName
1234,Dick,Black,ABC,01 Jan 2006,ManagerA
2345,Maisie,Scarlett,BCD,01 Jan 2006,ManagerB
3456,Colin,Mustard,CDE,15 Jan 2006,ManagerA
4567,Prabaker,Plum,ABC,01 Jan 2006,ManagerA
5678,Raven,Green,BCD,10 Jan 2006,ManagerB

Then you cannot use the sheet directly as the data source for the merge
because you will get the details for ManagerA 3 times and the details for
ManagerB twice. To get around that, you can either
a. create a separate sheet (or another type of data source altogether)
containing a column called "ManagerName" and with one row for each manager,
and use that as the data source for the merge (NB, it is advisable to have
at least 2 columns in any file you want to use as a data source) or
b. open the data source using a VBA OpenDataSource method call, using SQL
to specify that you only want one record per manager. The SQL would be
something like

SELECT DISTINCT [s1].[ManagerName] FROM [Sheet1$] [s1]

If you can't make option (b) work at first, I suggest you simplify things at
least for test purposes by using (a).

Once you have created the data source you need, the DATABASE field would
look something like this:

{ DATABASE \d "C:\\mydata\\emp.xls"
\s "SELECT [s1].[First Name],[s1].[Last Name],[s1].[Employee
Number],[s1].[Location],[s1].[Date] FROM [Sheet1$] [s1] WHERE
[s1].[ManagerName] = '{ MERGEFIELD "ManagerName" }'" \h }

This should work in Word 2003 (and possibly 2002) because the default method
for opening an Excel sheet as a data source is OLEDB and there seems to be
no need to use a \c switch to define a connection string.

See how far you get with that and let us know where you run into problems.

Peter Jamieson

"KarenB" wrote in message
...
As I indicated in my original post, I have already looked at Cindy's page,
but her instructions don't really articulate where to put that code, and
what
the difference is between Access and Excel.

"Peter Jamieson" wrote:

Word isn't very good at this kind of thing. You may be able to use the
DATABASE field approach described on Cindy Meister's pages at

http://homepage.swissonline.ch/cindymeister/

- see Mailmerge FAQ, Special Merges, Multiple Items per condition.

Not sure whether that will work with a merge to e-mail.

Peter Jamieson

"KarenB" wrote in message
...
I am attempting to create an email merge that will send a memo to each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and includes a
column with the manager's name.

I need to create a single email for each manager that includes a table
with
all staff currently reporting to them (name, employee number, training
location, date of training). There is a column for each of these
values
in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date
------------ ------------ -------------------- ---------- -----
Joe Smith 1234 Vanc May
15
Fred Brown 2345 Tor May
18

I thought I was on the right track when I found Cindy's
explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the creation
of
Customer Invoices, but after spending quite a bit of time looking at
the
example, it still wasn't clear to me how to make this work with an
Excel
spreadsheet.

Please help?

I have followed your instructions (for Option A), created a new worksheet for
a list of unique manager names (2 columns) and created the merge to one of
those columns (Manager). I then modified the Database field string as
follows:

{ DATABASE \d "D:\Current Projects\ProcureIT\User List Course Schedule.xls"
\s "SELECT [s1].[First],[s1].[Last],[s1].[EENum],[s1].[Location],[s1].[Date]
FROM [UserDetail$] [s1] WHERE [s1].[ManagerName] = '{ MERGEFIELD "Manager"
}'" \h }

Where, exactly, do I put that string?

"Peter Jamieson" wrote:

Sorry, missed the last bit of your message.

The basic approach is the same, but the text inside the DATABASE field has
to change, and precisely how depends on your version of Word and what is in
your Excel sheet.

Let's suppose your Excel Sheet is called emp.xls and has columns with the
following names:

Employee Number
First Name
Last Name
Location
Date
ManagerName

then, unlike the sample you are following which uses a table or queryfor the
data source for the merge, and a query inside a database field to provide
the invoice details, you are using the same worksheet as the data source for
the merge and to provide the details for each managername. Suppose you have
a very simple case with 5 records:

Employee Number,First Name,Last Name,Location,Date,ManagerName
1234,Dick,Black,ABC,01 Jan 2006,ManagerA
2345,Maisie,Scarlett,BCD,01 Jan 2006,ManagerB
3456,Colin,Mustard,CDE,15 Jan 2006,ManagerA
4567,Prabaker,Plum,ABC,01 Jan 2006,ManagerA
5678,Raven,Green,BCD,10 Jan 2006,ManagerB

Then you cannot use the sheet directly as the data source for the merge
because you will get the details for ManagerA 3 times and the details for
ManagerB twice. To get around that, you can either
a. create a separate sheet (or another type of data source altogether)
containing a column called "ManagerName" and with one row for each manager,
and use that as the data source for the merge (NB, it is advisable to have
at least 2 columns in any file you want to use as a data source) or
b. open the data source using a VBA OpenDataSource method call, using SQL
to specify that you only want one record per manager. The SQL would be
something like

SELECT DISTINCT [s1].[ManagerName] FROM [Sheet1$] [s1]

If you can't make option (b) work at first, I suggest you simplify things at
least for test purposes by using (a).

Once you have created the data source you need, the DATABASE field would
look something like this:

{ DATABASE \d "C:\\mydata\\emp.xls"
\s "SELECT [s1].[First Name],[s1].[Last Name],[s1].[Employee
Number],[s1].[Location],[s1].[Date] FROM [Sheet1$] [s1] WHERE
[s1].[ManagerName] = '{ MERGEFIELD "ManagerName" }'" \h }

This should work in Word 2003 (and possibly 2002) because the default method
for opening an Excel sheet as a data source is OLEDB and there seems to be
no need to use a \c switch to define a connection string.

See how far you get with that and let us know where you run into problems.

Peter Jamieson

"KarenB" wrote in message
...
As I indicated in my original post, I have already looked at Cindy's page,
but her instructions don't really articulate where to put that code, and
what
the difference is between Access and Excel.

"Peter Jamieson" wrote:

Word isn't very good at this kind of thing. You may be able to use the
DATABASE field approach described on Cindy Meister's pages at

http://homepage.swissonline.ch/cindymeister/

- see Mailmerge FAQ, Special Merges, Multiple Items per condition.

Not sure whether that will work with a merge to e-mail.

Peter Jamieson

"KarenB" wrote in message
...
I am attempting to create an email merge that will send a memo to each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and includes a
column with the manager's name.

I need to create a single email for each manager that includes a table
with
all staff currently reporting to them (name, employee number, training
location, date of training). There is a column for each of these
values
in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date
------------ ------------ -------------------- ---------- -----
Joe Smith 1234 Vanc May
15
Fred Brown 2345 Tor May
18

I thought I was on the right track when I found Cindy's
explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the creation
of
Customer Invoices, but after spending quite a bit of time looking at
the
example, it still wasn't clear to me how to make this work with an
Excel
spreadsheet.

Please help?

It goes in the Mail Merge Main Document at the point that you want your list
to appear.

However, you can't just type it in as is, because it's a nested field. You
need to press ctrl-F9 to enter the outermost {}, and type in all the text
inside except for { MERGEFIELD "Manager" }

Then put the insertion point between the two single quotes, press ctrl-F9 to
insert another {} and type MERGEFIELD "Manager" inside that.

Then you can use Alt-F9 to toggle between field code view and field result
view and select the field and press F9 to re-execute it.

However, I would be surprised if this field works first time and if not,
tracking down what needs to be changed can be difficult.

Also, you may need to have a look at the following KB article - I am not
sure whether it actually applies to your version of Word (regardless of what
the article says)

http://support.microsoft.com/kb/330554/en-us

Peter Jamieson
"KarenB" wrote in message
...
I have followed your instructions (for Option A), created a new worksheet
for
a list of unique manager names (2 columns) and created the merge to one of
those columns (Manager). I then modified the Database field string as
follows:

{ DATABASE \d "D:\Current Projects\ProcureIT\User List Course
Schedule.xls"
\s "SELECT
[s1].[First],[s1].[Last],[s1].[EENum],[s1].[Location],[s1].[Date]
FROM [UserDetail$] [s1] WHERE [s1].[ManagerName] = '{ MERGEFIELD "Manager"
}'" \h }

Where, exactly, do I put that string?

"Peter Jamieson" wrote:

Sorry, missed the last bit of your message.

The basic approach is the same, but the text inside the DATABASE field
has
to change, and precisely how depends on your version of Word and what is
in
your Excel sheet.

Let's suppose your Excel Sheet is called emp.xls and has columns with the
following names:

Employee Number
First Name
Last Name
Location
Date
ManagerName

then, unlike the sample you are following which uses a table or queryfor
the
data source for the merge, and a query inside a database field to provide
the invoice details, you are using the same worksheet as the data source
for
the merge and to provide the details for each managername. Suppose you
have
a very simple case with 5 records:

Employee Number,First Name,Last Name,Location,Date,ManagerName
1234,Dick,Black,ABC,01 Jan 2006,ManagerA
2345,Maisie,Scarlett,BCD,01 Jan 2006,ManagerB
3456,Colin,Mustard,CDE,15 Jan 2006,ManagerA
4567,Prabaker,Plum,ABC,01 Jan 2006,ManagerA
5678,Raven,Green,BCD,10 Jan 2006,ManagerB

Then you cannot use the sheet directly as the data source for the merge
because you will get the details for ManagerA 3 times and the details for
ManagerB twice. To get around that, you can either
a. create a separate sheet (or another type of data source altogether)
containing a column called "ManagerName" and with one row for each
manager,
and use that as the data source for the merge (NB, it is advisable to
have
at least 2 columns in any file you want to use as a data source) or
b. open the data source using a VBA OpenDataSource method call, using
SQL
to specify that you only want one record per manager. The SQL would be
something like

SELECT DISTINCT [s1].[ManagerName] FROM [Sheet1$] [s1]

If you can't make option (b) work at first, I suggest you simplify things
at
least for test purposes by using (a).

Once you have created the data source you need, the DATABASE field would
look something like this:

{ DATABASE \d "C:\\mydata\\emp.xls"
\s "SELECT [s1].[First Name],[s1].[Last Name],[s1].[Employee
Number],[s1].[Location],[s1].[Date] FROM [Sheet1$] [s1] WHERE
[s1].[ManagerName] = '{ MERGEFIELD "ManagerName" }'" \h }

This should work in Word 2003 (and possibly 2002) because the default
method
for opening an Excel sheet as a data source is OLEDB and there seems to
be
no need to use a \c switch to define a connection string.

See how far you get with that and let us know where you run into
problems.

Peter Jamieson

"KarenB" wrote in message
...
As I indicated in my original post, I have already looked at Cindy's
page,
but her instructions don't really articulate where to put that code,
and
what
the difference is between Access and Excel.

"Peter Jamieson" wrote:

Word isn't very good at this kind of thing. You may be able to use the
DATABASE field approach described on Cindy Meister's pages at

http://homepage.swissonline.ch/cindymeister/

- see Mailmerge FAQ, Special Merges, Multiple Items per condition.

Not sure whether that will work with a merge to e-mail.

Peter Jamieson

"KarenB" wrote in message
...
I am attempting to create an email merge that will send a memo to
each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and
includes a
column with the manager's name.

I need to create a single email for each manager that includes a
table
with
all staff currently reporting to them (name, employee number,
training
location, date of training). There is a column for each of these
values
in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date

------------ ------------ -------------------- ---------- -----
Joe Smith 1234 Vanc
May
15
Fred Brown 2345 Tor
May
18

I thought I was on the right track when I found Cindy's
explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the
creation
of
Customer Invoices, but after spending quite a bit of time looking at
the
example, it still wasn't clear to me how to make this work with an
Excel
spreadsheet.

Please help?

Well, I am very close.... the field worked with a bit of tweaking. The only
problem is that the field needs to be updated with each record (it doesn't
automatically update when scrolling through the records).

I checked the article you provided and it doesn't cover the version of Word
that I'm using (Word 2003), and I was unsuccessful in finding any fixes for
that version.

"Peter Jamieson" wrote:

It goes in the Mail Merge Main Document at the point that you want your list
to appear.

However, you can't just type it in as is, because it's a nested field. You
need to press ctrl-F9 to enter the outermost {}, and type in all the text
inside except for { MERGEFIELD "Manager" }

Then put the insertion point between the two single quotes, press ctrl-F9 to
insert another {} and type MERGEFIELD "Manager" inside that.

Then you can use Alt-F9 to toggle between field code view and field result
view and select the field and press F9 to re-execute it.

However, I would be surprised if this field works first time and if not,
tracking down what needs to be changed can be difficult.

Also, you may need to have a look at the following KB article - I am not
sure whether it actually applies to your version of Word (regardless of what
the article says)

http://support.microsoft.com/kb/330554/en-us

Peter Jamieson
"KarenB" wrote in message
...
I have followed your instructions (for Option A), created a new worksheet
for
a list of unique manager names (2 columns) and created the merge to one of
those columns (Manager). I then modified the Database field string as
follows:

{ DATABASE \d "D:\Current Projects\ProcureIT\User List Course
Schedule.xls"
\s "SELECT
[s1].[First],[s1].[Last],[s1].[EENum],[s1].[Location],[s1].[Date]
FROM [UserDetail$] [s1] WHERE [s1].[ManagerName] = '{ MERGEFIELD "Manager"
}'" \h }

Where, exactly, do I put that string?

"Peter Jamieson" wrote:

Sorry, missed the last bit of your message.

The basic approach is the same, but the text inside the DATABASE field
has
to change, and precisely how depends on your version of Word and what is
in
your Excel sheet.

Let's suppose your Excel Sheet is called emp.xls and has columns with the
following names:

Employee Number
First Name
Last Name
Location
Date
ManagerName

then, unlike the sample you are following which uses a table or queryfor
the
data source for the merge, and a query inside a database field to provide
the invoice details, you are using the same worksheet as the data source
for
the merge and to provide the details for each managername. Suppose you
have
a very simple case with 5 records:

Employee Number,First Name,Last Name,Location,Date,ManagerName
1234,Dick,Black,ABC,01 Jan 2006,ManagerA
2345,Maisie,Scarlett,BCD,01 Jan 2006,ManagerB
3456,Colin,Mustard,CDE,15 Jan 2006,ManagerA
4567,Prabaker,Plum,ABC,01 Jan 2006,ManagerA
5678,Raven,Green,BCD,10 Jan 2006,ManagerB

Then you cannot use the sheet directly as the data source for the merge
because you will get the details for ManagerA 3 times and the details for
ManagerB twice. To get around that, you can either
a. create a separate sheet (or another type of data source altogether)
containing a column called "ManagerName" and with one row for each
manager,
and use that as the data source for the merge (NB, it is advisable to
have
at least 2 columns in any file you want to use as a data source) or
b. open the data source using a VBA OpenDataSource method call, using
SQL
to specify that you only want one record per manager. The SQL would be
something like

SELECT DISTINCT [s1].[ManagerName] FROM [Sheet1$] [s1]

If you can't make option (b) work at first, I suggest you simplify things
at
least for test purposes by using (a).

Once you have created the data source you need, the DATABASE field would
look something like this:

{ DATABASE \d "C:\\mydata\\emp.xls"
\s "SELECT [s1].[First Name],[s1].[Last Name],[s1].[Employee
Number],[s1].[Location],[s1].[Date] FROM [Sheet1$] [s1] WHERE
[s1].[ManagerName] = '{ MERGEFIELD "ManagerName" }'" \h }

This should work in Word 2003 (and possibly 2002) because the default
method
for opening an Excel sheet as a data source is OLEDB and there seems to
be
no need to use a \c switch to define a connection string.

See how far you get with that and let us know where you run into
problems.

Peter Jamieson

"KarenB" wrote in message
...
As I indicated in my original post, I have already looked at Cindy's
page,
but her instructions don't really articulate where to put that code,
and
what
the difference is between Access and Excel.

"Peter Jamieson" wrote:

Word isn't very good at this kind of thing. You may be able to use the
DATABASE field approach described on Cindy Meister's pages at

http://homepage.swissonline.ch/cindymeister/

- see Mailmerge FAQ, Special Merges, Multiple Items per condition.

Not sure whether that will work with a merge to e-mail.

Peter Jamieson

"KarenB" wrote in message
...
I am attempting to create an email merge that will send a memo to
each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and
includes a
column with the manager's name.

I need to create a single email for each manager that includes a
table
with
all staff currently reporting to them (name, employee number,
training
location, date of training). There is a column for each of these
values
in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date

------------ ------------ -------------------- ---------- -----
Joe Smith 1234 Vanc
May
15
Fred Brown 2345 Tor
May
18

I thought I was on the right track when I found Cindy's
explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the
creation
of
Customer Invoices, but after spending quite a bit of time looking at
the
example, it still wasn't clear to me how to make this work with an
Excel
spreadsheet.

Please help?

The only
problem is that the field needs to be updated with each record (it doesn't
automatically update when scrolling through the records).

I checked the article you provided and it doesn't cover the version of
Word
that I'm using (Word 2003), and I was unsuccessful in finding any fixes
for
that version.

OK, I've just checked here. You'll need to add a DWORD value called
FieldCalcSecurityLevel and set the value to 0, under the following registry
key:
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\W ord\Options

(Follow the instructions for the other versions of Word, but substitute 11.0
in the appropriate place. The instructions are in

http://support.microsoft.com/kb/330079/EN-US/

Here at least it does change the behaviour of the DATABASE field with Word
2003. In any case, you should get the correct results when you actually
merge, but if you're mergeing to printer or e-mail it's obviously more
critical that you do because you don't get the opportunity to open the
output document and re-execute all the fields.

Peter Jamieson


"KarenB" wrote in message
...
Well, I am very close.... the field worked with a bit of tweaking. The
only
problem is that the field needs to be updated with each record (it doesn't
automatically update when scrolling through the records).

I checked the article you provided and it doesn't cover the version of
Word
that I'm using (Word 2003), and I was unsuccessful in finding any fixes
for
that version.

"Peter Jamieson" wrote:

It goes in the Mail Merge Main Document at the point that you want your
list
to appear.

However, you can't just type it in as is, because it's a nested field.
You
need to press ctrl-F9 to enter the outermost {}, and type in all the text
inside except for { MERGEFIELD "Manager" }

Then put the insertion point between the two single quotes, press ctrl-F9
to
insert another {} and type MERGEFIELD "Manager" inside that.

Then you can use Alt-F9 to toggle between field code view and field
result
view and select the field and press F9 to re-execute it.

However, I would be surprised if this field works first time and if not,
tracking down what needs to be changed can be difficult.

Also, you may need to have a look at the following KB article - I am not
sure whether it actually applies to your version of Word (regardless of
what
the article says)

http://support.microsoft.com/kb/330554/en-us

Peter Jamieson
"KarenB" wrote in message
...
I have followed your instructions (for Option A), created a new
worksheet
for
a list of unique manager names (2 columns) and created the merge to one
of
those columns (Manager). I then modified the Database field string as
follows:

{ DATABASE \d "D:\Current Projects\ProcureIT\User List Course
Schedule.xls"
\s "SELECT
[s1].[First],[s1].[Last],[s1].[EENum],[s1].[Location],[s1].[Date]
FROM [UserDetail$] [s1] WHERE [s1].[ManagerName] = '{ MERGEFIELD
"Manager"
}'" \h }

Where, exactly, do I put that string?

"Peter Jamieson" wrote:

Sorry, missed the last bit of your message.

The basic approach is the same, but the text inside the DATABASE field
has
to change, and precisely how depends on your version of Word and what
is
in
your Excel sheet.

Let's suppose your Excel Sheet is called emp.xls and has columns with
the
following names:

Employee Number
First Name
Last Name
Location
Date
ManagerName

then, unlike the sample you are following which uses a table or
queryfor
the
data source for the merge, and a query inside a database field to
provide
the invoice details, you are using the same worksheet as the data
source
for
the merge and to provide the details for each managername. Suppose you
have
a very simple case with 5 records:

Employee Number,First Name,Last Name,Location,Date,ManagerName
1234,Dick,Black,ABC,01 Jan 2006,ManagerA
2345,Maisie,Scarlett,BCD,01 Jan 2006,ManagerB
3456,Colin,Mustard,CDE,15 Jan 2006,ManagerA
4567,Prabaker,Plum,ABC,01 Jan 2006,ManagerA
5678,Raven,Green,BCD,10 Jan 2006,ManagerB

Then you cannot use the sheet directly as the data source for the
merge
because you will get the details for ManagerA 3 times and the details
for
ManagerB twice. To get around that, you can either
a. create a separate sheet (or another type of data source
altogether)
containing a column called "ManagerName" and with one row for each
manager,
and use that as the data source for the merge (NB, it is advisable to
have
at least 2 columns in any file you want to use as a data source) or
b. open the data source using a VBA OpenDataSource method call, using
SQL
to specify that you only want one record per manager. The SQL would be
something like

SELECT DISTINCT [s1].[ManagerName] FROM [Sheet1$] [s1]

If you can't make option (b) work at first, I suggest you simplify
things
at
least for test purposes by using (a).

Once you have created the data source you need, the DATABASE field
would
look something like this:

{ DATABASE \d "C:\\mydata\\emp.xls"
\s "SELECT [s1].[First Name],[s1].[Last Name],[s1].[Employee
Number],[s1].[Location],[s1].[Date] FROM [Sheet1$] [s1] WHERE
[s1].[ManagerName] = '{ MERGEFIELD "ManagerName" }'" \h }

This should work in Word 2003 (and possibly 2002) because the default
method
for opening an Excel sheet as a data source is OLEDB and there seems
to
be
no need to use a \c switch to define a connection string.

See how far you get with that and let us know where you run into
problems.

Peter Jamieson

"KarenB" wrote in message
...
As I indicated in my original post, I have already looked at Cindy's
page,
but her instructions don't really articulate where to put that code,
and
what
the difference is between Access and Excel.

"Peter Jamieson" wrote:

Word isn't very good at this kind of thing. You may be able to use
the
DATABASE field approach described on Cindy Meister's pages at

http://homepage.swissonline.ch/cindymeister/

- see Mailmerge FAQ, Special Merges, Multiple Items per condition.

Not sure whether that will work with a merge to e-mail.

Peter Jamieson

"KarenB" wrote in message
...
I am attempting to create an email merge that will send a memo to
each
manager outlining staff scheduled to attend training.

I have an Excel spreadsheet that has a list of all staff and
includes a
column with the manager's name.

I need to create a single email for each manager that includes a
table
with
all staff currently reporting to them (name, employee number,
training
location, date of training). There is a column for each of these
values
in
the spreadsheet.

Example:

To: ManagerName

The following staff are scheduled for training:

First Name Last Name Employee Number Location Date


------------ ------------ -------------------- ----------
-----
Joe Smith 1234 Vanc
May
15
Fred Brown 2345 Tor
May
18

I thought I was on the right track when I found Cindy's
explanation/website
(http://homepage.swissonline.ch/cindymeister) discussing the
creation
of
Customer Invoices, but after spending quite a bit of time looking
at
the
example, it still wasn't clear to me how to make this work with
an
Excel
spreadsheet.

Please help?