Dept with > 50 mail merges look for a way to streamline process

[Holly Schott]

Hello. I work at a University that is changing student software package and
therefore changing how we do business. Our Recruiting and Admissions
department sends out more than 50 different letters to various populations
continuously throughout the year. The data is extracted, one file per
letter, from the student Oracle database into a doc files and is they are
ftp'd to the departments pc.

I have tested by setting up 2 macros each containing one letter's mail
merge. Each macro begins by opening the word doc and ends with printing the
letters and closing without saving. I then set up a 3rd (Master) macro which
runs the 2 letter macros. This works but was difficult to set up (the
macro recorder is picky).

All of the original letter docs will be changed periodicly.

Could someone tell me a better way to do this?

Thanks

 

[Peter Jamieson]

Some considerations:
a. who will be producing any code that you might need
b. who will be maintaining the templates (i.e. will it be a
technically-oriented person or someone who does not know how the package as
a whole fits together)
c. how do you decide what to produce and when?
d. what needs to happen to the output after it is distributed? Do the
departments just print the results, or what?
e. did your previous system use Word for this, or are you only considering
Word because of the change of package?

(And which version of Word?)

I don't think you will get very far using recorded macros and you will
probably need to understand and edit them substantially to get what you
need, but they can take you quite a long way.

[Personally, I would at least consider having a look at something like the
Reporting Services features in SQL Server 2005 (I believe that there is a
version even with the free SQL Server Express 2005, but I do not know
whether it is severely limited compared to the real thing). I can't say I
know much about it, and e.g. whether it would be easy to work with Oracle
data, but it's the kind of thing that is geared to this sort of
applciation.]

Peter Jamieson

 

[Holly Schott]

Peter,

Thanks so much. The answers to your questions are embedded below. I don't
know how far I would get with SQL Server, but will ask around the office.
Unfortunately, as a State university we are not allowed to have software on
our machines that isn't licensed. So I don't know how far I'll get with
freeware.

Thanks again.

Holly

Some considerations:
a. who will be producing any code that you might need

The Admissions office maintains the letter doc and will be receiving the
data file.
The plan is to have them print the letters in their offices for distribution.

b. who will be maintaining the templates (i.e. will it be a
technically-oriented person or someone who does not know how the package as
a whole fits together)

Technical people will be figuring out how to do set-up and maintenance,
documenting
the procedure, and then turning it over the the non-technical Admissions
office. Unfortunately
we do not have anyone who is a Word guru.

c. how do you decide what to produce and when?

The 50 letters are produced on different schedules. Some are daily, some
are once a
year, etc.

d. what needs to happen to the output after it is distributed? Do the
departments just print the results, or what?

Admissions will print, stuff, and take to post office.

e. did your previous system use Word for this, or are you only considering
Word because of the change of package?

Our previous system created letters, but not pretty ones. We are
considering Word
because everyone has it on their machine.

(And which version of Word?)

I'm on Word 2002, not everyone will be, but we can force an
upgrade if necessary

I don't think you will get very far using recorded macros and you will
probably need to understand and edit them substantially to get what you
need, but they can take you quite a long way.

[Personally, I would at least consider having a look at something like the
Reporting Services features in SQL Server 2005 (I believe that there is a
version even with the free SQL Server Express 2005, but I do not know
whether it is severely limited compared to the real thing). I can't say I
know much about it, and e.g. whether it would be easy to work with Oracle
data, but it's the kind of thing that is geared to this sort of
applciation.]

Peter Jamieson

Hello. I work at a University that is changing student software package
and
therefore changing how we do business. Our Recruiting and Admissions
department sends out more than 50 different letters to various populations
continuously throughout the year. The data is extracted, one file per
letter, from the student Oracle database into a doc files and is they are
ftp'd to the departments pc.

I have tested by setting up 2 macros each containing one letter's mail
merge. Each macro begins by opening the word doc and ends with printing
the
letters and closing without saving. I then set up a 3rd (Master) macro
which
runs the 2 letter macros. This works but was difficult to set up (the
macro recorder is picky).

All of the original letter docs will be changed periodicly.

Could someone tell me a better way to do this?

Thanks

 

[Holly Schott]

Peter,

Thanks for your help. Here are the answers to your questions.

I'm on Word 2002, not everyone will be, but I think we can force an
upgrade if necessary.

a.
The Admissions office maintains the letter doc and will be receiving the
data file.
The plan is to have them print the letters in their offices for distribution.

b.
Technical people will be figuring out how to do set-up and maintenance,
documenting
the procedure, and then turning it over the the non-technical Admissions
office. Unfortunately
we do not have anyone who is a Word guru.

c.

The 50 letters are produced on different schedules. Some are daily, some
are once a
year, etc.

d.

Admissions will print, stuff, and take to post office.

e.


Our previous system created letters, but not pretty ones. We are
considering Word
because everyone has it on their machine.

Holly

Some considerations:
a. who will be producing any code that you might need
b. who will be maintaining the templates (i.e. will it be a
technically-oriented person or someone who does not know how the package as
a whole fits together)
c. how do you decide what to produce and when?
d. what needs to happen to the output after it is distributed? Do the
departments just print the results, or what?
e. did your previous system use Word for this, or are you only considering
Word because of the change of package?

(And which version of Word?)

I don't think you will get very far using recorded macros and you will
probably need to understand and edit them substantially to get what you
need, but they can take you quite a long way.

