Automate Merge to Word from an Access Query

[Prufrock]

Hi
I am in the process of setting up a form where I want the end user to click on a button and it runs a particular query ( this works fine) but I want to add functionality where after the query has run it automatically activates the Merge to Microsoft Word Wizard in Access enabling them to select new document or another one.

Any help appreciated.

 

[AlexCuse]

Are you using Access 2003? If so, you will need to make some changes to your registry to disable the warning that pops up when opening a mail merge doc. Assuming you have the merge set up, here is some code:

Public Function MailMergeMemo()

Dim WordFileTemplateName    As String
Dim WordFileOutputName    As String
Dim appword As Object

   
   WordFileTemplateName = _
                "S:\SomeDir\SomeMailMerge.doc"

    
   WordFileOutputName = Format(Date - 1, "mmddyy") & "Ratesheet1a.doc"
    
    
    Set appword = CreateObject("word.application")
    appword.Visible = True
    appword.Documents.Open WordFileTemplateName
     
     With appword.ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=True
    End With
    
    appword.ActiveDocument.SaveAs FileName:="S:\somedir\" & WordFileOutputName, _
        FileFormat:=wdFormatDocument, LockComments:=False, Password:="", _
AddToRecentFiles:= True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:= False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
        appword.Quit (False) 'without saving
        'appword.Documents("tempMemo2004.doc").Close
        'DoCmd.Quit 'quit access driver file
End Function

Hope it helps (and sorry about the formatting, I did my best),

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Prufrock]

Thanks Alex,

I wasn't thinking of taking it this far. I am using Access 2003. You know when you run a query you can then go up top and click Merge to Microsoft Word on Office links? This brings up the Access Wizard. All I want to do is have the person click a button which runs the particular query and goes the step further and brings up the Wizard for them. This way they get to either say it is a new word document they want to open with merge fields in place or one they already have. As there may be a range of documents the fields will be used with, I wanted to keep it simple for them to select at the end.

Thanks again

Jeff

 

[AlexCuse]

Hm. I do not know how to do that.

But consider this - you store a small table with the different documents that your query could be merged with. You give users a simple name for the doc, that will be easy to remember, and use this to populate a combo box. User selects document, and in another field in the table you have complete path to the document in question. This complete path is passed to the function, and voila, you have a mail merge

(of course, you still need to configure the mail merge documents to read from teh specific query and what not). Sorry I couldn't be of more help, hopefully someone else will know how it is done.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Prufrock]

Thanks I will give it a try and let you know if this actually is better and works out well.

Jeff