locked
Using an Access Query as the source of a Word Mail Merge Letter RRS feed

  • Question

  • I have several Word documents that are set up for mail merge. The data source is an Excel workbook. Each worksheet in the workbook supports a different word mail merge document. Here's the thing. All of the data in the workbook is from the same table in an Access database. That's the way it was designed when I took over this process.

    I want to replace the workbook and all of those worksheets with just one query in the database. The query would need two values passed to it, County and Date. The results of the query would be used to complete the mail merge. I hope I explained this idea well enough. 

    I know how to use a query as a data source, and I know how to set up the query to prompt for the criteria. Is it possible to have the letter pass the two values to the query?

    tod

     

     
    Saturday, January 23, 2016 11:47 PM

Answers

  • I'm pretty sure I reinvented a very old, moss covered wheel, but just for the sake of completeness I am replying with my solution. 

    I already have a word document set up as a mail merge template. I entered this code in the document vb editor and saved the file as filename.docm. I have assigned the procedure to a shortcut key combination. So now I open the letter, hit the shortcut keys and it will prompt me for a date. Then it queries the database and uses the results to create the mail merged document. All I have to do is preview and print. 

    Hope this helps somebody. Also feel free to tell suggest improvements, alternatives, etc.

    Tod

    ---------------

    Sub MyDebtLetter()
        Dim dbPath As String
        Dim objDoc As Document
        Dim strSQL As String
        Dim StartDate
        Dim EndDate
        Dim SecondLetter As Boolean
        
    EnterUploadDate:
        StartDate = InputBox("Enter an upload date", , Date)
        If StartDate = "" Or Not IsDate(StartDate) Then
            If MsgBox("Value must be a date. Would you like to try again?" & vbCrLf & vbCrLf & "Click Yes to try again." & vbCrLf & "Click No to cancel this operation.", vbYesNo + vbInformation) = vbNo Then
                Exit Sub
            Else
                GoTo EnterUploadDate
            End If
        End If
        
        EndDate = DateAdd("d", 1, CDate(StartDate))

        dbPath = "C:\MyPath\MyDatabase.accdb"
        strSQL = "SELECT * FROM [tblCase] WHERE Case_Type = 'Debt' AND db_Record_Created >= #" & StartDate & "# And db_Record_Created < #" & EndDate & "#"

        Set objDoc = ActiveDocument
        
        objDoc.MailMerge.OpenDataSource SQLStatement:=strSQL, Name:=dbPath, _
            Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Mode=Read;Extended Properties="""""
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
        
          
    End Sub

    Sunday, January 24, 2016 10:51 PM

All replies

  • There are 2 ways - one is to use a form with text boxes or combo's that are filled in before you open the Word.  The query references the forms text boxes or combo's. 

    The other is just to have parameters in the query.

    It seems you need more than having multiple worksheets.


    Build a little, test a little

    Sunday, January 24, 2016 12:20 AM
  • The 1st option solves the first half of my problem.

    There are two parameters that need to be passed to the query. One is a date. Easy enough. The query prompts for a date when I open the letter and I just type in it. Great. The other parameter I want the letter to provide. I don't want to type it or choose it or have any manual intervention. Maybe some VBA code in the letter, or some other thing. 

    Any suggestions?

    tod

    Sunday, January 24, 2016 12:43 AM
  •  The other parameter I want the letter to provide. I don't want to type it or choose it or have any manual intervention.

    It would be impossible to have alternate text if you do nothing!    What kind of parameter?

    I would suggest having a table in Access with the text pre-typed in the records.   Have a combo box in the form to select the text.


    Build a little, test a little

    • Marked as answer by todtown Sunday, January 24, 2016 3:10 AM
    • Unmarked as answer by todtown Sunday, January 24, 2016 5:37 PM
    Sunday, January 24, 2016 3:00 AM
  • Thanx for the info. I am exploring a new approach. Using Access VBA to query the table with the desired criteria, open the specified letter and feed the query results to the bookmarks in the letter. My second idea is to use Word VBA to query the database table with the criteria, bring back the results in a recordset and try it that way. Don't know if it'll work, but...will see. 

    Build a little, test a little. I once heard someone say. ;0)

    tod

    • Edited by todtown Sunday, January 24, 2016 3:14 AM
    Sunday, January 24, 2016 3:11 AM
  • Sounds like a lot of unneccessarywork.

    Build a little, test a little

    Sunday, January 24, 2016 5:29 PM
  • Well I'm doingitanyway,

    I found this code using VB to control the mail merge. I've modified it to work within the document. 

    I'll also try the code within the database, attached to a form. The user can control the criteria from the form and launch the mail merge from there. 

    tod

    Sunday, January 24, 2016 5:41 PM
  • I'm pretty sure I reinvented a very old, moss covered wheel, but just for the sake of completeness I am replying with my solution. 

    I already have a word document set up as a mail merge template. I entered this code in the document vb editor and saved the file as filename.docm. I have assigned the procedure to a shortcut key combination. So now I open the letter, hit the shortcut keys and it will prompt me for a date. Then it queries the database and uses the results to create the mail merged document. All I have to do is preview and print. 

    Hope this helps somebody. Also feel free to tell suggest improvements, alternatives, etc.

    Tod

    ---------------

    Sub MyDebtLetter()
        Dim dbPath As String
        Dim objDoc As Document
        Dim strSQL As String
        Dim StartDate
        Dim EndDate
        Dim SecondLetter As Boolean
        
    EnterUploadDate:
        StartDate = InputBox("Enter an upload date", , Date)
        If StartDate = "" Or Not IsDate(StartDate) Then
            If MsgBox("Value must be a date. Would you like to try again?" & vbCrLf & vbCrLf & "Click Yes to try again." & vbCrLf & "Click No to cancel this operation.", vbYesNo + vbInformation) = vbNo Then
                Exit Sub
            Else
                GoTo EnterUploadDate
            End If
        End If
        
        EndDate = DateAdd("d", 1, CDate(StartDate))

        dbPath = "C:\MyPath\MyDatabase.accdb"
        strSQL = "SELECT * FROM [tblCase] WHERE Case_Type = 'Debt' AND db_Record_Created >= #" & StartDate & "# And db_Record_Created < #" & EndDate & "#"

        Set objDoc = ActiveDocument
        
        objDoc.MailMerge.OpenDataSource SQLStatement:=strSQL, Name:=dbPath, _
            Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Mode=Read;Extended Properties="""""
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
        
          
    End Sub

    Sunday, January 24, 2016 10:51 PM
  • Hi tod,

    I am glad your issue has been resolved, I suggest you mark your reply as answer, and then others who run into the same issue would find the solution easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 25, 2016 3:20 AM