vba mail merge

[krowe]

Hi All

I realise there are a lot of threads about this, but i cant find my solution anywhere.

I have a split db. I would like to automate standard letters from the data in the db. I have a button which runs the code below.

Basically it creates a table called tblCurrentClientForLetterTemplate, this contains only the customer that is currently being looked at. (this works)

The code then opens a word template. (this works)

It is then meant to set up the datasource in word and create the letter - but it doesn't

the code is:

DoCmd.SetWarnings False
Dim mypath As String
Dim mypath2 As String
Dim Wordpath As String
Dim folder As String
Dim sDBPath As String
    Wordpath = Environ("office") & "\winword.exe"
    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    mypath2 = ("" & Wordpath & " """ & mypath & "merge test.doc""")
    DoCmd.RepaintObject , ""
    DoCmd.OpenQuery "qryForLetterTemplate2", acViewNormal, acEdit
       folder = CurrentProject.Path
    Call Shell(mypath2)
        oApp.Visible = True
    With oMainDoc.MailMerge
        .MainDocumentType = wdFormLetters
        sDBPath = folder & "new housing database v6 FE.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [tblCurrentClientForLetterTemplate]"
    End With
    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1
DoCmd.SetWarnings True

It works up to the oApp.Visible = True point.

I have selected the word11 reference library.

Please can anyone point me in the right direction?

edit - I meant to say, some of the code may seem a bit unneccesarily lenghty, but the database needs to work on different platforms (from network, desktop, within citrix etc) so I have had to set up ways of obtaining current file paths for the db, word doc and exe files.

Kind Regards

Kev

 

[the_net_2.0]

the problem is probably in here:

    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    mypath2 = ("" & Wordpath & " """ & mypath & "merge test.doc""")
    DoCmd.RepaintObject , ""
    DoCmd.OpenQuery "qryForLetterTemplate2", acViewNormal, acEdit
       folder = CurrentProject.Path
    Call Shell(mypath2)
        oApp.Visible = True
    With oMainDoc.MailMerge
        .MainDocumentType = wdFormLetters
        sDBPath = folder & "new housing database v6 FE.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [tblCurrentClientForLetterTemplate]"

first of all, what is this?

mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))

what are trying to get from it? the db's directory path?

your problem might also be this:

folder = CurrentProject.Path
    Call Shell(mypath2)
        oApp.Visible = True
    With oMainDoc.MailMerge
        .MainDocumentType = wdFormLetters
        sDBPath = folder & "new housing database v6 FE.mdb"

check to see if

CurrentProject.Path

returns a path with a "\" on the end. If it doesn't, obviously the following will be invalid:

        sDBPath = folder & "new housing database v6 FE.mdb"

more than likely, that's the problem.

 

[krowe]

Hi

Thank you for your reply.

The first bit of code works fine, it just calls the correct word document which has the template which has all the mail merge fields set up in already. .

You were right about the folder path, so I have changed the code slightly.

But, I still get a problem when it hits the oApp.Visible = True line.

Here is my updated code:

DoCmd.SetWarnings False
Dim mypath As String
Dim mypath2 As String
Dim Wordpath As String
Dim folder As String
Dim sDBPath As String
    Wordpath = Environ("office") & "\winword.exe"
    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    mypath2 = ("" & Wordpath & " """ & mypath & "merge test.doc""")
    DoCmd.RepaintObject , ""
    DoCmd.OpenQuery "qryForLetterTemplate2", acViewNormal, acEdit
    ' DoCmd.OutputTo acQuery, "qryForLetterTemplate2", "MicrosoftExcelBiff5(*.xls)", mypath & "\qryCurrentClient.xls", False, "", 0
  '  Call Shell(mypath2)
    'DoCmd.Close acQuery, "qryForLetterTemplate2"=
   folder = CurrentProject.Path & "\"
    Call Shell(mypath2)
        oApp.Visible = True
    With oMainDoc.MailMerge
        .MainDocumentType = wdFormLetters
        sDBPath = folder & "new housing database v6 FE.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [tblCurrentClientForLetterTemplate]"
    End With
    With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1
DoCmd.SetWarnings True

Thanks again for you help so far.

Kev

 

[krowe]

I've been looking at this code again.

How does access/word know what oApp and oMainDoc are?

Shouldn't i have these defined and dimmed?

Or are these codes that a reference library should understand?

Just a few thoughts that will hopefully take me nearer to an answer.

Thanks

 

[krowe]

I think i've sorted this

I'm using this code:

DoCmd.SetWarnings False
Dim mypath As String
Dim mypath2 As String
Dim mypath3 As String
Dim Wordpath As String
Dim folder As String
Dim sDBPath As String
Dim oApp As Word.Application
    Wordpath = Environ("office") & "\winword.exe"
    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    ' mypath2 = ("" & Wordpath & " """ & mypath & "merge test.doc""")
    mypath3 = ("" & mypath & "merge test.doc""")
    
    DoCmd.RepaintObject , ""
    DoCmd.OpenQuery "qryForLetterTemplate2", acViewNormal, acEdit
    ' DoCmd.OutputTo acQuery, "qryForLetterTemplate2", "MicrosoftExcelBiff5(*.xls)", mypath & "\qryCurrentClient.xls", False, "", 0
   '  Call Shell(mypath2)
    'DoCmd.Close acQuery, "qryForLetterTemplate2"=
   folder = CurrentProject.Path & "\"
    ' Call Shell(mypath2)
    
    
  Dim oWord As Word.Document
  Dim oMainDoc As Word.Document
  Set oApp = CreateObject("Word.Application")
  Set oWord = oApp.Documents.Open(FileName:=mypath3)

        oApp.Visible = True
    With oWord.MailMerge
        .MainDocumentType = wdFormLetters
        sDBPath = folder & "new housing database v6 FE.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [tblCurrentClientForLetterTemplate]"
    End With
    With oWord
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute
    End With
    oApp.Activate
    oApp.Documents.Parent.Visible = True
    oApp.Application.WindowState = 1
    oApp.ActiveWindow.WindowState = 1
DoCmd.SetWarnings True
End Sub

It seems to all be working.

 

[krowe]

Ok,

One last thing with this macro, then i'll stop hounding you all!

What do I need to add to the end of the code to close the doc "merge test.doc" but leave the outputted mail merge file open.

Everything i've tried just closes all instances of word.

Thanks

Kev