Hi
I am currently working on developing a mail merge process for some letters that need to be sent out.
I have a list of Jobs with each one being assigned to a specific group (there are 20 groups in total). I also have a table with a list of people linked to these jobs, as each job can have multiple people linked to it i use the code below to define a query prior to the merge (this part works fine).
so the query looks like this:
Job - FamilyName -FamilyID - Forename - Surname
1 - Group 1 - 1- Jo - Bloggs
2 - Group 2 - 2 - abc - abc
3 - Group 1 - 1 - xyz - xyz
1 - Group 1 - 1 - qwe - rty
The letters have the exact same name as the "FamilyName" field. and i use the FamilyID to loop through the records.
at the moment the merge opens 4 word documents (1 for each line, so would open 3 versions of the Group 1 letter), what i need it to do is to open 1 instance of each letter (so with the one above i would want only 2 documents to open). Is there a way to adapt the code below to ignore the line if the document is already open, or a way to tell it to only open each document once?
At present this gives me a document ready to be merged, While on the subject, is there a way to automate the actual merge action though the same VBA code?
This is the code im currently using:
Dim db As Database
Dim rec As Recordset
Dim qdf As QueryDef
Dim strSQL As String
Dim intId As Integer
Dim strExportTo As String
Dim VarFolder As Variant
Set db = CurrentDb()
Set rec = db.OpenRecordset("TblTempMailMerge")
Set qdf = db.QueryDefs("MergeTest")
Do While Not rec.EOF
'Sets the fields to be used within the mail merge dataset
intId = rec.Fields("FamilyID")
strExportTo = DLookup("[LetterLocation]", "TblExportPaths") & rec.Fields("FamilyName") & ".xls"
strSQL = " SELECT TblTempMailMerge.JERef, TblTempMailMerge.ServiceUnit, TblTempMailMerge.JobTitle, TblTempMailMerge.Forename, TblTempMailMerge.Surname, TblTempMailMerge.Points, TblTempMailMerge.FamilyName, TblTempMailMerge.FamilyID" & _
" FROM TblTempMailMerge" & _
" WHERE (((TblTempMailMerge.FamilyID)=" & intId & "));"
qdf.SQL = strSQL
' opens up the letter to be used in the merge.
VarFolder = DLookup("[LetterLocation]", "TblExportPaths")
Set app = CreateObject("word.Application")
app.Visible = True
app.Documents.Open VarFolder & rec.Fields("FamilyName") & ".doc"
rec.MoveNext
Loop
rec.Close
Set db = Nothing
Set rec = Nothing
Set qdf = Nothing
Any advice on ways to get this to work is appreciated.
I am currently working on developing a mail merge process for some letters that need to be sent out.
I have a list of Jobs with each one being assigned to a specific group (there are 20 groups in total). I also have a table with a list of people linked to these jobs, as each job can have multiple people linked to it i use the code below to define a query prior to the merge (this part works fine).
so the query looks like this:
Job - FamilyName -FamilyID - Forename - Surname
1 - Group 1 - 1- Jo - Bloggs
2 - Group 2 - 2 - abc - abc
3 - Group 1 - 1 - xyz - xyz
1 - Group 1 - 1 - qwe - rty
The letters have the exact same name as the "FamilyName" field. and i use the FamilyID to loop through the records.
at the moment the merge opens 4 word documents (1 for each line, so would open 3 versions of the Group 1 letter), what i need it to do is to open 1 instance of each letter (so with the one above i would want only 2 documents to open). Is there a way to adapt the code below to ignore the line if the document is already open, or a way to tell it to only open each document once?
At present this gives me a document ready to be merged, While on the subject, is there a way to automate the actual merge action though the same VBA code?
This is the code im currently using:
Dim db As Database
Dim rec As Recordset
Dim qdf As QueryDef
Dim strSQL As String
Dim intId As Integer
Dim strExportTo As String
Dim VarFolder As Variant
Set db = CurrentDb()
Set rec = db.OpenRecordset("TblTempMailMerge")
Set qdf = db.QueryDefs("MergeTest")
Do While Not rec.EOF
'Sets the fields to be used within the mail merge dataset
intId = rec.Fields("FamilyID")
strExportTo = DLookup("[LetterLocation]", "TblExportPaths") & rec.Fields("FamilyName") & ".xls"
strSQL = " SELECT TblTempMailMerge.JERef, TblTempMailMerge.ServiceUnit, TblTempMailMerge.JobTitle, TblTempMailMerge.Forename, TblTempMailMerge.Surname, TblTempMailMerge.Points, TblTempMailMerge.FamilyName, TblTempMailMerge.FamilyID" & _
" FROM TblTempMailMerge" & _
" WHERE (((TblTempMailMerge.FamilyID)=" & intId & "));"
qdf.SQL = strSQL
' opens up the letter to be used in the merge.
VarFolder = DLookup("[LetterLocation]", "TblExportPaths")
Set app = CreateObject("word.Application")
app.Visible = True
app.Documents.Open VarFolder & rec.Fields("FamilyName") & ".doc"
rec.MoveNext
Loop
rec.Close
Set db = Nothing
Set rec = Nothing
Set qdf = Nothing
Any advice on ways to get this to work is appreciated.
