Mail merge with word

[Guest]

I have a databse where I've set up a mail merge squence to output certain
records to a word document that's already created. This worked fine when my
database was in Access '97 but we've upgraded to Access 200 and now it
doesn't work. I keep getting told that there's an error and that the object
isn't defined.

This is my merging code;
Private Sub MergeIt()
Dim objWord As Word.Document

Set objWord =
GetObject("\\UM-UABSYS\Literature\LitLists\RefListMergeTemplate.doc",
"Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource
Name:="\\S:\UM-LABSYS\Literature\LitLists\LiteratureList_XP.mdb", _
LinkToSource:=True, Connection:="TABLE TempMerge",
SQLStatement:="SELECT References.Author, References.Year, References.Title,
References.[Publisher_Report_to], References.[In], References.Journal,
References.Volume, References.Edition, References.Pages FROM References,
TempMerge WHERE References.ID = TempMerge.ref_id"

objWord.MailMerge.Execute
End Sub


Private Sub cmdMerge_Click()

Dim i As Integer
Dim strSQL As String
Dim first_time_through As Boolean

i = 1

'delete previous records
DoCmd.RunSQL "delete * from TempMerge"

'strSQL = "insert into TempMerge values ("
first_time_through = True

DoCmd.SetWarnings False

While i < lstReportReferences.ListCount

strSQL = "insert into TempMerge values (" &
lstReportReferences.ItemData(i) & ");"
DoCmd.RunSQL strSQL

'If first_time_through Then
' strSQL = strSQL & lstReportReferences.ItemData(i)
' first_time_through = False
'Else
' strSQL = strSQL & "," & lstReportReferences.ItemData(i)
' End If

i = i + 1
Wend

DoCmd.SetWarnings True

'strSQL = strSQL & ";"
'DoCmd.RunSQL strSQL

Call MergeIt

End Sub

I got this from the Microsoft website sometime ago and it was modified by
someone who knows what they are doing. My basic question is why does this no
longer work? I've checked the path name of the Word document and it's
correct, I really can't figure this one out.

If anyone can help I would be grateful

 

[Guest]

Actually, I've just tried to type in the code that Albert D. Kallall has on
his website and I'm being told that my 'MergeAllWord' object is not defined,
so I'm not sure what's going on. I'm trying to get records from a specific
table merged with word based on a selection made by the user in a combobox,
the extremely horrible code I originally posted did this (whilst opening
several copies of Access at the same time) and after having read Alberts
website I now understand why.

What I've now got for my code is as follows

Me.Refresh

MergeAllWord ("Select References.Author, References.Year, References.Title,
References.[Publisher_Report_to], References.[In], References.Journal,
References.Volume, References.Edition, References.Pages FROM References,
TempMerge WHERE References.ID = TempMerge.ref_id")

(Much better than the stuff below) but I am getting the error message. Any
ideas why?

diddydi

I have a databse where I've set up a mail merge squence to output certain
records to a word document that's already created. This worked fine when my
database was in Access '97 but we've upgraded to Access 200 and now it
doesn't work. I keep getting told that there's an error and that the object
isn't defined.

This is my merging code;
Private Sub MergeIt()
Dim objWord As Word.Document

Set objWord =
GetObject("\\UM-UABSYS\Literature\LitLists\RefListMergeTemplate.doc",
"Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource
Name:="\\S:\UM-LABSYS\Literature\LitLists\LiteratureList_XP.mdb", _
LinkToSource:=True, Connection:="TABLE TempMerge",
SQLStatement:="SELECT References.Author, References.Year, References.Title,
References.[Publisher_Report_to], References.[In], References.Journal,
References.Volume, References.Edition, References.Pages FROM References,
TempMerge WHERE References.ID = TempMerge.ref_id"

objWord.MailMerge.Execute
End Sub


Private Sub cmdMerge_Click()

Dim i As Integer
Dim strSQL As String
Dim first_time_through As Boolean

i = 1

'delete previous records
DoCmd.RunSQL "delete * from TempMerge"

'strSQL = "insert into TempMerge values ("
first_time_through = True

DoCmd.SetWarnings False

While i < lstReportReferences.ListCount

strSQL = "insert into TempMerge values (" &
lstReportReferences.ItemData(i) & ");"
DoCmd.RunSQL strSQL

'If first_time_through Then
' strSQL = strSQL & lstReportReferences.ItemData(i)
' first_time_through = False
'Else
' strSQL = strSQL & "," & lstReportReferences.ItemData(i)
' End If

i = i + 1
Wend

DoCmd.SetWarnings True

'strSQL = strSQL & ";"
'DoCmd.RunSQL strSQL

Call MergeIt

End Sub

I got this from the Microsoft website sometime ago and it was modified by
someone who knows what they are doing. My basic question is why does this no
longer work? I've checked the path name of the Word document and it's
correct, I really can't figure this one out.

If anyone can help I would be grateful

 

[Albert D.Kallal]

Me.Refresh

MergeAllWord ("Select References.Author, References.Year,
References.Title,
References.[Publisher_Report_to], References.[In], References.Journal,
References.Volume, References.Edition, References.Pages FROM References,
TempMerge WHERE References.ID = " & TempMerge.ref_id)

Note how I changed the last expression part...

Also, it is not clear where TempMerge.ref_id is defined? Did you set the
variable? You need to assign that variable before the above can work..

 

[Guest]

What I've got is several tables, References (with all of my records in),
Reports (with report titles in), Report_References (which contains each
Reports ID number and all of the Reference ID numbers associated with that
report) and the table TempMerge which is where the Reference ID numbers are
passed to if a particular Report is in selection in a combo box on my form
when my Mail Merge button is clicked. Hope that makes sense. The code I
originally posted (longwinded as it is) is what filled the TempMerge table
with the relevent Reference ID's and then passed them to the Word document.

I have a feeling that the TempMerge table may now be redundant, but I'm
really at a very basic level in using VBA. I am getting told I'm not
defining the variable, I'm going to go see what I can do but any help or
ideas would be really appreciated,

Diane

Me.Refresh

MergeAllWord ("Select References.Author, References.Year,
References.Title,
References.[Publisher_Report_to], References.[In], References.Journal,
References.Volume, References.Edition, References.Pages FROM References,
TempMerge WHERE References.ID = " & TempMerge.ref_id)

Note how I changed the last expression part...

Also, it is not clear where TempMerge.ref_id is defined? Did you set the
variable? You need to assign that variable before the above can work..


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Ok...I did not look at that sql very close.

Your "where" close is the "old" style of a relational join (I should have
recognized that...as I worked with old FoxPro, and that is also how we did
joins).

So, given you got:

MergeAllWord ("Select References.Author, References.Year, References.Title,
References.[Publisher_Report_to], References.[In], References.Journal,
References.Volume, References.Edition, References.Pages FROM References,
TempMerge WHERE References.ID = TempMerge.ref_id")

Hum, you know, the above looks ok. Try pasting the sql part into a blank
query...does it run?

ie: paste:

Select References.Author, References.Year, References.Title,
References.[Publisher_Report_to], References.[In], References.Journal,
References.Volume, References.Edition, References.Pages FROM References,
TempMerge WHERE References.ID = TempMerge.ref_id

Into a blank query. Does that work?

 

[Guest]

Ok, I shall go and try that in a normal sql window. This may take a while as
I've recently moved home and jobs and don't have the internet at my new place
yet nor the database at new place of work. I flicking between the two, makes
things awkward, but interesting. Ok, bear with me I shall try this and get
back to you if it works (I have a funny feeling it does as the code seems to
run up to the point of having to find a word file to open and then it
crashes, but I'll check)

Diane

 

[Guest]

Albert,

I tried running the sql in a normal query and it did work (once I'd put the
references into the temporary table anyway). So what next, how do I get the
information into my word file? Any help is really appreciated as this is
really driving me up the wall,

Diane