"is now available for editing" problem with Excel-automated mail merge

[Melissa]

I have an excel file template that automated to open from an Access
application that will 1) save the template as a new file with the
current date/time appended to the file name, 2) pull data from an
Access database and format the data into a usable mail merge file, and
3) open the mail merge file in Word. This all works well only I have
two problems with which I need some help.

The first problem is how can I automate the mail merge file to open
with the current Excel file as the datasource for the mail merge?
Right now, I'm cutting and pasting the file path into Word myself.

The second problem is that once the mail merge is done, I close the
Word file and the Excel file. Then I get a message that the Excel
file I just closed file "is now available for editing. Choose Read-
Write to open it for editing." What can I do to prevent this?

Here's the code that currently opens the Word document:

Sub Create_Emails()

Dim appWD As Object
Dim stPath As String

ActiveWorkbook.Save

stPath = "X:\ProdMgt\RRAdmin\Request Proc\Entitlement and Increased
Access Requests\Templates\Request Denied (EIA) TEMPLATE.doc"
Set appWD = CreateObject("Word.Application") appWD.Visible = True
appWD.Documents.Open (stPath)
appWD.ActiveDocument.MailMerge.MainDocumentType = wdEMail Set appWD =
Nothing

End Sub

 

[Earl Kiosterud]

Melissa,

Whatever the criteria is for pulling the Access records you want for the mail merge (date
range, etc.), it seems you could just use that criteria in the Word mail merge, using the
Access table as the merge data source. What is the reason for first putting the data in
Excel? Look at the merge in Word -- there is query capability there. It varies with the
version of Word you're using.

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Melissa]

Thanks, Earl. The reason for not doing that directly in Word is that
the data I'm pulling needs to be formatted first. I'm sending
responses to request for research via email. One email could be a
response about more than one research provider. Since the data in
Access would return one row per email address per provider (email1/
provider1, email1/provider2, etc.), I needed to write some code that
provided fields named provider1, provider2, etc. and returned one row
with all providers for that email in the row (i.e., email1/provider1/
provider2, email2/provider1, email3, provider1, provider2, provider3)
If there's a way to do that directly from Access into Word, please
share. Importing the data into Excel and manipulating it with VBA was
the best solution we could come up with.

 

[Earl Kiosterud]

Melissa,

It sounds as if you have more than one provider per Access record. If that's the case, then
you're going to have to split them for sure. The Word mail merge, as far as I know, is set
up for a normal database where there's one record per item -- it's not set up to send
multiple letters via multiple fields in one record.

 

[Melissa]

Perhaps I didn't explain what I'm doing very well the first time. I
only have one provider per Access record. Each request record has the
email address of the person making the request and the provider he's
requesting. If a user selects three providers, I'll have three rows
in the table. The query will pull one record per request and, thus,
return three rows in the results. When it comes to the mail merge
process, querying directly from Access would only allow field names
"email", "firstname", and "broker". I don't want to send three emails
to one person. I want responses to all three requests in one email.
So we import the data into Excel, manipulate it with VBA to produce
one row per user with all requested brokers (broker1, broker2,
broker3). Is there another, more efficient way to do this?