none
mail merge with MS Access 2007 VBA to MS Word 2007

    Question

  • I was merging quite successfully from MS 2007 to MS Word 2000 forms (templates - .dot files).  But when I tried to do the same thing with MS Word 2007, the templates had to be re-connected manually each time to the templates to make they work as opposed to programmatically as they were done previously.  The MS Access vba I was using successfully with Word 200 is as follows:

    Private Sub cmdMailingLabels_Click()
    On Error GoTo Err_Mailing_label_button_Click

      
       
        DoCmd.SetWarnings False
       
        
        DoCmd.OpenQuery "qry Mailing labels", , acEdit
        DoCmd.TransferText acExportMerge, , "tbl Mailing labels", "c:\Mail Labels.txt"
        DoCmd.OpenQuery "qry Unmark Mailing Labels", , acEdit
       
        DoCmd.SetWarnings True
       
        'Declare an instance of Microsoft Word
            Dim Wrd As New Word.Application
            Set Wrd = CreateObject("Word.Application")

        'Specify the path and name to the Word Document
            Dim MergeDoc As String
            MergeDoc = Application.CurrentProject.Path
            MergeDoc = MergeDoc & "\Test Mailing Labels.dotx"

    'Open the document template, make it visible
    Wrd.Documents.Add MergeDoc
    Wrd.Visible = True

       
    exit_Mailing_label_button_click:
        Exit Sub

    Err_Mailing_label_button_Click:
        MsgBox Error$
        Resume exit_Mailing_label_button_click
      End Sub

    The preceding code worked just fine when merge with MS Word 2000 but not MS Word 2007. 

    Can you help?

    Boochie

    Wednesday, August 15, 2012 11:44 PM

Answers

  • Thanks for the info on MS Access to MS Word 2007.  The problem is now resolved with the help of this link:

    http://support.microsoft.com/kb/825765/en-us

    When I made the registry change, my original code still works perfectly and now the data source is linked and active when I open the mail merge letter with the MS Access mail merge code.

    Boochie


    Boochie

    Saturday, August 25, 2012 8:19 PM

