Results 1 to 14 of 14
  1. #1
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31

    Automatically running Word Mailmerge from Access VBA


    I have a properly running mailmerge in Word 2010. When I open the document, it prompts me to run the mailmerge. I want to open up the document from within Access 2010 programmatically and have to mailmerge run automatically. However when I open the document from within Access no only do I not get the prompt, the mailmerge does not run either. My research came up with a solution as outlined in Microsoft KB 825765. Is this my only option?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you already looked at this site: http://www.kallal.ca/msaccess/msaccess.html
    Scroll down to Super Easy Word Merge

  3. #3
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31
    Sorry, I am looking for a simple syntax example in VBA. I am not interested in merging anyone else's code in with mine.

  4. #4
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31

    A little progress

    Quote Originally Posted by pledbetter View Post
    Sorry, I am looking for a simple syntax example in VBA. I am not interested in merging anyone else's code in with mine.
    I was able to get a code snippet from http://support.microsoft.com/kb/209976/en-us to "sort of" get me where I need go. But when I run the code, the following happens:

    When I try this approach, Word does not open up. But when I open my mailmerge document manually,
    1. I get a prompt "The command cannot be performed because a dialog box is open". "Click OK, and then close open dialog boxes to continue"
    2. When I click OK, I get the prompt to run the query at the data source" Opening this document will run the following SQL command: SELECT * FROM [My Table]
    3. I get 2 instances of the completed mailmerge document: my original and another titled "Form Letters1". Both copies are correct, but they do not come up automatically, and require the manual running of the SELECT statement.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe trusted documents issue. You can view your settings from Backstage>Options>TrustCenter>TrustCenterSettings> TrustedDocuments

    Also, you can add a path via Trusted Locations.

  6. #6
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164
    I'm not sure exactly what sort of output you are looking for or how your code looks (it might help if you posted your code here) but I have a couple mail merge features I use regularly and they work beautifully. I don't have to close any dialogue boxes. I took the advice of some other, more talented, Access dBase administrators and I generate a .txt document and then use that as my merge record source. It may seem like a needless step but it really helped me out. I'll post some examples of this code below.

    Here is the code I use to generate the .txt file:
    Code:
    ' Defining our file paths and other constants
    ID= Me.ID.Value ' how we find the record we want in the Query table
    Name= Me.Name ' 
    docName = Name & " " & Format(Date, "mm-dd-yyyy") & ".txt" ' name of txt file for mail merge
    Sourcename = "YOUR_FILEPATH_HERE" & docName ' full file name with path
    strSQL = "SELECT * FROM [TABLE] WHERE [ID] = " & ID& ";" ' Selecting just a single record from the general query
    
    ' Committing this record to the query.
    ' This could mean the form will go to a different record that the one we are trying to generate
    ' documents for but that wont matter as we already have the IDnumber for the record we want
    Me.Requery
    DoCmd.GoToRecord , "", acLast
    
    
    
    NewQ:
    ' Generating specific query for this participant.  This query and the document it creates will be used
    ' for generating the Memo 
        Set qd = New DAO.QueryDef
        qd.SQL = strSQL
        qd.Name = "MailMerge"
        CurrentDb.QueryDefs.Append qd
        
    'Exporting this query as a basic text file
        DoCmd.TransferText acExportDelim, , "MailMerge", Sourcename, True
    
    'Cleaning up our query
        CurrentDb.QueryDefs.Delete "MailMerge"
        qd.Close
        Set qd = Nothing
    And then the actual Mail Merge:
    Code:
    Sub myMerge(Sourcename, strMonth, strMemoPath, lName)
    Dim temp1 As String, W As Word.Application, Wtemp As Word.Document
    ' Opening Word and merging the data for Memo
        temp1 = "FilePath\Memo Template - Accessish.dot"
        Set W = CreateObject("Word.Application")
        W.Visible = True
        Set Wtemp = W.Documents.Open(temp1)
        Wtemp.MailMerge.OpenDataSource Sourcename, , , , False
        Wtemp.MailMerge.ViewMailMergeFieldCodes = wdToggle
    ' This part of the code copies the merged document info into a new document
    ' This is intended to allow transimssion of the document without retaining
    ' the connection to the original data source.
        Wtemp.Select
        W.Selection.WholeStory
        W.Selection.Copy
        W.Documents.Add , , wdNewBlankDocument, True
        W.Selection.PasteAndFormat wdFormatOriginalFormatting
        W.ActiveDocument.SaveAs2 strMemoPath & lName & " Memo " & strMonth & " " & Year(Date) & ".docx", _
                                 wdFormatXMLDocument, False, "", True, "", False, False, False, False, False, 14
        'W.ActiveDocument.Close
        Wtemp.Close wdDoNotSaveChanges
        Set Wtemp = Nothing
        strMemoPath = ""
    End Sub
    I hope this helps or at least gives you an idea of some things to try out.

  7. #7
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31

    Almost done

    [QUOTE=Monterey_Manzer;206110]I'm not sure exactly what sort of output you are looking for or how your code looks (it might help if you posted your code here) but I have a couple mail merge features I use regularly and they work beautifully. I don't have to close any dialogue boxes. I took the advice of some other, more talented, Access dBase administrators and I generate a .txt document and then use that as my merge record source. It may seem like a needless step but it really helped me out. I'll post some examples of this code below.<br>
    <br>Yes, I had to check the "Trust Access to the VBA project object model" in Trust Center - Macro Settings in my Word Options in order to get the merge to be executed from within Access.<br><br>
    The only output I am looking for is my mail merge document opened with the fields filled out properly as I've embedded them in my document.&nbsp; The data source is a table with a single row in it that is cleared out upon database start and appended to just before the mail merge is to start.&nbsp; All that works fine.&nbsp; I can do a manual mail merge from the Word document just fine.&nbsp; The mail merge is initiated from the same database in which the data source exists.&nbsp; After some experimentation, I've come up with this code:


    Private Sub RunMailMerge()
    Dim objWord As Word.Document
    Set objWord = GetObject("C:\MCLAP\Buyer Agency - Mailmerge.docx", "Word.Document")

    ' Make Word visible.
    objWord.Application.Visible = True

    ' Set the mail merge data source
    objWord.MailMerge.OpenDataSource _
    Name:="C:\MCLAP\Current.accdb", _
    LinkToSource:=True, _
    Connection:="TABLE tblBAData", _
    SQLStatement:="SELECT * FROM [tblBAData]"

    ' Execute the mail merge
    objWord.MailMerge.Execute
    End Sub


    When I run this code, the following happens:

    1. I get the prompt to run the query. I can live with that.
    2. I get two copies of a correctly mail-merged document: my original document and another titled "Form Letters1"

    How can I get one or the other NOT to display? Not just not be "not visible", but not to come up at all? I found out by trial and error that the document can be "there" but simply not visible, which can bite you if you are not aware of that "feature"

  8. #8
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    164
    So the query you have to run appends the data you want to the table you then use for the mail merge? I would think you could code something like that in to function automatically using the QueryDef code in the first snippet I provided.

    However, as for the two mail merge documents, I'm not sure where the second "Form Letters1" document is coming from but, as long as the name remains consistent, you could simply have your Access code close it. You could declare a variable for the Word application in addition to the document and use the document index to find and close that document. Something like this:
    Code:
    Dim W as Word.Application
    Set W = CreateObject("Word.Application")
    W.Documents(2).Close
    Not ideal but it's a place to start. You could just try modifying the index number or get more creative and loop through both documents and check the Name property (W.Documents(index).Name) using something like:
    Code:
    For i = 1 To W.Documents.Count
          If InStr(1,W.Documents(i).Name,"Form Letters1",vbTextCompare) > 0 Then ' if the title has Form Letters1 in it close the window
                   W.Documents(i).Close wdSaveNo
          End If
    Next i
    I hear you on the invisible thing! I do a lot with Excel while it's invisible but the problem with hidden workbooks crops up often enough that I wrote a simple function to make set both Word and Excel visible properties to True.

  9. #9
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31
    Quote Originally Posted by Monterey_Manzer View Post
    So the query you have to run appends the data you want to the table you then use for the mail merge? I would think you could code something like that in to function automatically using the QueryDef code in the first snippet I provided.

    However, as for the two mail merge documents, I'm not sure where the second "Form Letters1" document is coming from but, as long as the name remains consistent, you could simply have your Access code close it. You could declare a variable for the Word application in addition to the document and use the document index to find and close that document. Something like this:
    Code:
    Dim W as Word.Application
    Set W = CreateObject("Word.Application")
    W.Documents(2).Close
    Not ideal but it's a place to start. You could just try modifying the index number or get more creative and loop through both documents and check the Name property (W.Documents(index).Name) using something like:
    Code:
    For i = 1 To W.Documents.Count
          If InStr(1,W.Documents(i).Name,"Form Letters1",vbTextCompare) > 0 Then ' if the title has Form Letters1 in it close the window
                   W.Documents(i).Close wdSaveNo
          End If
    Next i
    I hear you on the invisible thing! I do a lot with Excel while it's invisible but the problem with hidden workbooks crops up often enough that I wrote a simple function to make set both Word and Excel visible properties to True.


    I have no idea where "Form Letters1 comes from. I'm guessing it is some sort of Word default? It's the two instances od Word that bugs me. I don't know why that is happening when I am only calling up one document. I will try your approach with the add-on that I will check for the existence of the extra document first, just in case a different OS/Office combination does not create the 2nd document. Thanks!

  10. #10
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31
    The other thing about this mysterious document is that when I take it and do a "Save As", it does not try to save it as "Form Letters1" It tries to save it as the default (whatever the first sentence is on the document). When I cancel, the document now takes on the name "Letters 1", but again when I try to do a "Save As", it again tries to take on a title that is merely a regurgitation of the very first sentence on the document. When I added your w.Documents(2).Close method, I get a "Object doesn't support this property or method" error.

  11. #11
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31
    Ok, I found that if I modify your code to W.Application.Documents(2).Close it works.

    I'm also wondering if Access was trying to tell me that it created a Form called "Letters1", and somehow involved Word with it? So many "undocumented features", so little time!


    Now I just need to figure out how open the document as a PDF, and initially naming it with the client's ID!

  12. #12
    pledbetter is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2010
    Location
    Martinsville, IN
    Posts
    31
    OK so here's the solution I came up with:

    Private Sub RunMailMerge()
    Dim txtMailMergeFile As String
    Dim objWord As Word.Document
    Dim i As Integer
    txtMailMergeFile = DLookup("txtFilePath", "tblConstants") & DLookup("txtBAMailMergeFile", "tblConstants")

    'Set objWord = GetObject("C:\MCLAP\Buyer Agency - Mailmerge.docx", "Word.Document")
    Set objWord = GetObject(txtMailMergeFile, "Word.Document")

    ' Make Word visible.
    objWord.Application.Visible = True

    ' Set the mail merge data source
    objWord.MailMerge.OpenDataSource _
    Name:="C:\MCLAP\Current.accdb", _
    LinkToSource:=True, _
    Connection:="TABLE tblBAData", _
    SQLStatement:="SELECT * FROM [tblBAData]"

    ' Execute the mail merge
    objWord.MailMerge.Execute

    ' Close any "extra" mail merge documents that may open
    For i = 1 To objWord.Application.Documents.Count - 1
    objWord.Application.Documents(i).Close wdSaveNo
    Next i
    End Sub



    I have a single-row table called "tblConstants" that I use to store, obviously, constants like file paths, file names, and other things not likely to change much and are used throughout the database. Obviously I need to put in some error trapping, but this works pretty well for me. I never found a way for the datasource SELECT query to run automatically without the prompt and without having to do a registry hack but I can live with the prompt. I also realized that knowing the name of the "extra" mail merge document was irrelevant. I just wanted one to be visible. So I simply closed all but one of the existing Word documents. I suppose if you actually HAD a Word document you were using at the time it might close that too, but for me, it is not likely.



    Thanks to all who helped me with this.

  13. #13
    jimskier2 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Posts
    1

    Letter1

    Quote Originally Posted by pledbetter View Post
    I have no idea where "Form Letters1 comes from. I'm guessing it is some sort of Word default? It's the two instances od Word that bugs me. I don't know why that is happening when I am only calling up one document. I will try your approach with the add-on that I will check for the existence of the extra document first, just in case a different OS/Office combination does not create the 2nd document. Thanks!
    The first document is your Merge Template which you must not change unless you the letter needs changing. Letter1 is the finished product.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A flurry of late, late responses to old posts this month! Interesting. You probably didn't notice that the last post is over 4 years old. Beginning to think that threads should be demarcated somehow when the last post is beyond a certain age but the OP doesn't mark it as solved.
    Then again, perhaps you intended to respond, in which case, this isn't the same as what's been happening.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with new computer running Word 2013
    By rankhornjp in forum Programming
    Replies: 15
    Last Post: 03-06-2014, 11:17 AM
  2. Access and mailmerge in Word
    By Popnorth in forum Access
    Replies: 2
    Last Post: 01-21-2013, 08:25 AM
  3. Mailmerge: Making a list - Access to word
    By Ribido in forum Access
    Replies: 3
    Last Post: 01-07-2012, 12:40 PM
  4. Replies: 1
    Last Post: 11-30-2011, 07:06 PM
  5. Help Automatically running and Update Query
    By JohnRandolphSTL in forum Queries
    Replies: 8
    Last Post: 04-13-2010, 02:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums