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

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

0
8/27/2007 5:59:35 PM
excel 39879 articles. 2 followers. Follow

4 Replies
444 Views

Similar Articles

[PageSpeed] 34

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" <mwilliams@snl.com> wrote in message 
news:1188237575.618733.264740@k79g2000hse.googlegroups.com...
>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
> 


0
someone798 (944)
8/27/2007 6:23:56 PM
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.

On Aug 27, 2:23 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> 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 Kiosterudwww.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" <mwilli...@snl.com> wrote in message
>
> news:1188237575.618733.264740@k79g2000hse.googlegroups.com...
>
> >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


0
8/27/2007 7:23:56 PM
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.
-- 
Earl Kiosterud
www.smokeylake.com

-----------------------------------------------------------------------
"Melissa" <mwilliams@snl.com> wrote in message 
news:1188242636.148982.50650@r34g2000hsd.googlegroups.com...
> 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.
>
> On Aug 27, 2:23 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
>> 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 Kiosterudwww.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" 
>> <mwilli...@snl.com> wrote in message
>>
>> news:1188237575.618733.264740@k79g2000hse.googlegroups.com...
>>
>> >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
>
> 


0
someone798 (944)
8/28/2007 2:00:14 AM
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?

On Aug 27, 10:00 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> 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.
> --
> Earl Kiosterudwww.smokeylake.com
>
> -----------------------------------------------------------------------"Melissa" <mwilli...@snl.com> wrote in message
>
> news:1188242636.148982.50650@r34g2000hsd.googlegroups.com...
>
> > 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.
>
> > On Aug 27, 2:23 pm, "Earl Kiosterud" <some...@nowhere.com> wrote:
> >> 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 Kiosterudwww.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"
> >> <mwilli...@snl.com> wrote in message
>
> >>news:1188237575.618733.264740@k79g2000hse.googlegroups.com...
>
> >> >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


0
8/29/2007 1:42:51 PM
Reply:

Similar Artilces:

"is now available for editing" problem with Excel-automated mail merge
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...