Jump to content
YellowLab

Word MailMerge Automation

Recommended Posts

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.


You can't see a rainbow without first experiencing the rain.

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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;"'

 


You can't see a rainbow without first experiencing the rain.

Share this post


Link to post
Share on other sites
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.


You can't see a rainbow without first experiencing the rain.

Share this post


Link to post
Share on other sites
water

Glad it was that easy :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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. 

#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 by lilaray7581

Share this post


Link to post
Share on other sites
water

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

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
lilaray7581

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

Share this post


Link to post
Share on other sites
water

:) 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×