Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge Word 2003 Issue | Mailmerge | |||
Macros - Keyboard Commands | Microsoft Word Help | |||
Wordperfect Office 2000 conversion to Word 2003 | New Users | |||
How do I create & merge specific data base & master documents? | New Users | |||
Word 2002 Mail Merge - merging to new doc displays blank empty dia | Mailmerge |