none
constructing a filename in a mail merge document

    Question

  • Hopefully I'm not duplicating a previous question but I've looked through and I can't find an exact solution.
    I have an Access 2003 database which feeds a single record into a mail merge document in Word 2007. This produces a contract letter which I then want to save with a filename that reflects the contract reference printed.
    One field in the mail merge table is called “ContractRef” and I want to incorporate this into the saved file name "C:\Contracts\Contract Letter 1.docx" instead of the figure 1.

    I'd appreciate info on how I get the macro (listed below) to recognise the "ContractRef" in the underlying table and then use it to create the file name.

    Thanks in huge anticipation

    John
     

    Sub AutoOpen()
    '
    ' macAutoOpen Macro
    '
    '
    Documents.Open FileName:="""C:\Contracts\Contract - Organisation.doc""", _
    ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    wdOpenFormatAuto, XMLTransform:=""
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    End With
    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
    wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
    ManualDuplexPrint:=False, Collate:=False, Background:=True, PrintToFile:= _
    False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
    PrintZoomPaperHeight:=0
    ActiveDocument.SaveAs FileName:="C:\Contracts\Contract Letter 1.docx", FileFormat:= _
    wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
    :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
    :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False
    ActiveDocument.Close
    End Sub
    Thursday, November 25, 2010 2:50 PM

Answers

  • In this scenario I have to assume that the ContractRef is the same on all the records selected for merge. If so, the following may work, but I haven't verified that /a/ record is current at this point in the process. Something like...

    Dim strContractRef as String

    .

    .

    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord

    strContractRef = cstr(.DataFields("ContractRef").Value

    End With

    .

    .

    ActiveDocument.SaveAs FileName:="C:\Contracts\Contract Letter " & strContractRef & ".docx", etc.

     

    NB, in this case AFAICR the column name is case sensitive and has to match exactly - i.e. "contractref" won't do it.



    Peter Jamieson
    • Marked as answer by casrhinos Monday, November 29, 2010 1:57 PM
    Thursday, November 25, 2010 5:33 PM
  • Hi John

    I've done some editing to the sample code you provided, but didn't test. You'll notice I declared objects for the main merge document and the result document. This is a bit more efficient than relying on ActiveDocument throughout.

    In addition, I've included a variable for the information you want to extract and demonstrate how to populate it, then apply it to the file name. Please note that the index value for DataFields is case-sensitive - you have to enter the field name exactly as it appears in the list of Merge fields.

    Sub MMTest()
      Dim doc As word.Document, MMDoc As word.Document
      Dim recInfo As String
      
      Set doc = Documents.Open(FileName:="""C:\Contracts\Contract - Organisation.doc""", _
    ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    wdOpenFormatAuto, XMLTransform:="")
    
      doc.MailMerge.ViewMailMergeFieldCodes = wdToggle
      With doc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
          recInfo = .DataFields("NameOfField").value
          .FirstRecord = wdDefaultFirstRecord
          .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
      End With
      Set MMDoc = ActiveDocument
      MMDoc.PrintOut
    MMDoc.SaveAs FileName:="C:\Contracts\Contract Letter " & recInfo & ".docx", FileFormat:= _
    wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
    :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
    :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False
    MMDoc.Close
    
    End Sub
    

    Cindy Meister, VSTO/Word MVP
    • Marked as answer by casrhinos Monday, November 29, 2010 1:57 PM
    Thursday, November 25, 2010 5:38 PM

All replies

  • In this scenario I have to assume that the ContractRef is the same on all the records selected for merge. If so, the following may work, but I haven't verified that /a/ record is current at this point in the process. Something like...

    Dim strContractRef as String

    .

    .

    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord

    strContractRef = cstr(.DataFields("ContractRef").Value

    End With

    .

    .

    ActiveDocument.SaveAs FileName:="C:\Contracts\Contract Letter " & strContractRef & ".docx", etc.

     

    NB, in this case AFAICR the column name is case sensitive and has to match exactly - i.e. "contractref" won't do it.



    Peter Jamieson
    • Marked as answer by casrhinos Monday, November 29, 2010 1:57 PM
    Thursday, November 25, 2010 5:33 PM
  • Hi John

    I've done some editing to the sample code you provided, but didn't test. You'll notice I declared objects for the main merge document and the result document. This is a bit more efficient than relying on ActiveDocument throughout.

    In addition, I've included a variable for the information you want to extract and demonstrate how to populate it, then apply it to the file name. Please note that the index value for DataFields is case-sensitive - you have to enter the field name exactly as it appears in the list of Merge fields.

    Sub MMTest()
      Dim doc As word.Document, MMDoc As word.Document
      Dim recInfo As String
      
      Set doc = Documents.Open(FileName:="""C:\Contracts\Contract - Organisation.doc""", _
    ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    wdOpenFormatAuto, XMLTransform:="")
    
      doc.MailMerge.ViewMailMergeFieldCodes = wdToggle
      With doc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
          recInfo = .DataFields("NameOfField").value
          .FirstRecord = wdDefaultFirstRecord
          .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
      End With
      Set MMDoc = ActiveDocument
      MMDoc.PrintOut
    MMDoc.SaveAs FileName:="C:\Contracts\Contract Letter " & recInfo & ".docx", FileFormat:= _
    wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
    :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
    :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False
    MMDoc.Close
    
    End Sub
    

    Cindy Meister, VSTO/Word MVP
    • Marked as answer by casrhinos Monday, November 29, 2010 1:57 PM
    Thursday, November 25, 2010 5:38 PM
  • Thank you Peter I'll give it a try when back in work on Monday

    Really appreciate your help

    Friday, November 26, 2010 1:45 PM
  • Thank you Cindy I'll also give this a try when back in work on Monday

    Really appreciate your help

    Friday, November 26, 2010 1:46 PM
  • Thanks very much to the both of you. This works a treat now.

    Many thanks

    John

    Monday, November 29, 2010 1:57 PM