Results 1 to 3 of 3
  1. #1

    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)

    Function Merge_With_Word()
    ' 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.ChangeFileOpenDirectory"C:WINDOWSDESKTOPIn quiriesInquiry_Merge_Documents"
    ObjWord.Documents.Open FileName:=strMerge(i), ConfirmConversions:= _
    False, ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto
    With ObjWord.ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .MailAsAttachment = False
    .MailAddressFieldName = ""
    .MailSubject = ""
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=True
    End With
    ObjWord.ChangeFileOpenDirectory "C:WINDOWSDESKTOPInquiriesInquiry_Letters_to_S end"
    'Save Letter
    ObjWord.ActiveDocument.SaveAs FileName:=strLetters(i), FileFormat:= _
    wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
    True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
    False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    Next i
    ' End For loop
    Exit Function

    ' On error, continue with next instruction
    Resume Next

    End Function

  2. #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...


    Rich P.

  3. #3

    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!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts