Mail merge problem

  • Thread starter Thread starter Andrew J
  • Start date Start date
A

Andrew J

I have an aplication that opens a doc and runs mail merge
using vba (e.g. mergeDoc.MailMerge.Execute). The problem
is that when the path to the database changes, the merge
doesn't work. There doesn't seem to be a way in code to
set a new data source.

Is there a way to change the datasource for the word doc
in vba?

Thanks in advance.
 
Well, can use ms-access to "set" the document if it is relative to the
ms-access document. You can always open the merge doc, and set the merge
path (data source) (so, I guess that would accomplish the same thing). The
only thing I would not like is that fact that you would be modifying the
template each time.

So, fire up word, turn the macro recorder on, and then attach the mdb file.

You then can steal the code from word that sets the data source and paste it
into your code in ms-access. You then modify the code so that the path is
set each time.

The following code is to set the merge file to a text file, but the idea
would be the
same for a mdb file.

WordDoc.MailMerge.MainDocumentType = 0 ' wdFormLetters = 0

WordDoc.MailMerge.OpenDataSource _
Name:=strSaveDir & TextMerge, _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""


' write doc to disk....
WordDoc.SaveAs FileName:=strSaveDir & strNewName, _
FileFormat:=0, _
LockComments:=False, Password:="", AddToRecentFiles:=False,
WritePassword:="", _
ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False,
SaveAsAOCELetter:=False

As mentioned, get the word macro recorder to make the above (I did not type
the above!!!), and then just change the hard code to strSaveDir &
strFileName.

You then just need code to set-up the SaveDir and FileName. You can get the
current mdb dir via:

strDB = CurrentDb.Name

strDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))
 
Hi Andrew,

The answer to your question is: Yes - you can
change the datasource of a Word mailmerge
document using Access vba.

However, how exactly do you want to do this?
For example, do you want vba first to check if
the merge won't work (because the database's
location has changed) and then, if it won't work,
open a dialog to allow the user to navigate to
the database's new location?

If this is your aim, which version of Access are
you using? (The code for opening the dialog is
different for Access 97 and Access 2000).

Geoff
 
Hi Geoff, thanks for your answer.

I was planning on setting the datasource each time the
document is opened. Alternatively I could check to see if
the datasource cannot be found and change it then.

This will be for Access 97 and then 2000 or 2002 later on.
 
To set the datasource:

myWD1.ActiveDocument.MailMerge.MainDocumentType =
wdFormLetters
myWD1.ActiveDocument.MailMerge.OpenDataSource Name:= _
App.Path & "\yourDB.mdb", LinkToSource:=True,
Format:=wdOpenFormatAuto, Connection:="TABLE Members", _
SQLStatement:= "bla bla bla", SQLStatement1:=""

The real problem is that the Word document contains a
datasource. Thus, when you open the document, it attempts
to find this source. The solution is to save the Word
document with out the data source (as a non - MailMerge
document). Here is how:

'If the "Form" file is a Mail Merge document (with the DB
path), convert it to normal document without this info.
If mydoc1.MailMerge.MainDocumentType <>
wdNotAMergeDocument Then
mydoc1.MailMerge.MainDocumentType =
wdNotAMergeDocument
mydoc1.Save
MsgBox "Saved the Word Document file as non Mail
Merge."
End If

After you do the mail merge, make sure that you save the
main document as this type, or block this save:

'If this document was a MergerDocument when it was opened,
'right away it was changed to a non Merge Document and
saved.
'Now do not save it.
mydoc1.MailMerge.MainDocumentType = wdNotAMergeDocument
mydoc1.Saved = True
mydoc1.Close False


This problem also gave me fits for a while, Jim
 
Back
Top