Reply
 
Thread Tools Display Modes
  #1   Report Post  
Ed B
 
Posts: n/a
Default Automate a mail merge in Word 2002 from a macro in Access 2002

I am trying to send data from an Access 2002 table to a standard merge
document in Word 2002. This process worked fine in Office 2000, but now I
can not get past the Word mail merge wizard with my macro. Does anyone know
how to send the necessary code to Word 2002 to bypass the wizard and merge
the data? The macro currently uses the SendKeys statement, but I would like
to use a more reliable method of calling up the merge.

--
Have a good day, Ed
  #2   Report Post  
Cindy M -WordMVP-
 
Posts: n/a
Default

Hi ?B?RWQgQg==?=,

I am trying to send data from an Access 2002 table to a standard merge
document in Word 2002. This process worked fine in Office 2000, but now I
can not get past the Word mail merge wizard with my macro. Does anyone know
how to send the necessary code to Word 2002 to bypass the wizard and merge
the data? The macro currently uses the SendKeys statement, but I would like
to use a more reliable method of calling up the merge.

You really don't provide us with enough information. Which message? What are
your SendKey statements currently supposed to do (execute which steps)? Have
you tried recording these steps in a macro in Word in order to get the basic
syntax? If not, please do so and copy the code into your response.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

  #3   Report Post  
Ed B
 
Posts: n/a
Default

I am trying to send data from an Access 2002 table to a standard merge
document in Word 2002. This process worked fine in Office 2000, but now I
can not get past the Word mail merge wizard with my macro. Does anyone know
how to send the necessary code to Word 2002 to bypass the wizard and merge
the data? The macro currently uses the SendKeys statement, but I would like
to use a more reliable method of calling up the merge.

The SendKeys statement is as follows:
SendKeys = +%TLM~G:\Regulatory\Form Letters\Standard.doc~%(trmm)

The Standard document is a letter with the merge fields already defined and
linked to the database.

The result of this is nothing happens. I dont get anywhere.

I recorded the following Macro in Word 2002
Sub MergeStandardLetter2()
'
'
ChangeFileOpenDirectory "G:\Regulatory\Form Letters\"
Documents.Open FileName:="Standard.doc", ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

But I have two problems,
a) Every time I run the Macro, I get the following screen box that I have to
respond to, that I would like to eliminate:

Opening this document will run the following SQL Command
SELECT*FROM [Payments & Filing - Current Session] WHERE (([LegalEntity] IS
NOT NULL))
Data from your database will be placed in the document. Do you want to
continue
YES NO
{would not allow me to paste the actual box on this post}

b) I dont know how to call up this Macro in Word from Access 2002.

I hope this gives you enough information to be able to help me.
--
Have a good day, Ed


"Cindy M -WordMVP-" wrote:

Hi ?B?RWQgQg==?=,

I am trying to send data from an Access 2002 table to a standard merge
document in Word 2002. This process worked fine in Office 2000, but now I
can not get past the Word mail merge wizard with my macro. Does anyone know
how to send the necessary code to Word 2002 to bypass the wizard and merge
the data? The macro currently uses the SendKeys statement, but I would like
to use a more reliable method of calling up the merge.

You really don't provide us with enough information. Which message? What are
your SendKey statements currently supposed to do (execute which steps)? Have
you tried recording these steps in a macro in Word in order to get the basic
syntax? If not, please do so and copy the code into your response.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)


  #4   Report Post  
Cindy M -WordMVP-
 
Posts: n/a
Default

Hi ?B?RWQgQg==?=,

OK, the simple part first. The message you're getting is explained in this
article, and it also gives you a work-around (a Registry key):

"Opening This Will Run the Following SQL Command" Message When You Open a Word
Document - 825765
http://support.microsoft.com?kbid=825765

For using the macro in Access, you have to automate the Word application. Could
you show us the code you're currently using to access Word in order to use the
SendKeys, please? We need that to "wrap around" the recorded code.

