Mail Merge help

[goodmanjl531]

i run a mail merge on a gew word documents using an excel file as my source.

 

I update the excel sheet first then save, then i open the word document click YES for the use my excel document and run SQL  Then i will go to Mailings and click Finish & merge-->Edit new documents  then copy all lines from the new document and dump into a simple text editor to finish

 

I tried below code with no luck..

 

expandcollapse popup

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

;   S:\IT\NEW-EPOS\Xstore-Image\XStoreBase\mnts\Tax\ NA TAX Tables.xlsx

; 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 = "S:\IT\NEW-EPOS\Xstore-Image\XStoreBase\mnts\Tax\NA TAX Tables.xlsx"     ;“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 =  "S:\IT\NEW-EPOS\Xstore-Image\XStoreBase\mnts\Tax\3.0-Tax_authority.docx"              ;“file location“
Dim $oWordMailMergeDoc = _Word_DocOpen($oWord,$sWordMailMergeDoc)
Dim $sDataSourceFileName =  "S:\IT\NEW-EPOS\Xstore-Image\XStoreBase\mnts\Tax\"; NA TAX Tables.xlsx"         ;“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 = $wdSendToNewDocument
.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

ANy ideas on how this could be automated ?   I have 5 word docs to run the mail merge in.

 

Thanks in advance 

Edited February 2, 2023 by goodmanjl531

[GMK]

"I tried below code with no luck.."  You'll have to be more specific.  Do you get error messages?  What happens when you run the code?  Where is it failing?