Creating mail merge docs in Word from Access?!

[Juggler25682]

Hi there...

Have a system here that is having a mailing list/mail merge 'system' added to it.

General needs are (all Office 2000):

- Click a button in Access that runs a query to identify all "mail requirements" that have not been sent out yet (ie where DateSent = Null on a Mail table) - grabbing customer names/address etc [relatively straight forward for me]

- Open Word and create x number of mail merged docs with the customer details (as covering letters if you like) [the merge .doc is set up, although lacking in ideas how to get Word to run automatically from Access... presume I need to set up a macro in Word to initiate the merge with the Access data)

- Then (this is the trickier bit) update the SentDate in the Mail table back in Access by adding the current date into it when the mail merge is complete (or when it is first requested etc... timing on this doesn't matter)!


I'm at a novice level, at the moment I'm just looking for general ideas as it's difficult trying to look for solutions when I'm not sure which solutions I need

Any help/guidance appreciated..

Juggler

 

[jfischer]

The following example assumes the following:
1) You have set up your Word mail merge document to use the appropriate Access database, table, and fields.
2) Your Access form has a TextBox named txtDocName into which the user can type the name of the Word document for the mail merge.
3) You have one or more Access queries defined that set the last mail date on your mailing records and do various other maintenance activities.
4) You have a Reference set in your Access project to the appropriate MS Word object library, depending on the version of Word you are using. (Open any code module, click Tools, References, to add the Reference.)
5) Your Access form has a Command button named btnMerge with the following code entered for the OnClick event:

Private Sub btnMerge_Click()
  'Requires Reference to MSWord8.OLB for Office 97
  'Requires Reference to MSWord9.OLB for Office 2000
  'Requires Reference to MSWord.OLB for Office XP (2002)
  'See MS Article Q159328, etc.
  Dim objWord As Word.Document
  Dim dbs As Database
  Dim qdf As QueryDef
  Dim Result
  
  Set objWord = GetObject(Me!txtDocName, "Word.Document")
  'Make Word visible
  objWord.Application.Visible = True
  'Set the Mail Merge source as the MyMailing database
  objWord.MailMerge.OpenDataSource _
    Name:="C:\MyMailing\MyMailing.mdb", _
    LinkToSource:=True, _
    Connection:="TABLE tblMyMailing", _
    SQLStatement:="SELECT * FROM [tblMyMailing]"
  'Execute the Mail Merge
  objWord.MailMerge.Execute
  Set objWord = Nothing
  'Update the MyMailing database with last mail date
  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs("qryMyLastMailingDateUpd")
  qdf.Execute
  'Archive the MyMailing table for history
  Set qdf = dbs.QueryDefs("qryMyArchiveMailingApp")
  qdf.Parameters(0) = Me!txtDocName
  qdf.Execute
  Set qdf = Nothing
  Set dbs = Nothing
End Sub