Results 1 to 3 of 3
Thread: Mail Merge Question
2001-05-03, 13:37 #1misbGuest
Mail Merge Question
Hi. I've created a function in Access to automatically merge about 80 queries with different letters in Word. The function is called from a macro. The merge works but frequently I'm left with about 20 copies of Access open on my desktop and I can't figure out what's causing the problem. I am running Office 2000 and Win95. Below is the code I'm using (I omitted some of the query names to save space). Thanks for your help!
Option Compare Database
Dim ObjWord As Word.Application
Const Size As Integer = 10
Dim strLetters(1 To Size)
Dim strMerge(1 To Size)
' Merge_With_Word Macro
' Macro recorded 2/22/01 by Preferred User
On Error GoTo errhandler
strLetters(1) = "Inq_Accounting.doc"
strLetters(2) = "Inq_Allied.doc"
strLetters(3) = "Inq_Behavsci.doc"
strLetters(4) = "Inq_Biology.doc"
strLetters(5) = "Inq_Busadm.doc"
strLetters(6) = "Inq_Busund.doc"
strLetters(7) = "Inq_Campvst.doc"
strLetters(8) = "Inq_Cheerld.doc"
strLetters(9) = "Inq_Chem.doc"
strLetters(10) = "Inq_Crimjust.doc"
strMerge(1) = "shell_inq_Accounting.doc"
strMerge(2) = "shell_inq_Allied.doc"
strMerge(3) = "shell_inq_Behavsci.doc"
strMerge(4) = "shell_inq_Biology.doc"
strMerge(5) = "shell_inq_Busadm.doc"
strMerge(6) = "shell_inq_Busund.doc"
strMerge(7) = "shell_inq_Campvst.doc"
strMerge(8) = "shell_inq_Cheerld.doc"
strMerge(9) = "shell_inq_Chem.doc"
strMerge(10) = "shell_inq_Crimjust.doc"
Set ObjWord = CreateObject("word.application.9")
ObjWord.Visible = True
' For loop - Merge document with query and create letters
For i = 1 To Size
ObjWord.Documents.Open FileName:=strMerge(i), ConfirmConversions:= _
False, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
ObjWord.ChangeFileOpenDirectory "C:WINDOWSDESKTOPInquiriesInquiry_Letters_to_S end"
ObjWord.ActiveDocument.SaveAs FileName:=strLetters(i), FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
' End For loop
' On error, continue with next instruction
2001-05-04, 03:38 #2
- Join Date
- Jan 2001
- Thanked 0 Times in 0 Posts
Re: Mail Merge Question
This may be what you need...
When you select the Data Source for the merge, before selecting the filename, check the option on the right, in the Dialog box that reads, "Select Option".
If I recall, this works to prevent the other instances of Access from opening. Let me know if it doesn't do the trick. I can dig deeper...
2001-05-04, 19:49 #3misbGuest
Re: Mail Merge Question
Thanks for the suggestion. I tried it but unfortunately it didn't solve the problem. I think I may have figured out what was creating the problem. I had configured Startup (Tools -> Startup...) to automatically open a switchboard I created and hide the rest of the database. That way the employee who will be using this will only have to see the menu with the buttons I want her to push. I realized that the problem only occurred when I opened the database and let the Startup run. If I held down the Shift key to bypass Startup, the function executed properly (without any additional copies of Access opening). So I set Startup back to the defaults, saved, and closed the database. Then I opened it and reset Startup to open the Switchboard and hide the database. After that, I haven't had any problems. Everything executes properly and only one copy of Access is open. I have no idea why this was a problem or why that fixed the problem but it's been working consistently ever since. Thanks for your help!