Word Mail Merge to File

[StaticTank]

On a daily basis I need to merge documents to a file and then send them to one of our production printers. I don't want to send them directly to the printer for a number of reasons so I would prefer they were printed to file.

I have it working on a limited basis using VBA and send keys but it is extremely unreliable. Here is the code that I am using for that:

'Active record is set to the last record
myMerge.DataSource.ActiveRecord = wdLastRecord

'Record the last record for confirmation
intFinalRecord = myMerge.DataSource.ActiveRecord

'Display the number of records for confirmation
MsgBox "There are " & intFinalRecord & " Records"

'Set active record to record one
myMerge.DataSource.ActiveRecord = wdFirstRecord

Set myMerge = ActiveDocument.MailMerge

With myMerge
    .Destination = wdSendToPrinter
    SendKeys "{ENTER}", False
    SendKeys "C:test" & strLETTER_NAME & " " & strMAILING_TYPE _
        & " " & strLETTER_HEAD_TYPE & " " & strPRINT_TYPE & ".prn", False
    SendKeys "{ENTER}", False
    .Execute
End With
    
ActivePrinter = strPName
Application.Quit False

However due to the unreliability of send keys and the need to more fully automate it, I wrote an extensive script that will locate all files modified today and populate a form with them and a check box for each one so that I can select which file to merge. I wanted to include the functionality to merge them with Autoit rather than my VBA send keys macro.

Some other code that I have experimented with is MrMitchell's _WordDocPrint2. This code works great to print to file but it only prints the active letter in the merge I haven't figured a way to make a mail merge print to file.

expandcollapse popup

Func _WordDocPrint2(ByRef $o_object, $f_Background = 0, $i_Copies = 1, $i_Orientation = -1, $f_Collate = 1, $s_Printer = "", $i_Range = 0, $i_From = "", $i_To = "", $s_Pages = "", $i_PageType = 0, $i_Item = 0, $f_PrintToFile = 0, $s_OutputFileName = "")
    If Not IsObj($o_object) Then
        __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_InvalidDataType")
        Return SetError($_WordStatus_InvalidDataType, 1, 0)
    EndIf
    ;
    If Not __WordIsObjType($o_object, "document") Then
        __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_InvalidObjectType")
        Return SetError($_WordStatus_InvalidObjectType, 1, 0)
    EndIf
    ;
    Local $s_ActivePrinter, $i_Extended, $i_DocOrientation = "", $i_ErrorStatusCode = $_WordStatus_Success, $s_ErrorMSG = ""

    Switch $i_Range
        Case 3
            If Not $i_From Or Not $i_To Then
                __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_InvalidValue", _
                        "When $i_Range is set to 3, then you must specify $i_From and $i_To.")
                Return SetError($_WordStatus_InvalidValue, 7, 0)
            EndIf
        Case 4
            If Not $s_Pages Then
                __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_InvalidValue", _
                        "When $i_Range is set to 4, you must specify $s_Pages.")
                Return SetError($_WordStatus_InvalidValue, 7, 0)
            EndIf
    EndSwitch

    $i_Orientation = String($i_Orientation)
    If $i_Orientation <> "-1" Then
        Switch $i_Orientation
            Case "0", "1"
                $i_DocOrientation = String($o_object.PageSetup.Orientation)
                If $i_DocOrientation <> $i_Orientation Then
                    $o_object.PageSetup.Orientation = $i_Orientation
                EndIf
            Case Else
                __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_InvalidValue")
                Return SetError($_WordStatus_InvalidValue, 4, 0)
        EndSwitch
    EndIf

    ; Setup internal error handler to Trap COM errors, turn off error notification
    Local $status = __WordInternalErrorHandlerRegister()
    If Not $status Then __WordErrorNotify("Warning", "_WordDocPrint", _
            "Cannot register internal error handler, cannot trap COM errors", _
            "Use _WordErrorHandlerRegister() to register a user error handler")
    Local $f_NotifyStatus = _WordErrorNotify() ; save current error notify status
    _WordErrorNotify(False)

    If $s_Printer Then
        $s_ActivePrinter = $o_object.Application.ActivePrinter
        $o_object.Application.ActivePrinter = $s_Printer
        If @error = $_WordStatus_ComError And $WordComErrorNumber = -2147352567 And $WordComErrorDescription = "There is a printer error." Then
            $i_ErrorStatusCode = $_WordStatus_InvalidValue
            $s_ErrorMSG = "Invalid printer name specified."
            $i_Extended = 6
        EndIf
    EndIf

    $i_From = String($i_From)
    $i_To = String($i_To)
    If Not $i_ErrorStatusCode Then
