Microsoft office 2013 Access and Word Mail Merge

My new favorite EE User Name :-)
Very creative !

LOL!  Its me because when people ask "Who is this?"  and Girl Friday (from the old days - the gal that could do everything).  It was my cb handle.

GGID ...
Girl Gets It Done ... a friend of my uses that.

Meanwhile ... I'm not really a Word guy ... well I mean I know a lot of words and how to use them, but interfacing Access with Word ... no so much.

Helen Feddema is the go to person on this ... hopefully she will drop in , or others.  I will try to entertain you in the meantime :-)

Have to say too, handle is great!

  My first question would be, on the other two machines, is the same edition of Office installed?  (32 or 64 bit).

 Second question; did you use a DSN?

Jim.

LOL!  You are all funny and brighten my day!

The extension to the Word data source is .odc

I found a work around, albeit it is not pleasant:

Open up document
Select Data source (.odc file)
When in Word Document, select Mail Merge
Select Existing Recipient (which is same .odc file)
Save Document

This has to be done one time for each document on each computer.  After this is done, document opens just fine with correct MSAccess data in the Word merge fields.

Houston we have a problem:  Eeek!  I have like 200 Word Documents.

Girl Friday (who obviously can't do EVERYTHING!!!!)

P.S.  Yes Jim, Everyone is using Office 2013....  64 bit os / 32 bit office.

I just realized it's not Friday yet and I need ... more coffee because it's only JoeTuesday !
And yesterday was Stormy Monday (If I had a dollar for every time I've played that song!)

Instead of using another database, could you use a text file?  That is what I do with mail merges.  It avoids the memory hogging of having the database open when the merge doc is opened, and since the text file is created anew (in the current folder) when the merge is run, you don't have to know where a database is.  Here is some code from the sample database for my Working with Word ebook.  The relevant portion (for your needs) is the saving of data to be merged to an Access table, which is then exported to a comma-delimited text file that is the mail merge data source:

Private Sub cmdCreateDocuments_Click()
'Created by Helen Feddema 30-May-2014
'Last modified by Helen Feddema 8-Apr-2015

On Error GoTo ErrorHandler

   Dim appWord As Word.Application
   Dim cbo As Access.ComboBox
   Dim doc As Word.Document
   Dim docMerge As Word.Document
   Dim fil As Scripting.File
   Dim fso As New Scripting.FileSystemObject
   Dim i As String
   Dim intCount As Integer
   Dim intDocCount As Integer
   Dim intReturn As Integer
   Dim intSaveNameFail As String
   Dim lngContactID As Long
   Dim lngRecordCount As Long
   Dim lngSelectCount As Long
   Dim rstMerge As DAO.Recordset
   Dim rstSource As DAO.Recordset
   Dim strAddress As String
   Dim strBookmark As String
   Dim strCompanyName As String
   Dim strContactName As String
   Dim strContactNameAndJob As String
   Dim strCountry As String
   Dim strDBPath As String
   Dim strDefaultDocsPath As String
   Dim strDefaultTemplatesPath As String
   Dim strDocsPath As String
   Dim strDocType As String
   Dim strJobTitle As String
   Dim strLongDate As String
   Dim strPhone As String
   Dim strProgressBarText As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strSalutation As String
   Dim strSaveDateFormat As String
   Dim strSaveName As String
   Dim strSaveNamePath As String
   Dim strShortDate As String
   Dim strTemplateName As String
   Dim strTemplateNameAndPath As String
   Dim strTemplatesPath As String
   Dim strTest As String
   Dim strTestFile As String
   Dim strTextFile As String
   
On Error Resume Next

   'Delete old merge data text file, if it exists
   strDBPath = Application.CurrentProject.Path & "\"
   strTextFile = strDBPath & "Merge Data.txt"
  'Debug.Print "Text file for merge: " & strTextFile
   
   Set fil = fso.GetFile(strTextFile)
   
On Error GoTo ErrorHandler
   
   If Not (fil Is Nothing) Then
      fil.Delete
   End If
   
   DoCmd.SetWarnings False
      
   'Check that a template has been selected
   Set cbo = Me![cboSelectDocument]
   strTemplateName = Nz(cbo.Value)
   If strTemplateName = "" Then
      strTitle = "No template selected"
      strPrompt = "Please select a template"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      cbo.SetFocus
      cbo.Dropdown
      GoTo ErrorHandlerExit
   Else
      Debug.Print "Template: " & strTemplateName
      strDocType = cbo.Column(1)
   End If
   
   strFilter = GetProperty("Filter", "")
   lngRecordCount = Nz(DCount("*", "qryMergeContacts"))
      
   'Check that a document has been selected
   Set cbo = Me![cboSelectDocument]
   strTemplateName = Nz(cbo.Column(0))
  'Debug.Print "Template: " & strTemplateName
   strFilter = GetProperty("Filter", "")
   lngRecordCount = Nz(DCount("*", "qryMergeContacts"))
   
   If strTemplateName = "" Then
      strTitle = "No document selected"
      strPrompt = "Please select a document"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      cbo.SetFocus
      cbo.Dropdown
      GoTo ErrorHandlerExit
   ElseIf strFilter = " " Then
      strTitle = "Question"
      strPrompt = "Create a merge document for all " & lngRecordCount _
         & " contacts?"
      intReturn = MsgBox(prompt:=strPrompt, _
         Buttons:=vbQuestion + vbYesNo, _
         Title:=strTitle)
      If intReturn = vbNo Then
         GoTo ErrorHandlerExit
      ElseIf intReturn = vbYes Then
         DoCmd.CopyObject newname:="qfltMergeContacts", _
            sourceobjecttype:=acQuery, _
            sourceobjectname:="qryMergeContacts"
      End If
   End If
   
   'Set Word application variable; if Word is not running,
   'the error handler defaults to CreateObject
   Set appWord = GetObject(, "Word.Application")
   
   strLongDate = Format(Date, "mmmm d, yyyy")
   strSaveDateFormat = GetProperty("SaveDateFormat", "")
   strShortDate = Format(Date, strSaveDateFormat)
   
   'Get selected Docs and Templates paths from database properties
   '(saved from controls on main menu)
   strDocsPath = GetDocsPath
   strTemplatesPath = GetTemplatesPath

   If strTemplatesPath = " " Then
      GoTo ErrorHandlerExit
   Else
      strTemplateNameAndPath = strTemplatesPath & strTemplateName
      'Debug.Print "Template name and path: " & strTemplateNameAndPath
   End If
   
On Error Resume Next
   'Check for existence of template in templates folder,
   'and exit if not found

   Set fil = fso.GetFile(strTemplateNameAndPath)
   If fil Is Nothing Then
      strTitle = "Template not found"
      strPrompt = "Can't find " & strTemplateName & " in " _
         & strTemplatesPath & "; canceling"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   End If
   
On Error GoTo ErrorHandler
   
   strTitle = "Information missing"
   strQuery = "qfltMergeContacts"
   
   Set rstSource = CurrentDb.OpenRecordset(strQuery)
   lngSelectCount = rstSource.RecordCount
   
   If lngSelectCount = 0 Then
      strTitle = "No records found"
      strPrompt = "Please select another filter"
      MsgBox prompt:=strPrompt, _
         Buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      GoTo ErrorHandlerExit
   Else
      strProgressBarText = "Creating merge document... "
      Call SysCmd(acSysCmdInitMeter, strProgressBarText, _
         lngSelectCount)
   End If
         
   'Clear tblMergeList and set up recordset based on it
   strTable = "tblMergeList"
   strSQL = "DELETE tblMergeList.* FROM tblMergeList;"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
  'Debug.Print "Opening recordset based on " & strTable
   Set rstMerge = CurrentDb.OpenRecordset(strTable, dbOpenTable)
   lngRecordCount = 0
    
   Do While Not rstSource.EOF
      lngContactID = rstSource![ContactID]
     'Debug.Print "On Contact ID: " & lngContactID
      
      'Set variables with data from different fields
      strContactName = Nz(rstSource![FirstName]) & _
         " " & Nz(rstSource![LastName])
      strJobTitle = Nz(rstSource![JobTitle])
      strCompanyName = Nz(rstSource![CompanyName])
      strPhone = Nz(rstSource![WorkPhone])
      strSalutation = Nz(rstSource![Salutation])
      strAddress = Nz(rstSource![StreetAddress]) & vbCrLf & _
         Nz(rstSource![City]) & ", " & _
         Nz(rstSource![StateOrProvince]) & _
         "  " & Nz(rstSource![PostalCode])
         
      'Write data from variables to a new record in table
      With rstMerge
         .AddNew
         ![Name] = strContactName
         ![JobTitle] = strJobTitle
         ![CompanyName] = strCompanyName
         ![Address] = strAddress
         ![Salutation] = strSalutation
         ![TodayDate] = strLongDate
         .Update
      End With
         
      lngRecordCount = lngRecordCount + 1
      Debug.Print "Updating progress bar for record " _
         & lngRecordCount & " of "; lngSelectCount & " records"
      Call SysCmd(acSysCmdUpdateMeter, lngRecordCount)

NextContact:
      rstSource.MoveNext
   Loop
   
   rstSource.Close
   rstMerge.Close
   
   'Export merge list to a text file (to avoid having to open the
   'database when the document is opened later on)
   DoCmd.TransferText transfertype:=acExportDelim, _
      TableName:=strTable, _
      FileName:=strTextFile, _
      HasFieldNames:=True
   
   'Open a new merge document based on the selected template
   Set doc = appWord.Documents.Add(strTemplateNameAndPath)
  'Debug.Print "New merge doc name: " & doc.Name
         
   'Check for existence of previously saved letter in documents folder,
   'and append an incremented number to save name if found
   strSaveName = strDocType & " on " & strShortDate & ".docx"
   i = 2
   intSaveNameFail = True
   Do While intSaveNameFail
      strSaveNamePath = strDocsPath & strSaveName
     'Debug.Print "Proposed save name and path: " _
         & vbCrLf & strSaveNamePath
      strTestFile = Nz(Dir(strSaveNamePath))
     'Debug.Print "Test file: " & strTestFile
      If strTestFile = strSaveName Then
        'Debug.Print "Save name already used: " & strSaveName
         
         'Create new save name with incremented number
         intSaveNameFail = True
         strSaveName = strDocType & " " & CStr(i) _
            & " on " & strShortDate & ".docx"
         strSaveNamePath = strDocsPath & strSaveName
        'Debug.Print "New save name and path: " _
            & vbCrLf & strSaveNamePath
         i = i + 1
      Else
        'Debug.Print "Save name not used: " & strSaveName
         intSaveNameFail = False
      End If
   Loop
   
  'Debug.Print "Final save name: " & strSaveNamePath
   
   'Set the merge data source to the text file just created,
   'and do the merge
   With doc
      .MailMerge.OpenDataSource Name:=strTextFile, _
         Format:=wdOpenFormatText
      
      If Nz(InStr(strTemplateName, "Label")) > 0 Then
         .MailMerge.MainDocumentType = wdMailingLabels
      Else
         .MailMerge.MainDocumentType = wdFormLetters
      End If
      
      .MailMerge.Destination = wdSendToNewDocument
      .MailMerge.Execute
      
      'Set another Document variable to the newly merged document,
      'to ensure that the correct document is saved
      Set docMerge = appWord.ActiveDocument
      .Close savechanges:=wdDoNotSaveChanges
   End With
   
   docMerge.SaveAs2 strSaveNamePath
   
   Call SaveMailingInfo(strDocType, CStr(lngRecordCount) & " contacts")
   
   If lngSelectCount = 1 Then
      strPrompt = strDocType & " created for 1 contact"
   Else
      strPrompt = strDocType & " created for " _
         & lngSelectCount & " contacts"
   End If
   
   MsgBox prompt:=strPrompt, _
      Buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle

   Call SysCmd(acSysCmdRemoveMeter)
   Call BringDocToFront(appWord, docMerge)
      
ErrorHandlerExit:
   Set appWord = Nothing
   Set fil = Nothing
   Exit Sub

ErrorHandler:
   If Err = 429 Then
      'Word is not running; open Word with CreateObject
      Set appWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " _
         & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Select allOpen in new window

If you do need to save the merge data to another database, you could put a folder path selector on the main menu of the database from which the merge is being done, so the path could be selected once, and then used in code as needed.  See my Access Archon article on the New Style Main Menu for this technique:

http://www.helenfeddema.com/Files/accarch212.zip

Here is a screen shot of this menu:

Thank you Helen for your time.  I did put a path to the word documents folder and the database path (a separate database from the main one) in a table so the code knows where to look.

My main problem is the .odc driver with the Mail Merge.  

When the word document is opened from another machine, it prompts for the "new data source."  This is ok as it only has to be done once, but a pain in the arse because there are over 200 word documents and 3 computers.

I think I am misunderstanding the data source connection somehow.

I am not sure why you would need an .odc driver.  My code assumes that you are working with the database that has the data to merge, and that Word is installed on the same computer.  Is that not the case?

As far as that message is concerned, this is the very situation that the text file method avoids.  The Word document is not connected directly to the database, just to the text file (as a merge data source).  Since you have the two paths stored, you could save the text file to either one as needed.

Valerie,

<<As far as that message is concerned, this is the very situation that the text file method avoids.  The Word document is not connected directly to the database, just to the text file (as a merge data source).  Since you have the two paths stored, you could save the text file to either one as needed. >>

 Another approach is from the Access side, do the mail merge and poke that data into Word documents directly.  Although it works well, it is a little more complex on the Access side; what's your coding skill level?

 I have code here, but I haven't used it in years....can't think of any reason it would not work however.  I believe Helen has some too that's a little more current.

 I've never worked with .odc connections on the Word side, so I can't offer anything there.

Jim.

Helen, I am working on another project today (I am self proprietor and have many clients).  I will look at your code24.zip file later.

QUESTION:  With all of your methods, what happens when the Word Document opens?  Does the user have to click the "Mail Merge button" and merge the data?  Or does the data already display on the Word Document via "mail merge fields"?

My client uses these "Mail Merge" documents multiple times every day and wants to be able to click a button one time, print the document with the current information displayed on the MSAccess screen, and then close the document and continue the work in MSAccess.  

Thank you for your time and information.  It is very much appreciated.  I have been programming in Access for 20+ years and have worked a lot with PDF files seamlessly (grabbing data from msaccess), but these Word Documents are biting my behiney.  

This is an old 2000 .mdb database that used bookmarks and did stuff the data into the word document using bookmarks, but the client was always frustrated because they couldn't insert new bookmarks, etc. or things got messed up with formatting, etc.

I would also like them to have the flexibility to create a new Word Document, Pick the same .odc data source  and insert whatever merge fields from the table I created.  I love to create databases, but I don't like to babysit!

Valerie aka ItsMeGirlFriday!

All of my methods create and merge the data, so the document (or documents) open filled with the data from Access.  It appears that you might need an actual merge linked to a database, which makes the situation more complex, at least if the data is frequently being updated, so a text file created from it will be out of date quickly.  If the data is static, though, a text file data source would work fine.

@Helen Feddema:  Yes, the data is updated continually...  so the text file would only be good for possibly 1-2 minutes.  The text file would have to be constantly re-created everytime they updated a record and printed a new word document (fax or correspondence).  It used to work perfectly in older versions of Microsoft Office when there was only one odbc driver from Control Panel.  Now there are 4 depending on which configuration of windows/office you have ie: 64-64, 64-32, 32-32 etc.

I will try to look later today or tomorrow at the code that you have in the code24.zip file.  

Thank you again for your help.  I greatly appreciate it.

My text file method works great for batch mailings, etc., where you need output from current data for a one-time mailing or whatever.  But if you need to continually update data and print one or two documents, it would be a problem when just working from Word.  If you are working from the Access database, however, that would not be a problem, since you could update the data and then do the text file merge, possibly selecting just a few records.  My Working with Word ebook has more choices than the old Four Ways to Merge to Word sample database, letting you use either a multi-select listbox or filter queries to select records for a merge.

I am sure the code will work, i just have to take the time to look at it.  Helen seems very knowledgeable.

Valerie, what about this solution:
- User opens .NET Windows Forms app
- User selects a Word template from available templates
- .NET app creates a document in background, by merging the data with chosen template
- Once generated, document opens in MS Word
- User prints the document and exits Word

I am using this kind of approach to generate custom reports from our IT Service Management application and it runs really well.

To create your solution you will need:
1. Third party toolkit, capable of creating Word documents. You have several choices, I am using Docentric Toolkit.
2. VisualStudio 2010 Professional or later.
3. MS Word 2007 or later.

Now you do the following:
1. Create .NET Windows Forms project, where you will define data model by creating classes with appropriate structure.
2. In .NET Windows Forms project you will then define methods which will populate these classes with data at runtime. Data can come from anywhere: database, XML, CSV, etc. You will probably also want to create configuration file where you will set parameters that may change over time: file paths, database connection strings, etc.
3. Create user interface where users will select report templates.
4. When you have the application ready you create templates in MS Word. You can use any formatting that MS Word supports. You insert placeholders where you want your data to appear at runtime. Placeholders can present single fields, lists (i.e. collections of data), pictures, charts or subdocuments. One interesting feature is also the ability to display or hide any part of the template based on conditional logic, which is defined in template itself.

The benefits that you will achieve by this approach are:
1. You can publish this application and templates on a server; no MS Word installation is necessary on the server.
2. Users can easily install this application on their local computers and are informed about any consequent updates when they are available. In most cases they don’t need administrator’s privileges to install or update the application.
3. Document generation is really fast and once everything is set up it runs without any problems.
4. When Office versions are updated, the application still runs without any need for change. In our environment our users have Office 2007, 2010 and 2013 and they all happily run our reporting application.
5. Adding or changing templates is easy: just add a new template to the folder or change an existing one.
6. Adding new data structure (via data model class) is also easy, once it is done, you just publish new version on the server and users are notified about it the next time they start the application.
7. Adding users is super easy: just install the application on user’s computer and off we go…
8. End users can create templates themselves. Docentric Toolkit has add-in for Word which allows business users to modify and/or create templates.

When we were setting things up, we received some training from Docentric and we received many useful tips from their support as well. I suggest you take the same approach. It really pays off very soon.

That is a interesting solution.  I will have to look into it.  Thank you for your kind reply.
Valerie