none
Mail Merge without opening doc. RRS feed

  • Question

  • Hey,

    I'm attempting to use C# to do a mail merge. I've got a docx with all the templating ready to go. I've got a csv file with the data.

    I open the file with 

      oDataDoc = wrdApp.Documents.Open(ref oName, ref oMissing,
                            ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                            ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                            ref oFalse, ref oMissing, ref oMissing, ref oMissing,
                            ref oMissing);

    The first problem is that this actually opens the file on the users computer using their Word application. I'd rather not do that and only "open for editing" inside of my code. Is there a different open call I should be making?

    I do my mail merge like 

     wrdSelection = wrdApp.Selection;
     wrdMailMerge = oDataDoc.MailMerge;
     wrdMailMerge.Destination = Microsoft.Office.Interop.Word.WdMailMergeDestination.wdSendToNewDocument;
     wrdMailMerge.Execute(ref oFalse);

    This generates a new file that is also opened on the users machine. The original docx as well as the csv are both saved on a server so what I'd like to do is save the newly created doc in the same location then download it to the users machine. Instead the file is opening on the users machine to begin with, and I don't know how to save it to the server location.

    I've been googling this but haven't found the right answer yet. Any insight would be appreciated.

    Thank you.

     


    Thursday, November 30, 2017 1:30 PM

All replies

  • Opening the Word document on the user's machine is the least of your worries; once your code opens it anywhere it will stall while waiting for the user to respond to the mailmerge SQL prompt. In VBA that would be handled along the lines of:

    Sub RunMerge()
    Dim wdApp As New Word.Application, wdDoc As Word.Document, i As Long
    Dim strWkBkNm As String, StrFldr As String, StrNm As String, j As Long
    'Illegal filename characters
    Const StrNoChr As String = """*./\:?|"
    strWkBkNm = ThisWorkbook.FullName: StrFldr = ThisWorkbook.Path & "\"
    With wdApp
      'Visible = True for debugging purposes
      .Visible = False
      'Disable alerts to prevent an SQL prompt
      .DisplayAlerts = wdAlertsNone
      'Open the mailmerge main document
      Set wdDoc = .Documents.Open(StrFldr & "Mail Merge Main Document.docx", _
        ConfirmConversions:=False, ReadOnly:=True, AddToRecentFiles:=False)
      With wdDoc
        With .MailMerge
          'Define the mailmerge type
          .MainDocumentType = wdFormLetters
          'Define the output
          .Destination = wdSendToNewDocument
          'Connect to the data source, Applying the required SQL Statement,
          'including filters, on the 'SQLStatement' line and the
          ''SQLStatement1' line, for SQL Statements over 255 characters
          .OpenDataSource Name:=strWkBkNm, ReadOnly:=True, _
            AddToRecentFiles:=False, LinkToSource:=False, _
            Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "User ID=Admin;Data Source=StrWkBkNm;" & _
            "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
            SQLStatement:="SELECT * FROM `Sheet1$`", _
            SQLStatement1:="", SubType:=wdMergeSubTypeAccess
          .SuppressBlankLines = True
          'Process each record
          For i = 1 To 10 '.DataSource.RecordCount
            With .DataSource
              .FirstRecord = i
              .LastRecord = i
              .ActiveRecord = i
              'Get the output filename
              StrNm = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
            End With
            'Remove specified illegal characters from the file name
            For j = 1 To Len(StrNoChr)
              StrNm = Replace(StrNm, Mid(StrNoChr, j, 1), "_")
            Next
            StrNm = Trim(StrNm)
            'Create the mailmerge output for this record
            .Execute Pause:=False
            'The output document will automatically be the 'active' one
            With wdApp.ActiveDocument
              .SaveAs Filename:=StrFldr & StrNm & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
              ' and/or:
              .SaveAs Filename:=StrFldr & StrNm & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
              'Close the saved output file
              .Close SaveChanges:=False
            End With
          Next i
          'Disconnect from the data source
          .MainDocumentType = wdNotAMergeDocument
        End With
        'Close the mailmerge main document
        .Close SaveChanges:=False
      End With
      'Restore the Word alerts
      .DisplayAlerts = wdAlertsAll
      'Quit Word
      .Quit
    End With
    Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub

    The above code starts a new Word instance, then hides it from the user, via '.Visible = False'. The code also uses '.DisplayAlerts = wdAlertsNone' to suppress the mailmerge SQL prompt, but that also disconnects the document from the data source. Consequently, the connection must be re-established with the relevant SQLStatement (in the above case, getting all records from an Excel worksheet in the workbook the macro is run from). As written, the merge outputs a separate document and/or pdf for each record processed, named after the assumed 'Last_Name' & 'First_Name' datafields in the source.

    I'll leave it to you to do the C# conversion of whatever parts of the code you need.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, November 30, 2017 9:23 PM
  • If you want to avoid using Word altogether during the merge process, I suggest you look at Eric White's approach, which uses the Open Xml SDK rather than the Word Object model. I think the starting point is here: http://ericwhite.com/blog/documentassembler-developer-center/ . It would not be a simple substitution of one technique for another - you would have to modify your document and data.

    Peter Jamieson

    Friday, December 1, 2017 8:36 AM