I recorded the following Macro in Word 2002
Sub MergeStandardLetter2()
'
'
ChangeFileOpenDirectory "G:\Regulatory\Form Letters\"
Documents.Open FileName:="Standard.doc", ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

But I have two problems,
a) Every time I run the Macro, I get the following screen box that I have to
respond to, that I would like to eliminate:

Opening this document will run the following SQL Command
SELECT*FROM [Payments & Filing - Current Session] WHERE (([LegalEntity] IS
NOT NULL))
Data from your database will be placed in the document. Do you want to
continue
YES NO
{would not allow me to paste the actual box on this post}

b) I dont know how to call up this Macro in Word from Access 2002.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

  #5   Report Post  
Ed B
 
Posts: n/a
Default

Hi, Cindy

The SendKeys statement (SendKeys = +%TLM~G:\Regulatory\Form
Letters\Standard.doc~%(trmm) ) shows the strokes to open Word. the Tools,
Links, Merge it with Word command is the one called. This coupled with the
call of the Standard document used to be enough to open Word and Merge the
data from the Access table.

I don't know if this is what you were looking for, but I don't know of
another method to start Word that allows me to run the macro I recorded.


--
Have a nice day, Ed


"Cindy M -WordMVP-" wrote:

Hi ?B?RWQgQg==?=,

OK, the simple part first. The message you're getting is explained in this
article, and it also gives you a work-around (a Registry key):

"Opening This Will Run the Following SQL Command" Message When You Open a Word
Document - 825765
http://support.microsoft.com?kbid=825765

For using the macro in Access, you have to automate the Word application. Could
you show us the code you're currently using to access Word in order to use the
SendKeys, please? We need that to "wrap around" the recorded code.

I recorded the following Macro in Word 2002
Sub MergeStandardLetter2()
'
'
ChangeFileOpenDirectory "G:\Regulatory\Form Letters\"
Documents.Open FileName:="Standard.doc", ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

But I have two problems,
a) Every time I run the Macro, I get the following screen box that I have to
respond to, that I would like to eliminate:

Opening this document will run the following SQL Command
SELECT*FROM [Payments & Filing - Current Session] WHERE (([LegalEntity] IS
NOT NULL))
Data from your database will be placed in the document. Do you want to
continue
YES NO
{would not allow me to paste the actual box on this post}

b) I donĂ¢‚¬„¢t know how to call up this Macro in Word from Access 2002.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)




  #6   Report Post  
Cindy M -WordMVP-
 
Posts: n/a
Default

Hi ?B?RWQgQg==?=,

The SendKeys statement (SendKeys = +%TLM~G:\Regulatory\Form
Letters\Standard.doc~%(trmm) ) shows the strokes to open Word. the Tools,
Links, Merge it with Word command is the one called. This coupled with the
call of the Standard document used to be enough to open Word and Merge the
data from the Access table.

Shift+Alt+T, followed by L and then M opens Word? What menu commands are these
in Access?

Not that it matters, I suppose, because you won't be able to go this route to
do any sensible automation... Automating another application is not a trivial
thing for someone with minimal programming experience, but let's see...

You need to copy this to an ACCESS module and test. I'm not going to guarantee
that I won't make any typing/syntax errors.

Before you try to run it, you need to go to the Tools/References menu in the
VB Editor and activate the checkbox for "Microsoft Word 10" (or similar).

If you get error messages, be sure to quote the full and exact text in your
reply, and indicate on which line it occurs.

Sub MergeStandardLetter2()
Dim wd as Word.Application
Dim doc aS Word.Document

Set wd = new Word.Application
wd.Visible = True
Set doc = wd.Documents.Open( _
FileName:="G:\Regulatory\Form Letters\Standard.doc", _
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto)
With doc.MailMerge
.Destination = wd.wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wd.wdDefaultFirstRecord
.LastRecord = wd.wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Set doc = Nothing
Set wd = Nothing
End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

  #7   Report Post  
Ed B
 
Posts: n/a
Default

Cindy,

I tried your suggested macro and got the following error messages:

Sub MergeStandardLetter2()
Dim wd as Word.Application
Dim doc aS Word.Document

Set wd = new Word.Application
wd.Visible = True
Set doc = wd.Documents.Open( _
FileName:="G:\Regulatory\Form Letters\Standard.doc", _
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto)
With doc.MailMerge
..Destination = wd.wdSendToNewDocument
..SuppressBlankLines = True
With .DataSource
..FirstRecord = wd.wdDefaultFirstRecord
..LastRecord = wd.wdDefaultLastRecord
End With
..Execute Pause:=False
End With
Set doc = Nothing
Set wd = Nothing
End Sub

Compile Error: Method or Data Member Not Found

I had to remove the "md." from the following lines to get it to compile
.Destination = wd.wdSendToNewDocument
.FirstRecord = wd.wdDefaultFirstRecord
.LastRecord = wd.wdDefaultLastRecord

After I did that, I tried to run the macro, and got the following error
"Run-Time error '5852' Requested object is not available"

and the debugger highlighted the following line of code:
..Destination = wdSendToNewDocument

What could be the problem, as it works fine in the recorded macro I did in
Word?

--
Have a nice day, Ed


"Cindy M -WordMVP-" wrote:

Hi ?B?RWQgQg==?=,

The SendKeys statement (SendKeys = +%TLM~G:\Regulatory\Form
Letters\Standard.doc~%(trmm) ) shows the strokes to open Word. the Tools,
Links, Merge it with Word command is the one called. This coupled with the
call of the Standard document used to be enough to open Word and Merge the
data from the Access table.

Shift+Alt+T, followed by L and then M opens Word? What menu commands are these
in Access?

Not that it matters, I suppose, because you won't be able to go this route to
do any sensible automation... Automating another application is not a trivial
thing for someone with minimal programming experience, but let's see...

You need to copy this to an ACCESS module and test. I'm not going to guarantee
that I won't make any typing/syntax errors.

Before you try to run it, you need to go to the Tools/References menu in the
VB Editor and activate the checkbox for "Microsoft Word 10" (or similar).

If you get error messages, be sure to quote the full and exact text in your
reply, and indicate on which line it occurs.

Sub MergeStandardLetter2()
Dim wd as Word.Application
Dim doc aS Word.Document

Set wd = new Word.Application
wd.Visible = True
Set doc = wd.Documents.Open( _
FileName:="G:\Regulatory\Form Letters\Standard.doc", _
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto)
With doc.MailMerge
.Destination = wd.wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wd.wdDefaultFirstRecord
.LastRecord = wd.wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Set doc = Nothing
Set wd = Nothing
End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)


  #8   Report Post  
Cindy M -WordMVP-
 
Posts: n/a
Default

Hi ?B?RWQgQg==?=,

I had to remove the "md." from the following lines to get it to compile
.Destination = wd.wdSendToNewDocument
.FirstRecord = wd.wdDefaultFirstRecord
.LastRecord = wd.wdDefaultLastRecord

After I did that, I tried to run the macro, and got the following error
"Run-Time error '5852' Requested object is not available"

and the debugger highlighted the following line of code:
..Destination = wdSendToNewDocument

Try substituting the following, in order, in the three lines
0
1
-16

Note, however, that the error message could mean there's a problem with
state of the mailmerge document, not with the actual code I gave you.

From the sound of it, you may have lost the link to the data source. As a
matter of fact... See the following article, and make the suggested
change to the Registry key mentioned. Then open the document manually and
connect to the data source, save and close. Now test the code again.

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765
http://support.microsoft.com?kbid=825765

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

  #9   Report Post  
Ed B
 
Posts: n/a
Default

Cindy,

I don't have the ability to change the registry keys on my computer as I am
not an administrator, also, this would have to be done on a number of
computers that are trying to use this routine (nine associates use the
database and prepare letters automatically from it). I don't believe our
company policy on security would allow the change of the registry keys anyway.

I can open the Standard document from within Word and connect to the
database and the letter will merge with the proper data table in the access
database.

I don't understand what you want me to substitute the numbers for in the
three lines . 0 should be substituted for what? I apologize if I sound dumb,
but I am not that familiar with VBA code.

Again, thanks for your help.
--
Have a nice day, Ed


"Cindy M -WordMVP-" wrote:

Hi ?B?RWQgQg==?=,

I had to remove the "md." from the following lines to get it to compile
.Destination = wd.wdSendToNewDocument
.FirstRecord = wd.wdDefaultFirstRecord
.LastRecord = wd.wdDefaultLastRecord

After I did that, I tried to run the macro, and got the following error
"Run-Time error '5852' Requested object is not available"

and the debugger highlighted the following line of code:
..Destination = wdSendToNewDocument

Try substituting the following, in order, in the three lines
0
1
-16

Note, however, that the error message could mean there's a problem with
state of the mailmerge document, not with the actual code I gave you.

From the sound of it, you may have lost the link to the data source. As a
matter of fact... See the following article, and make the suggested
change to the Registry key mentioned. Then open the document manually and
connect to the data source, save and close. Now test the code again.

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765
http://support.microsoft.com?kbid=825765

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)


  #10   Report Post  
Cindy M -WordMVP-
 
Posts: n/a
Default

Hi ?B?RWQgQg==?=,

1. When you open a mail merge document using VBA, in Word 2002 with SPs
installed or any Word 2003, as in the code here, the data source link is
removed. Unless you change the Registry key.

2. This "security feature" is actually ridiculous. The only SQL a Word mail
merge can execute is a SELECT statement. And in order to do that, it has to find
the data source file in EXACTLY the same path as when the main merge document
was set up. The chances that someone external can send you such a combination
that will, in addition, be ABLE to execute something "bad" are so small that I
would have no qualms about allowing this Registry key change. But if your IT
people use knee-jerk reactions instead of thinking through and evaluating
problems, then you probably have no chance.

3. This means, your macro code would also have to ATTACH the data source to the
main merge document, after opening it. And that means you have to record
attaching to the data source in a macro, as well. And no guarantees that will
work 100%, but we can try.

4. The numbers I gave you are the real values behind the wd... things. I gave
you those to make sure the problem had nothing to do with your VBA code, but was
purely a question of the actions of this security measure.

5. And, before you ask, no, there's no reliable way to use the SendKeys method
you had before in Word 2002/2003. The interface changed. SendKeys dupicates your
exact keystrokes, and the commands it was using before aren't there anymore.

I don't have the ability to change the registry keys on my computer as I am
not an administrator, also, this would have to be done on a number of
computers that are trying to use this routine (nine associates use the
database and prepare letters automatically from it). I don't believe our
company policy on security would allow the change of the registry keys anyway.

I can open the Standard document from within Word and connect to the
database and the letter will merge with the proper data table in the access
database.

I don't understand what you want me to substitute the numbers for in the
three lines . 0 should be substituted for what? I apologize if I sound dumb,
but I am not that familiar with VBA code.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)

Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Word 2003 Issue S.Nicks Mailmerge 5 February 20th 09 02:55 AM
Macros - Keyboard Commands Janet Microsoft Word Help 6 April 11th 05 05:28 AM
Wordperfect Office 2000 conversion to Word 2003 MikeE New Users 1 March 21st 05 01:04 AM
How do I create & merge specific data base & master documents? maggiev New Users 2 January 13th 05 12:30 AM
Word 2002 Mail Merge - merging to new doc displays blank empty dia Steve Vincent Mailmerge 1 December 15th 04 07:28 PM


All times are GMT +1. The time now is 09:08 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"