Word MailMerge Automation

[YellowLab]

I have been trying to automate Word MailMerge and am running into some difficulties. I found an old post, but can't figure out how to modify the code to get it work properly.

The original post is from 2012 and can be found here: https://www.autoitscript.com/forum/topic/141245-word-mail-merge-to-file/

Below is my script snippit:

#include <Word.au3>
Const $wdFormLetters   = 0
Const $wdSendToNewDocument  = 0
Const $wdSendToPrinter   = 1

$oWord=_Word_Create()
Dim $sWordMailMergeDoc = "C:\Users\XXX\Documents\My Programs\autoit 3_12\Scripts\DocReview\15-Review.doc"
Dim $oWordMailMergeDoc = _Word_DocOpen($oWord,$sWordMailMergeDoc)
Dim $sDataSourceFileName = "C:\Users\XXX\Documents\My Programs\autoit 3_12\Scripts\DocReview\15-000-Index.doc"
;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 '&$sDataSourceFileName
With $oWordMailMergeDoc.MailMerge
.MainDocumentType = $wdFormLetters
.Destination = $wdSendToPrinter
.OpenDataSource($sDataSourceFileName, 0, False, False, True, False, "", "", False, "", "", $sDataSourceConnectionString, $sSQLStatement, "", Default, 1)
.Datasource.FirstRecord = 1
.Datasource.LastRecord = 10
.Execute()
EndWith

My data source is a CSV saved in DOC format.

The script will open the merge document but won't link the list data to the file. My feeling is there is something wrong with the datasource string, but I don't know how to extract the exact datasource for my file.

[water]

Please have a look at the ADO tutorial in the wiki.
How to create a connection string for a text file is described here.

[water]

I'm not sure you can use a Word document as datasource. Could you specify the CSV file as datasource?

[YellowLab]

Awesome!

Thanks for the resource links.

Changing the source connection did the trick:

Dim $sDataSourceConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=' & $sDataSourceFileName & ';Mode=Read;Extended Properties="text;HDR=YES;FMT=CSVDelimited;"'

 

[YellowLab]

The word document (.DOC) as a comma separated document works. But that was an extra step (I was trying other things to get the script to work) and left it as a CSV. Works as intended.

[water]

Glad it was that easy

[lilaray7581]

So I modified this script to open the excel file first but can't figure out how to get it to open to a particular sheet within that file. Can someone, please, help me? I have tried different variations of adding the sheet activate ... but it ends up not being correct. 

expandcollapse popup

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Word.au3>
Const $wdLabels   = 0
Const $wdSendToNewDocument  = 0
Const $wdSendToPrinter   = 1

; Create application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox(64, "Merge App", "Error creating the Excel application object." & @CRLF & “Missing Excel application”)

; *****************************************************************************
; Open an existing workbook and enter details for label(s).
; *****************************************************************************
Local $sWorkbook = “file location“
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Merge App", "Error opening '" & $sWorkbook & "'." & @CRLF & "Missing necessary documents to run this application. Please contact IT Dept.")
MsgBox(64, "Merge Apps", "Fill out. Click OK once completed.")
; Close the workbook with saving
_Excel_BookClose($oWorkbook, True)

$oWord=_Word_Create()
Dim $sWordMailMergeDoc = “file location“
Dim $oWordMailMergeDoc = _Word_DocOpen($oWord,$sWordMailMergeDoc)
Dim $sDataSourceFileName = “file location xlsx“
;Get the exact data source connection string from your merge document
Dim $sDataSourceConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sDataSourceFileName & ';Mode=Read;Extended Properties="HDR=YES;IMEX=1;FMT=XLSX;'
Dim $sSQLStatement = 'SELECT * FROM `Merge App Data$`'
With $oWordMailMergeDoc.MailMerge
.MainDocumentType = $wdLabels
.Destination = $wdSendToPrinter
.OpenDataSource($sDataSourceFileName, 0, False, False, True, False, "", "", False, "", "", $sDataSourceConnectionString, $sSQLStatement, "", Default, 1)
.Datasource.FirstRecord = 1
.Datasource.LastRecord = 10
.Execute()
EndWith
_Word_Quit($oWord) ; close MS Word

 

Edited August 20, 2015 by lilaray7581

[water]

You need to modify the select statement. There you can specify the worksheet to be read. 

[lilaray7581]

Thank you for giving me the sense of direction! I got it to work. 

[water]