Hi,
without boring you with the background, I am trying to run a mail merge from the excel data source, rather than from a word document. I have a table similar to the one below (but much larger), and a number of word documents that use the same mail merge fields. the table below and the two associated Word documents(Letter1 and Letter2) are a smaller version of the problem I'm trying to solve.
The table:
File Record name job title
1 Bill Buffalo
2 Jim mechanic
3 Bob The Builder
4 Sam Son of
5 Mary Technician
6 Jane Line Manager
7 Karen BP
8 Adam MIS Analyst
I have a list box that allows the user to select one of the "File Record" values and which letter (1 or 2) to open. The correct letter opens, and the mail merge opens successfully; however, it doesn't select the one record I want, but instead I have to go through the process of selecting a record again.
In the code below, I'm certain that I just need to add the correct "Where" clause to the Select query, but the syntax is defeating me:
Any suggestion would be gratefully received. I have been trawling through different forums and the internet, but without much success
without boring you with the background, I am trying to run a mail merge from the excel data source, rather than from a word document. I have a table similar to the one below (but much larger), and a number of word documents that use the same mail merge fields. the table below and the two associated Word documents(Letter1 and Letter2) are a smaller version of the problem I'm trying to solve.
The table:
File Record name job title
1 Bill Buffalo
2 Jim mechanic
3 Bob The Builder
4 Sam Son of
5 Mary Technician
6 Jane Line Manager
7 Karen BP
8 Adam MIS Analyst
I have a list box that allows the user to select one of the "File Record" values and which letter (1 or 2) to open. The correct letter opens, and the mail merge opens successfully; however, it doesn't select the one record I want, but instead I have to go through the process of selecting a record again.
In the code below, I'm certain that I just need to add the correct "Where" clause to the Select query, but the syntax is defeating me:
Code:
Private Sub Run_Click()
Dim Record As String
Record = Me.MailMerger
Dim LetterChoice As String
LetterChoice = Me.Letter
Unload Me
Set appWd = CreateObject("Word.Application")
appWd.Visible = True
Set DocWD = appWd.Documents.Open(Filename:="C:\ATR Logs\mail Merge\" & LetterChoice & ".Doc")
DocWD.MailMerge.OpenDataSource Name:="C:\ATR Logs\mail Merge\Log.xls" _
, ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\ATR Logs\mail Merge\Log.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine T" _
, SQLStatement:="Select * from `Sheet1$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
DocWD.MailMerge.ViewMailMergeFieldCodes = wdToggle
End SubAny suggestion would be gratefully received. I have been trawling through different forums and the internet, but without much success