;~    $o_object.PrintOut ($f_Background, 0, $i_Range, "", $i_From, $i_To, $i_Item, $i_Copies, $s_Pages, $i_PageType, 0, $f_Collate)
        $o_object.PrintOut ($f_Background, 0, $i_Range, $s_OutputFileName, $i_From, $i_To, $i_Item, $i_Copies, $s_Pages, $i_PageType, $f_PrintToFile, $f_Collate)
        If @error = $_WordStatus_ComError Then
            $i_ErrorStatusCode = $_WordStatus_ComError
        EndIf
    EndIf

    If $i_DocOrientation <> "" And $i_DocOrientation <> $i_Orientation Then
        $o_object.PageSetup.Orientation = $i_DocOrientation
    EndIf
    If $s_ActivePrinter Then
        $o_object.Application.ActivePrinter = $s_ActivePrinter
    EndIf

    ; restore error notify and error handler status
    _WordErrorNotify($f_NotifyStatus) ; restore notification status
    __WordInternalErrorHandlerDeRegister()

    Switch $i_ErrorStatusCode
        Case $_WordStatus_Success
            Return SetError($_WordStatus_Success, 0, 1)
        Case $_WordStatus_InvalidValue
            __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_InvalidValue", $s_ErrorMSG)
            Return SetError($_WordStatus_InvalidValue, $i_Extended, 0)
        Case $_WordStatus_ComError
            __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_ComError", "There was an error while executing the 'PrintOut' Method.")
            Return SetError($_WordStatus_ComError, 0, 0)
        Case Else
            __WordErrorNotify("Error", "_WordDocPrint", "$_WordStatus_GeneralError", "Invalid Error Status - Notify Word.au3 developer")
            Return SetError($_WordStatus_GeneralError, 0, 0)
    EndSwitch
EndFunc   ;==>_WordDocPrint

I have also tried the following code as well to no avail:

local $data = "C:Laser MergeFirstClassDataLetterX.txt"
local $MailMerge

$oWord = _WordCreate("C:Laser MergeFirstClassLetterX.doc",0,0)
$oWordDoc = _WordDocGetCollection($oWord, 0)

$MailMerge = $oWordDoc.MailMerge
$MailMerge.OpenDataSource ('"' & $data & '"', 0, true, true, true, false, "", "", false)
$MailMerge.Destination = 1 ; New document. Not sure what other destinations can be used here...
$MailMerge.execute

Any assistance or advice would be appreciated. I have tried to look up what any more of the Word Coms I might be able to use to do this but I haven't been able to locate any that are helpful. I just feel like I am running in circles and not getting anywhere.

Thanks,

StaticTank

Edited June 7, 2012 by StaticTank

[water]

This VB code solved the mailmerge problem of a guy on a german forum. Should be easy to transfer it to AutoIt.

Dim WordApp As Word.Application
     Dim WordDatei As Word.Document
     WordApp = New Word.Application()
     WordDatei = WordApp.Documents.Open("C:\testdok.doc")
     WordDatei.MailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters
     WordDatei.MailMerge.OpenDataSource(Name:= _
         "C:\testcsv.csv", _
         ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
         AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
         WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
         Format:=Word.WdOpenFormat.wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _
         :="", SubType:=Word.WdMergeSubType.wdMergeSubTypeOther)
     With WordDatei.MailMerge
         .Destination = Word.WdMailMergeDestination.wdSendToNewDocument
         .SuppressBlankLines = True
         With .DataSource
             .FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
             .LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
         End With
         .Execute(Pause:=False)
     End With
     WordDatei.Close()
     WordApp.Visible = True