All replies

  • Hi Boochie,

    I mailmerge this way in 2007 and 2010, the part you would be interested in is the DoEvents part of the code

     hope it helps..

       strTable = "tblTmpHepBMerge"
       'Delete the table if it exists
    DoCmd.DeleteObject acTable, strTable
    strSQL = "Select * INTO " & strTable & " FROM qryWordMergeHepB where [print]= -1"
    CurrentDb.Execute strSQL

    'Add and remove the Print and Printed flag
    cmdAddTick
    cmdRemoveTick

    DoEvents
      Dim WrdMrg
            WrdMrg = "Y:\Diseases\CDDBMailMerge\Hep_B_case_report.dotx"
            Shell "C:\WINDOWS\explorer.exe """ & WrdMrg & "", vbNormalFocus
     Me.fsubClinicianResident.Form.Requery


    David

    Thursday, August 16, 2012 12:25 AM
  • Hi Boochie,

    Welcome to the Access forum!

    IMHO, DataSource has to be set for MergeDoc. Currently, I can't see the relation between MergeDoc and "c:\Mail Labels.txt".

    Please refer to the following links as well:

    OpenDataSource Method
    http://msdn.microsoft.com/en-us/library/office/aa220319(v=office.11).aspx 

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Friday, August 17, 2012 9:48 AM
  • David, I am sorry to ask this.  I am just a novice VBA user and do not understatnd your reference to Y:\Diseases.......  Is this the path for your data source?  I my case the path for the .txt file is C:\Mail Labels.txt.  I do not understand the part of the path "CDDBMailMerge".  Can you help?

    Thanks

    Boochie


    Boochie

    Sunday, August 19, 2012 9:08 PM
  • Hi Boochie

    Here is the logic for my mailmerge.

    1. Open mailmerge form

    2. Select record and tick the chkbox Print

    3. Press Mailmerge button

    the following code runs

     strTable = "tblTmpHepBMerge"
       'Delete the table if it exists
    DoCmd.DeleteObject acTable, strTable
    strSQL = "Select * INTO " & strTable & " FROM qryWordMergeHepB where [print]= -1"
    CurrentDb.Execute strSQL

    Then.

    'Goto the following file (Hep_B_case_report.dotx)on the (Y:\Diseases\CDDBMailMerge)

    Merge the data to this file.

    The Y drive is a network drive..this could be your c: drive or what ever

    DoEvents
      Dim WrdMrg
            WrdMrg = "Y:\Diseases\CDDBMailMerge\Hep_B_case_report.dotx"
            Shell "C:\WINDOWS\explorer.exe """ & WrdMrg & "", vbNormalFocus
     Me.fsubClinicianResident.Form.Requery



    David

    Sunday, August 19, 2012 10:29 PM
  • I have changed my code as follows but I get a message saying:  "Invalid Procedure call to Argument."

    On Error GoTo Err_Mailing_label_button_Click

      
       
        DoCmd.SetWarnings False
       
        
        DoCmd.OpenQuery "qry Mailing labels", , acEdit
        DoCmd.TransferText acExportMerge, , "tbl Mailing labels", "c:\Mail Labels.txt"
        DoCmd.OpenQuery "qry Unmark Mailing Labels", , acEdit
       
        DoCmd.SetWarnings True
       
        'Declare an instance of Microsoft Word
            Dim Wrd As New Word.Application
            Set Wrd = CreateObject("Word.Application")

        'Specify the path and name to the Word Document
            'Old Code:  Dim MergeDoc As String
            'Old Code MergeDoc = Application.CurrentProject.Path
           
            DoEvents
            Dim MergeDoc As String
           
            MergeDoc = "C:\Temp\TEST ACCESS DATABASE LOOKUP REPORTER\Test Mailing Labels.dot"
            Shell "C:\WINDOWS\explorer.exe""" & MergeDoc & "", vbNormalFocus

    'Open the document template, make it visible
    'Old Code:  Wrd.Documents.Add MergeDoc
    'Old Code:  Wrd.Visible = True

       
    exit_Mailing_label_button_click:
        Exit Sub

    Err_Mailing_label_button_Click:
        MsgBox Error$
        Resume exit_Mailing_label_button_click

    Hope this helps.

    Boochie


    Boochie

    Sunday, August 19, 2012 10:32 PM
  • Hi,

    You donot need to declare an instance

    Try this

    On Error GoTo Err_Mailing_label_button_Click

      
       
        DoCmd.SetWarnings False
       
        
        DoCmd.OpenQuery "qry Mailing labels", , acEdit
        DoCmd.TransferText acExportMerge, , "tbl Mailing labels", "c:\Mail Labels.txt"
        DoCmd.OpenQuery "qry Unmark Mailing Labels", , acEdit
       
        DoCmd.SetWarnings True

      
            DoEvents
            Dim MergeDoc As String
           
            MergeDoc = "C:\Temp\TEST ACCESS DATABASE LOOKUP REPORTER\Test Mailing Labels.dot"
            Shell "C:\WINDOWS\explorer.exe""" & MergeDoc & "", vbNormalFocus

       
    exit_Mailing_label_button_click:
        Exit Sub

    Err_Mailing_label_button_Click:
        MsgBox Error$
        Resume exit_Mailing_label_button_click


    David

    Sunday, August 19, 2012 10:45 PM
  • Dave, I just copied and pasted your code snippet and overwrote mine.  I get this error message:  "Invalid Procecure Call or Argument."

    Boochie


    Boochie

    Sunday, August 19, 2012 11:13 PM
  • Ok,

    there is a problem here.

     DoCmd.TransferText acExportMerge, , "tbl Mailing labels", "c:\Mail Labels.txt"

    Lets go back to the begining

    The word document you are merging to, what is the qryName (recordsource)?

    Look at my code again, i will write better comments

       Dim strSQL As String
       Dim strTable As String

     strTable = "tblTmpHepBMerge" 'This is the recordsource of the word document


       'Delete the table if it exists (Set up new table ready to append new records for mailmerge)
    DoCmd.DeleteObject acTable, strTable

    strSQL = "Select * INTO " & strTable & " FROM qryWordMergeHepB where [print]= -1" 'This populates the new recordsource
    CurrentDb.Execute strSQL

    DoEvents (The following code opens word that has the tblTmpHepBMerge recordsource and merges)
      Dim WrdMrg
            WrdMrg = "Y:\Diseases\CDDBMailMerge\Hep_B_case_report.dotx"
            Shell "C:\WINDOWS\explorer.exe """ & WrdMrg & "", vbNormalFocus


    David

    Sunday, August 19, 2012 11:27 PM
  • The query is called "qry Mailing labels".  I have an invoice form based on a query.  One of the fields on the form is a check box to be checked off if a mailing label is required.  Once checked, then I click on the Mailing Labels command button which triggers the listed code above.  The form query contains two tables:  Invoices and Clients with a relationship on the client number field from both tables.  The "mailing labels" field criteria in the query is set to "yes".  The "yes" field is cleared of "yes" when I click on the Mailing Labels command button on the main form by running the "qry Unmark Mailing Labels" query.  The tbl Mailing labels table just contains client info fields like FirstName, LastName, Address, City, etc.

    I wish I could explain more but I am just a novice at this.  But I'm learning fast. 

    Hope that helps


    Boochie

    Monday, August 20, 2012 12:15 AM
  • Ok,

    lets test this

    Behind the form button in Access

    Just have the following code

    Replace the WrdMrg path with your path and word document

    And see if it fires off your document.

    DoEvents (The following code opens word that has the tblTmpHepBMerge recordsource and merges)
      Dim WrdMrg
            WrdMrg = "Y:\Diseases\CDDBMailMerge\Hep_B_case_report.dotx"
            Shell "C:\WINDOWS\explorer.exe """ & WrdMrg & "", vbNormalFocus



    David

    Monday, August 20, 2012 12:19 AM
  • Okay, the entirety of my code looks like this:

    On Error GoTo Err_Mailing_label_button_Click

      
       
        DoCmd.SetWarnings False
       
        
        DoCmd.OpenQuery "qry Mailing labels", , acEdit
        DoCmd.TransferText acExportMerge, , "tbl Mailing labels", "c:\Mail Labels.txt"
        DoCmd.OpenQuery "qry Unmark Mailing Labels", , acEdit
       
        DoCmd.SetWarnings True

      
            DoEvents
            Dim WrdMrg
           
            WrdMrg = "C:\Temp\TEST ACCESS DATABASE LOOKUP REPORTER\Test Mailing Labels.dot"
            Shell "C:\WINDOWS\explorer.exe""" & WrdMrg & "", vbNormalFocus

       
    exit_Mailing_label_button_click:
        Exit Sub

    Err_Mailing_label_button_Click:
        MsgBox Error$
        Resume exit_Mailing_label_button_click

    And I still get the Error Message:  "Invalid procedure call or argument".

    One thought:  My previous code worked perfectly when using Word 2000 because the data source was actually embedded in the .dot template file.  When I ran the old code, the template opened up, I clicked on "merge" and the data was merged to the "Mail Labels.txt" file directly under my C Drive.  But when using Word 2007 (still the old code) the data source does not seem to get saved to the template, so each time the file opens, I have to continually re-attach  the .txt file.  Could this cause a problem?  So somewhere in the above code the data source needs to be referenced?

    Boochie

     


      End Sub


    Boochie

    Monday, August 20, 2012 9:51 PM
  • Hi,

    the way i construct a mailmerge is the following.

    Open the word template, click on mailmerge, then link back to the Access table or query that is going to be the the recordsource

    Save the template with the recordsource.

    Then behind a button on the Access form i put the following code, with the correct path and filename  in the following script Hep_B_case_report.dotx has the recordsource of tblTmpHepBMerge in my access database.

    DoEvents (The following code opens word that has the tblTmpHepBMerge recordsource and merges)
      Dim WrdMrg
            WrdMrg = "Y:\Diseases\CDDBMailMerge\Hep_B_case_report.dotx"
            Shell "C:\WINDOWS\explorer.exe """ & WrdMrg & "", vbNormalFocus



    I donot do this

    DoCmd.TransferText acExportMerge, , "tbl Mailing labels", "c:\Mail Labels.txt"


    David

    Monday, August 20, 2012 10:18 PM
  • You might like to take a look at the file AccWord.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This demonstrates various Access to Word automation operations, including mail merging.  The zip file includes both .mdb and .accdb files, along with the necessary Word documents.  The ReadMe file gives a brief explanation of how it works.

    I've just made a few small amendments and have updated the file in the SkyDrive folder.  It was originally written in Access 2000 as far as I recall, but seems to work in Access 2007.  The email address shown on the opening form no longer exists BTW.


    Ken Sheridan, Stafford, England

    Monday, August 20, 2012 10:57 PM
  • Thanks for the info on MS Access to MS Word 2007.  The problem is now resolved with the help of this link:

    http://support.microsoft.com/kb/825765/en-us

    When I made the registry change, my original code still works perfectly and now the data source is linked and active when I open the mail merge letter with the MS Access mail merge code.

    Boochie


    Boochie

    Saturday, August 25, 2012 8:19 PM