Automated Mail Merge

[Tiffosi2007]

Hi,

This code is taken from Fornatians post way back in 2003, i am having some problems with it and dont know if it is the original code or me being dumb.

Basically the code runs a maketable query and then uses the results and runs a mail merge. The code runs fine through to the highlighted segment. Where i get a Run time 13 type mismatch.

Public Function CreateLetters()
' Open a letter in Word and insert text - used by menu command.
'You must put Word in the DAO Reference Library.

Dim Dbs As Database
Dim rstTEMPOwnersMerge As Recordset
Dim rstSourceTable As String
Dim appWord As Word.Application
Dim intPages As Integer, StrMessage, I As String

Dim Worddoc As String

'Make declarations of Source and MergeFiles
'Set the temp table created by query as rstSourcetable

rstSourceTable = "TempOwnersMerge"

'Set location of word document

Worddoc = "H:\Access\Plan & Directory DB\Reports\PlanDistLetter.doc"

'Set it so that it doesnt come up with warnings re deleting data and such

DoCmd.SetWarnings False

'Run query which adds records to tempownersmerge (query is addtable one)

DoCmd.OpenQuery ("qryPlanMailMerge"), acViewNormal, acReadOnly

'Turn warnings back on

DoCmd.SetWarnings True

' set db to current

Set Dbs = CurrentDb()

'set rstTEMPOwnersMerge recordset to rstSourceTable (which is in turn set to TempOwnersMerge)

Set rstTEMPOwnersMerge = Dbs.OpenRecordset(rstSourceTable)

' If no records are returned then exit

If rstTEMPOwnersMerge.RecordCount = 0 Then
MsgBox "There are no matching records for your criteria, please try again", 0, "No Records"
Exit Function
End If

'Switch to Microsoft Word so it won't go away when you finish.
On Error Resume Next
AppActivate "Microsoft Word"

'If Word isn't running, start and activate it
If Err Then
Shell "c:\Program Files\Microsoft Office\Office\" _
& "Winword /Automation", vbMaximizedFocus
AppActivate "Microsoft Word"
End If
On Error GoTo 0

'Get an Application object so you can automate Word.
Set appWord = GetObject(, "Word.Application")

'Open a document based on the memo template, turn off the
'spell check

With appWord
.Documents.Add Worddoc
.ActiveDocument.ShowSpellingErrors = False
End With

MsgBox "The letter is now on screen and is ready for final editing and merging", 0, "Letter Ready..."

Set Dbs = Nothing
Set rstTEMPOwnersMerge = Nothing
Set appWord = Nothing

Set StrMessage = Nothing
End Function


I have yet to test the rest of the code but hopefully its is just the one bit i am stuck with. For further info, my query is called qryPlanMailMerge and the table the query adds to is TempOwnersMerge.

Thanks

 

[KeithG]

Do you have a reference set to the DAO library? Access is trying to open an ADO recordset, your setup for a DAO recordset. Use the below

instead of

Dim rstTEMPOwnersMerge As Recordset

Use this

Dim rstTEMPOwnersMerge As DAO.Recordset

 

[Tiffosi2007]

Keith, that worked great thanks. Sodslaw though it only got a little further, looking at debug it gets to ;

With appWord
.Documents.Add Worddoc
.ActiveDocument.ShowSpellingErrors = False
End With

Before i get a corruption error on the word doc. This is not the case as i can open it. However, it does seem to be iffy when opening while access is still running. It tells me it has been placed in a state by 'admin' preventing it from being read or locked.

Surely i shouldnt need to close the db before opening word? Is there an option i have missed?

Thanks

 

[KeithG]

What is the error message?

 

[Tiffosi2007]

Cheers Keith,

Error message is Run-Time error '51'51;
Word was unable to read this document. It may be corrupt
Try one or more of the following:
*Open and Repair the file
*Open the file with the text recovery converter.

I have already created the word document (PlanDistLetter) and it is mapped to the database using the mail merge wizard.

If need be i can ul copy of the letter.

 

[KeithG]

Can you compile your code successfully?

 

[Tiffosi2007]

Keith, the code compiled with no errors. It will open word but doesnt open the doc i specified earlier, it goes straight for the template. Very Frustrating!!!!

 

[KeithG]

Try changing Documents.Add to Documents.Open


You don't have the file open when trying to run this code do you?

 

[Tiffosi2007]

lol this is getting worse and worse, almost there though. I had just spotted the .add and changed it to .open. The file will now open the correct letter. Unfortunately, it seems to have lost all the mail merge settings i had saved in the word document and just merely opens it as a standard doc. It does not even have a link back to the DB (when you open the document on its own without the code the mail merge works fine)

 

[KeithG]

I think I would have to see what is going on. Could you use a report instead of the mail merge document?

 

[GaryPanic]

I cannot help - but flip this around

make your qry .
now make your letter - make mail merge -point to qry in access then use field names as merge options
merge .
hey presto..

 

[Tiffosi2007]

Gary - Thanks, i had tried that but i would end up having to keep showing users how to do this so wanted to automate it.

Keith - Heres a copy of the db docs. If you can figure it out i will send you a crate of beer through the post (ok maybe not)

I could use a report i suppose but i am trying to keep it as familiar to end users as possible and it may be that they wish to do some formatting to the letter after the mail merge which would be much easier for them in Word. Some of the users are not very computer literate.

Thanks!

Please note the folder structure for the db was:

H:\Access\Plan & Directory(containsDB) \Reports (Contains letter)

P.s. i had to remove all data from within the contact and org tables to upload it. The form for starting the mail merge is frmplanmailmerge. Qry is qryplanmailmerge and there is a module for the code called mdlPlanMailMerge!

 

[GaryPanic]

thats me out
g