The Mailmerge object for Word 2007 can be found here.

Can't test it at the moment so I don't know what the VB code does in detail. Maybe - if the weather is bad - I will find some time tomorrow.

[StaticTank]

I know a bit about VB code and that is similar to what I have seen when trying to find code of any type to do what I am looking to do. Unfortunately all that does is send the merge to a new document. This Code is doing basically the same thing as this:

local $data = "C:Laser MergeFirstClassDataLetterX.txt"
local $MailMerge

$oWord = _WordCreate("C:Laser MergeFirstClassLetterX.doc",0,0)
$oWordDoc = _WordDocGetCollection($oWord, 0)

$MailMerge = $oWordDoc.MailMerge
$MailMerge.OpenDataSource ('"' & $data & '"', 0, true, true, true, false, "", "", false)
$MailMerge.Destination = 1 ; New document. Not sure what other destinations can be used here...
$MailMerge.execute

Only he is using some different options. I have tried to manipulate "$MailMerge.Destination = 1", "$MailMerge.Destination = wdSendToNewDocument",or event "$MailMerge.Destination =

wdSendToPrinter

". But I am afraid it isn't working.

Technically speaking I am merging to a printer but I want it to print(the merge) to a file.

I haven't really dealt with printing to a new document and then printing that to a file (which I could possibly do). However being that I need to do this with multiple files I am not sure how to deal with knowing when the merge to a new document is finished to start the print to file.

I wish there was a way like in the

_WordDocPrint2 to just print the merge straight to the file...

StaticTank

This VB code solved the mailmerge problem of a guy on a german forum. Should be easy to transfer it to AutoIt.

Dim WordApp As Word.Application
     Dim WordDatei As Word.Document
     WordApp = New Word.Application()
     WordDatei = WordApp.Documents.Open("C:testdok.doc")
     WordDatei.MailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters
     WordDatei.MailMerge.OpenDataSource(Name:= _
         "C:testcsv.csv", _
         ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
         AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
         WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
         Format:=Word.WdOpenFormat.wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _
         :="", SubType:=Word.WdMergeSubType.wdMergeSubTypeOther)
     With WordDatei.MailMerge
         .Destination = Word.WdMailMergeDestination.wdSendToNewDocument
         .SuppressBlankLines = True
         With .DataSource
             .FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
             .LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
         End With
         .Execute(Pause:=False)
     End With
     WordDatei.Close()
     WordApp.Visible = True

The Mailmerge object for Word 2007 can be found here.

Can't test it at the moment so I don't know what the VB code does in detail. Maybe - if the weather is bad - I will find some time tomorrow.

Edited June 7, 2012 by StaticTank

[MrMitchell]

I ran this and it opened the merge document, ran the merge for record 1 only, and merged to a new document. I was also able to change that one line to merge directly to printer without creating a new document. I know you don't want that, but it's possible anyhow.

You can run it yourself to see what the title of the new Word window will be after the merge so you can interact with it (print to file, save, etc...). Don't really know what else to do!

Make sure you correctly modify $sWordMailMergeDoc and $sSQLStatement and if you have trouble using your own connection string just copy your own connection string directly in place of $sDataSourceConnectionString in the .OpenDataSource method.

I'm using Word 2007 (with my Word merge doc and Excel spreadsheet saved in 2003 format) and it worked for me.

#include <Word.au3>
Const $wdFormLetters   = 0
Const $wdSendToNewDocument  = 0
Const $wdSendToPrinter   = 1
Dim $sWordMailMergeDoc = 'C:tempMailMergeDoc.doc'
Dim $oWordApp = _WordCreate($sWordMailMergeDoc)
Dim $oWordMailMergeDoc = $oWordApp.ActiveDocument
Dim $sDataSourceFileName = 'C:tempMailMergeExcelSource.xls'
;Get the exact data source connection string from your merge document
Dim $sDataSourceConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=' & $sDataSourceFileName & ';Mode=Read;Extended Properties="HDR=YES;IMEX=1;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0'
Dim $sSQLStatement = 'SELECT * FROM `Sheet1$`'
With $oWordMailMergeDoc.MailMerge
.MainDocumentType = $wdFormLetters
.Destination = $wdSendToNewDocument  ;This can also be $wdSendToPrinter to send to your default printer
.OpenDataSource($sDataSourceFileName, 0, False, False, True, False, "", "", False, "", "", $sDataSourceConnectionString, $sSQLStatement, "", Default, 1)
.Datasource.FirstRecord = 1
.Datasource.LastRecord = 1
.Execute()
EndWith

[StaticTank]

Unfortunately, I need to be able to merge a random number of letters each day. The amounts of each letter varies as well. So if I were to merge to a new file and then print the file to the printer there is a delay when word is still printing. How would I know when it is finished. That is why I was going the route I was going with merging to a file directly so once that is done then it would continue on instead of having to wait and know when word is done merging.

When Word merges to a file if the file was 68,000 records it would be some time before I would be able to send it to the printer and then close the document and start on the next letter. I could send it to the printer but I want to name it myself since I am adding some descriptive info to the file name. These are not little printers, they are large production printers. The printer operators need to see some info about the job when it gets to the printer. Printing directly out of word also just names it "Microsoft Word - *file name*". Also I may want to move it to a new printer and it is easier to send the file myself when I want to move it or send different jobs to multiple printers.

I ran this and it opened the merge document, ran the merge for record 1 only, and merged to a new document. I was also able to change that one line to merge directly to printer without creating a new document. I know you don't want that, but it's possible anyhow.

You can run it yourself to see what the title of the new Word window will be after the merge so you can interact with it (print to file, save, etc...). Don't really know what else to do!

Make sure you correctly modify $sWordMailMergeDoc and $sSQLStatement and if you have trouble using your own connection string just copy your own connection string directly in place of $sDataSourceConnectionString in the .OpenDataSource method.

I'm using Word 2007 (with my Word merge doc and Excel spreadsheet saved in 2003 format) and it worked for me.

#include <Word.au3>
Const $wdFormLetters   = 0
Const $wdSendToNewDocument  = 0
Const $wdSendToPrinter   = 1
Dim $sWordMailMergeDoc = 'C:tempMailMergeDoc.doc'
Dim $oWordApp = _WordCreate($sWordMailMergeDoc)
Dim $oWordMailMergeDoc = $oWordApp.ActiveDocument
Dim $sDataSourceFileName = 'C:tempMailMergeExcelSource.xls'
;Get the exact data source connection string from your merge document
Dim $sDataSourceConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=' & $sDataSourceFileName & ';Mode=Read;Extended Properties="HDR=YES;IMEX=1;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0'
Dim $sSQLStatement = 'SELECT * FROM `Sheet1$`'
With $oWordMailMergeDoc.MailMerge
.MainDocumentType = $wdFormLetters
.Destination = $wdSendToNewDocument  ;This can also be $wdSendToPrinter to send to your default printer
.OpenDataSource($sDataSourceFileName, 0, False, False, True, False, "", "", False, "", "", $sDataSourceConnectionString, $sSQLStatement, "", Default, 1)
.Datasource.FirstRecord = 1
.Datasource.LastRecord = 1
.Execute()
EndWith

[MrMitchell]

Ok so if you use VBA Word will fire an event when the merge is complete but I don't know how to catch it using COM. Need help with that from someone who knows Word very well. The event name is MailMergeAfterMerge.

[water]

Please have a look at my There is an example how to catch events with Outlook.

Haven't done it before but I think it has to be similar in Word.

[MrMitchell]

I'll look, thanks.

I just ran a test merge for 3000 records. It seems my script actually waited for the merge to complete before moving on to the next line. So now I don't know if the wait is somehow built-in to the .execute method...the OP and I seem to have different results.