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?