Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2009
    Berkshire, UK

    Unanswered: Help with mail merge

    I have created a database of allotment gardens, and use the following VBA (copied and modified from elsewhere) to generate an invoice for the allotment holder whose name is selected from a combo box in a form.

    The combo box displays the holder's name from a query, and returns the numeric HolderID which is used to select records for the mailmerge.

    It all works as intended, but I would like the merged Word document name to include the holder's name.

    I have no idea how to modify the code to extract this information from the database. Can anyone help please?

    TIA - Mike

    Private Sub cboHolderName_AfterUpdate()
        Dim strMergedDocName As String
        strMergedDocName = "Allotment Invoice " & Format(CStr(Date), "dd MMM yyyy")
        Dim objWord As Word.Document
        Set objWord = GetObject(CurrentProject.Path & "\templates\InvoiceTemplate.doc", "Word.Document")
        ' Make Word visible.
        objWord.Application.Visible = True
        ' Set the mail merge data source as the allotments database.
        objWord.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
        LinkToSource:=True, Connection:="QUERY qryInvoice", _
        SQLStatement:="SELECT * FROM [qryInvoice] WHERE qryInvoice.HolderID = " & cboHolderName.Value
        ' Execute the mail merge.
        objWord.Application.Documents(1).SaveAs (CurrentProject.Path & "\mailmerge\" & strMergedDocName & ".doc")
        objWord.Application.Documents(2).Close wdDoNotSaveChanges
        DoCmd.Close acForm, Me.Name
    End Sub

  2. #2
    Join Date
    Oct 2009
    as you have had no reply I will just add - - I use Access query to define the record set. I use Word to set up the WordMerge - using the query as the record source.

    I don't use Access to set up the WordMerge.

    After the document exists - then from Access it is easy to simply open the document - and that triggers the merge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts