Mail Merge Automation

[Ricki Miles]

I am creating a template that will be used as the form letter for a mail
merge. In order to make it easier for the user not to have to choose Tools,
Mail Merge, Create, Form Letters, Get Data, Open Data Source and choose the
data source, I would like to make the whole process a macro. The only part
that will be difficult for me is calling up the data source to use. How
could I have a message box asking for the data source pathname during the
running of the macro?

Thanks very much,

Ricki

 

[Doug Robbins - Word MVP]

Use the following:

Dialogs(wdDialogMailMergeOpenDataSource).Show


--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Ricki Miles]

Hi Doug,

I appreciate your help. Showing the dialog box is not exactly what I was
hoping for. These are very basic users who don't know anything about
merging and the templates need to be as easy as possible. If the data
source is called 123.xls, for example, when they create a new document from
the template, all I would like them to have to do is press one toolbar
button which will run a macro to do the following:

Ask them which data source in a message box so that they can type 123.xls

Do the merge, unlink all fields and show the Save As dialog box to prompt
them to save this completed file.

Thanks again for your help,

Ricki

 

[Doug Robbins - Word MVP]

To get input from the user, use an InputBox.

However, I question your reasoning. If the users are "very basic users":

1. How will they know what to type?

2. There's a good chance that they will get it wrong

3. There's an even better chance that they will get it wrong if they have
to type not only the filename, but the correct path to the file as well.

If on the other hand they use the open data source dialog, they can just
navigate to the file and once they select it, you don't need any more code
to attach it to the mailmerge main document.

One other thing that doesn't seem to fit to me is your reference to "unlink
all fields". What do you mean by that?

--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP

 

[Ricki Miles]

Thanks for the thoughts. I will try it with the dialog box, rather than the
input box. The users are creating forms that will merge data in from the
excel data source, including special signature blocks using IF statements
(ie. if there are two borrowers, if there is a corporation, etc. the
signature block is different). Since there can occasionally be some
addresses that don't fit well in a table cell, or the signature block breaks
across a page, etc., they may have to edit the form and/or the data - more
formatting than anything else. That's the reason I think the fields should
be unlinked. Otherwise, they will make changes, but the changes may be lost
when they print.

Thanks again - I will give it a try,

Ricki

 

[Ricki Miles]

Hi again, Doug,

I tried using the dialog box as in the previous message and it allowed me to
navigate to the data source, but then, since the source was an Excel file,
it took me to that Excel file. I would like the user to choose the data
source and have the merge continue automatically. Do I need another line of
code to get back from Excel into the merge?

Also, I tried the Input box to see how it works, but I could only get it to
give me the input box. I couldn't get the value of the input box into the
macro code, as I've never used an input box before. Does there have to be a
line of code somewhere when the mail merge is running that picks up the
value of the input box?

Thanks for all your help,

Ricki