Excel Macro with MSword Mail Merge using 2d Array

ylafont · #1 07-28-2021, 02:17 PM

Not sure where to post this but here goes

First, thank you in advance for the feedback, second i am not programmer but putting my best foot forward.

I have created a VBA macro that reads an excel table and creates a few 2D arrays out of the initial table. (This is in excel). I had the macro working perfectly using an excel table as the datasource for a MSword Mail Merge - partial code below.

I have a MS word document properly formatted containing all the merge fields (thought this may make this easier) and want to use one of the created arrays as the mail merge Data source, actually only need the last record to be merged. is there a method of using the Array as a data source? or should a different method be used? Any assistance is greatly appreciated, thank you in advance.

Code:

 
    objDoc.MailMerge.MainDocumentType = wdFormLetters
   objDoc.MailMerge.OpenDataSource Name:= _
    "C:\Program Files\Excel\Data\ExcelDataFile.xlsm", _
    ConfirmConversions:=False, _
    ReadOnly:=True, _
    LinkToSource:=True, _
    AddToRecentFiles:=False, _
    PasswordDocument:="", _
    PasswordTemplate:="", _
    WritePasswordDocument:="", _
    WritePasswordTemplate:="", _
    Revert:=False, _
    Format:=wdOpenFormatAuto, _
    Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Program Files\Excel\Data\ExcelDataSource.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:", _
    SQLStatement:="SELECT * FROM `ProductionRequest$`", SQLStatement1:="", _
    SubType:=wdMergeSubTypeAccess
    'objDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord
    objDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord
    objDoc.MailMerge.ViewMailMergeFieldCodes = wdToggle
    'objDoc.MailMerge.Execute

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge - Create seperate Mail merge pdf for each sheets of an excel file	sureshbvs	Word VBA	1	03-12-2021 05:42 AM
Need macro to fill the values in msword based on the excel sheet info	ganesang	Word VBA	50	08-29-2018 12:53 AM
Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA	RMerckling	Mail Merge	16	05-17-2018 05:19 PM
Mail Merge Macro	ch1325	Word VBA	2	06-08-2015 06:18 AM
msword email merge doc as pdf	pablo Isl	Mail Merge	3	06-09-2010 07:38 AM