[Personally, I would at least consider having a look at something like the
Reporting Services features in SQL Server 2005 (I believe that there is a
version even with the free SQL Server Express 2005, but I do not know
whether it is severely limited compared to the real thing). I can't say I
know much about it, and e.g. whether it would be easy to work with Oracle
data, but it's the kind of thing that is geared to this sort of
applciation.]

Peter Jamieson

Hello. I work at a University that is changing student software package
and
therefore changing how we do business. Our Recruiting and Admissions
department sends out more than 50 different letters to various populations
continuously throughout the year. The data is extracted, one file per
letter, from the student Oracle database into a doc files and is they are
ftp'd to the departments pc.

I have tested by setting up 2 macros each containing one letter's mail
merge. Each macro begins by opening the word doc and ends with printing
the
letters and closing without saving. I then set up a 3rd (Master) macro
which
runs the 2 letter macros. This works but was difficult to set up (the
macro recorder is picky).

All of the original letter docs will be changed periodicly.

Could someone tell me a better way to do this?

Thanks

 

[Peter Jamieson]

Thanks so much. The answers to your questions are embedded below. I

don't
know how far I would get with SQL Server, but will ask around the office.
Unfortunately, as a State university we are not allowed to have software
on
our machines that isn't licensed. So I don't know how far I'll get with
freeware.

It sounds like a relatively non-technical dept. will need to maintain the
letters and that suggests that an everyday package such as Word is likely to
be the only game in town. The main argument against that is that it's easier
to screw it up, but that's an option available in most software packages.

I'm on Word 2002, not everyone will be, but we can force an
upgrade if necessary

Word 2002 should be OK, since you're just distributing the results of the
application. [What you do /not/ want to be doing with Word Mailmerge
applications is having to distribute the mailmerge application itself].

The 50 letters are produced on different schedules. Some are daily, some
are once a
year, etc.

I would probably aim to produce one VBA macro to process each schedule
(though it does depend on how easy it is to start the macro from whatever
you are using for scheduling). Each macro would probably process a simple
list (probably held in a Word document or perhaps a .txt file) of the merge
templates to process in that schedule. If it is possible to define all the
necessary selection/filter/sort criteria for a template within the template,
I would probably do that. Otherwise, you can do the same thing in an
OpenDataSource call. I would certainly check that I could open whatever
Oracle tables/views I needed (assuming you are getting your data directly
from the database - maybe it is being exported first?) early in my
feasibility testing. If all the merges aim to produce separate letters for
each record in the data source, you should be able to use the same code
"pattern" to process all of the data. There are a few examples of how to do
this - e.g. search this group using Google groups for messages containing
"Jamieson activerecord" or have a look at Graham Mayor's site at
http://www.gmayor.com for some other approaches. You will also need to apply
the registry patch described in the following article:

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

Peter Jamieson

Peter,

Thanks so much. The answers to your questions are embedded below. I
don't
know how far I would get with SQL Server, but will ask around the office.
Unfortunately, as a State university we are not allowed to have software
on
our machines that isn't licensed. So I don't know how far I'll get with
freeware.

Thanks again.

Holly

Some considerations:
a. who will be producing any code that you might need

The Admissions office maintains the letter doc and will be receiving the
data file.
The plan is to have them print the letters in their offices for
distribution.

b. who will be maintaining the templates (i.e. will it be a
technically-oriented person or someone who does not know how the package
as
a whole fits together)

Technical people will be figuring out how to do set-up and maintenance,
documenting
the procedure, and then turning it over the the non-technical Admissions
office. Unfortunately
we do not have anyone who is a Word guru.

c. how do you decide what to produce and when?

The 50 letters are produced on different schedules. Some are daily, some
are once a
year, etc.

d. what needs to happen to the output after it is distributed? Do the
departments just print the results, or what?

Admissions will print, stuff, and take to post office.

e. did your previous system use Word for this, or are you only
considering
Word because of the change of package?

Our previous system created letters, but not pretty ones. We are
considering Word
because everyone has it on their machine.

(And which version of Word?)

I'm on Word 2002, not everyone will be, but we can force an
upgrade if necessary

I don't think you will get very far using recorded macros and you will
probably need to understand and edit them substantially to get what you
need, but they can take you quite a long way.

[Personally, I would at least consider having a look at something like
the
Reporting Services features in SQL Server 2005 (I believe that there is a
version even with the free SQL Server Express 2005, but I do not know
whether it is severely limited compared to the real thing). I can't say I
know much about it, and e.g. whether it would be easy to work with Oracle
data, but it's the kind of thing that is geared to this sort of
applciation.]

Peter Jamieson

Hello. I work at a University that is changing student software
package
and
therefore changing how we do business. Our Recruiting and Admissions
department sends out more than 50 different letters to various
populations
continuously throughout the year. The data is extracted, one file per
letter, from the student Oracle database into a doc files and is they
are
ftp'd to the departments pc.

I have tested by setting up 2 macros each containing one letter's mail
merge. Each macro begins by opening the word doc and ends with
printing
the
letters and closing without saving. I then set up a 3rd (Master) macro
which
runs the 2 letter macros. This works but was difficult to set up
(the
macro recorder is picky).

All of the original letter docs will be changed periodicly.

Could someone tell me a better way to do this?

Thanks