Results 1 to 2 of 2
  1. #1
    AlexTheGr8 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Location
    Ontario
    Posts
    11

    Mail Merge one record to Work

    Hi All,

    I'm having difficulty finding resources on doing this in MS Access 2013 to MS Word 2013.

    I have a database with records on clients. In the database there is a form where the staff can use drop downs to select the clients information. I want to create a button that when pressed will send the information of only that one record to a Word document mail merge that is already set up. It's important that I have a formatted Word form already set up that it goes to because formatting is important to staff.

    I have a query built that already filters by the drop down box, but when I try to connect from Access to Word (whether to existing or non existing word document) it gives me the error that the folder where the front end is split can't be found. I tried doing this in the back end with a query and it connected no issue. Can mail merges only exist in non-split databases? Or should I unsplit it, and do my mail merge query building, and then re-split it when it's done?


    Thanks!


    Alex

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I recommend against using an Access query as the mail merge source; instead export the query to a delimited txt file (I use currentproject.path to get the local front-end folder).

    Here is an example of how I used to do this:
    Code:
    Public Sub vcKillMailMerge(Optional strFolder As String)
        Dim strFileName As String
        Dim iFolderCount As Integer
        Dim strFolders() As String
        Dim i As Integer
        Dim strFilePattern As String
        
        On Error Resume Next
        
        If strFolder = "" Then strFolder = Forms![MainSwitchboardForm]![MailMergeFolder]
        
        strFilePattern = "*ACCDB_MailMerge*"
         'Collect child folders
        strFileName = Dir$(strFolder & "\", vbDirectory)
        Do Until strFileName = ""
            If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
                If Left$(strFileName, 1) <> "." Then
                    ReDim Preserve strFolders(iFolderCount)
                    strFolders(iFolderCount) = strFolder & "\" & strFileName
                    iFolderCount = iFolderCount + 1
                End If
            End If
            strFileName = Dir$()
        Loop
         'process files in current folder
        strFileName = Dir$(strFolder & "\" & strFilePattern)
        Do Until strFileName = ""
            '*******************************************
            Kill strFolder & "\" & strFileName
             '*******************************************
            strFileName = Dir$()
        Loop
    End Sub
    
    Private Sub vcMailMerge()
    
    Dim iFileCount As Integer
    Dim strMailMergeFolder as string
    
    
    strMailMergeFolder = nz(Forms![MainSwitchboardForm]![MailMergeFolder],Application.CurrentProject.Path)
    
    
    vcKillMailMerge (strMailMergeFolder )
    
    If Right(strMailMergeFolder, 1) <> "\" Then strMailMergeFolder = strMailMergeFolder & "\"
    iFileCount = 1
    On Error GoTo Error_Kill
    Kill_File:
    If Len(Dir(strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt")) > 0 Then Kill strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt"
    GoTo relink
    
    
    Error_Kill:
    iFileCount = iFileCount + 1
    GoTo Kill_File
    
    relink:
         DoCmd.TransferText acExportDelim, , "Letter Source", strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt", True
         RelinkDocMailMergeText strMailMergeFolder & "ACCDB_MailMerge" & iFileCount & ".txt", strDocument, ReadOnlyMode, True
    
    End SUb
    
    Public Sub RelinkDocMailMergeText_Student(strMailMergeFileName As String, strDoc As String, boReadOnlyMode As Boolean, Optional boEmail As Boolean)
    Dim WordApp As Object
    Dim retcode
    Dim strFileName
    
    
    'the original sub is a lot bigger as it deals with saving to pdf, emailing , embedded pictures....
    Dim strDocumentName As String, sSubject As String, sBody As String
    
    
    On Error Resume Next
    
    
    
    
    strFileName = strDoc
    
    
    If Dir(Nz(strFileName, "")) = "" Then
        MsgBox strFileName & " was not found!  Is it hidden?", vbExclamation, "Document not in this folder!"
        Exit Sub
    End If
    
    
    
    
    Set WordApp = CreateObject("Word.Application")
             
    With WordApp
              .Application.Visible = True
    '          On Error GoTo telluser    
              .StatusBar = "Preparing to add a new Mail-Merge document in Word format.  Please wait..."
              .Documents.Open strFileName, ReadOnly:=boReadOnlyMode , AddToRecentFiles:=False, Revert:=True
    
    
        .ActiveDocument.MailMerge.OpenDataSource Name:= _
            strMailMergeFileName, ConfirmConversions:=False, ReadOnly:= _
            False, LinkToSource:=True, Revert:=True, AddToRecentFiles:=False
        .ActiveDocument.ActiveWindow.View.ShowFieldCodes = False
            
        .ActiveDocument.MailMerge.SuppressBlankLines = True         
        .ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False   
        .ActiveDocument.MailMerge.DataSource.ActiveRecord = -4    
        .ActiveDocument.MailMerge.Destination = 0                   'Merge to a New Document
    
    
      .Activate
        
    End If
        
    WORD_NOTHING:
    End With
    On Error Resume Next
    
    
    DoCmd.SetWarnings True
    'WordApp.Quit
    Set WordApp = Nothing
    Exit Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 2
    Last Post: 07-27-2019, 10:02 AM
  2. Replies: 5
    Last Post: 10-10-2014, 03:07 PM
  3. Help with Mail Merge please ?
    By bellevue in forum Forms
    Replies: 21
    Last Post: 02-16-2013, 07:05 AM
  4. Trying to get mail merge code to work
    By itm in forum Programming
    Replies: 0
    Last Post: 09-13-2012, 01:01 PM
  5. Mail merge
    By grgerhard in forum Forms
    Replies: 0
    Last Post: 04-25-2006, 05:06 PM

Tags for this Thread

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