mail merge access 2010

[krowe]

Hi

I'm upgrading one of my databases to 2010 (front end only).

In the 2003 version the code for running a mail merge works fine, but im getting a runtime 91 error - object variable or with block variable not set.

here is the code i'm using:

Dim mypath As String
Dim mypath3 As String
Dim Wordpath As String
Dim sDBPath As String
Dim oApp As Word.Application
Dim ThisDB As String
Dim oWord As Word.Document
Dim oMainDoc As Word.Document
 
    Wordpath = Environ("office") & "\winword.exe"
    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    mypath3 = ("" & mypath & "merge test.doc""")
    ThisDB = CurrentDb.Name
 
    Set oApp = CreateObject("Word.Application")
    Set oWord = oApp.Documents.Open(FileName:=mypath3)
    oApp.Visible = True
    With oWord.MailMerge
           .MainDocumentType = wdFormLetters
            sDBPath = ThisDB
            .OpenDataSource Name:=sDBPath, _
            SQLStatement:="SELECT * FROM [tblWriteToClient]"
    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
    oWord.Close
 
DoCmd.SetWarnings True

please can someone advise why this may not be working in 2010 - i've made sure the correct reference library is checked so its not that.

Thanks

Kev

edit - sorry it breaks at the line With oWord.MailMerge

 

[spikepl]

It most likely means what it says - so oWord is not set. I suspect the path. Do

debug.print mypath3

And if you are doing
DoCmd.SetWarnings True

that means that you maybe somehere have previously set

DoCmd.SetWarnings False

which is not a good thing to do while debugging your app

 

[krowe]

hi

i've remmed out the setwarnings (this was still in from a maketable query that used to be in the code)

here is the result from the debug:

debug.Print mypath3
C:\Documents and Settings\krowe\Desktop\Access 2010 citrix version\Housing DB local copy\merge test.doc"

which is pointing to the correct file (if i put this output into start>run it opens the word doc)

 

[spikepl]

Amazing. With a " in the filename?

Either a " at the beginning and end, or none.

 

[krowe]

ah i had to put the " at the beginning to make it work....

penny is dropping

 

[krowe]

so, i take it it needs the " around as there are spaces in the file name?

 

[krowe]

o wondered if maybe it was because i was using an old word doc, so i converted to word 2010.

I cant seem to get the syntax right for mypath3, i'm assuming i need a " at the beginning, but keeps coming up with syntax errors if i put it in the string.

 

[spikepl]

mypath3 = ("""" & mypath & "merge test.doc""")

But I am not sure that it needs the file wrapped in "
Try without and with.

 

[krowe]

thanks for your help so far!, i think it is getting there but now have a runtime error 5174 File could not be found (C:/windows/system32/False)

for some reason when i debug mypath3 it returns False

here is my code now:

Dim mypath As String
Dim mypath3 As String
Dim Wordpath As String
Dim sDBPath As String
Dim oApp As Word.Application
Dim ThisDB As String
Dim oWord As Word.Document
Dim oMainDoc As Word.Document
 
    Wordpath = Environ("office") & "\winword.exe"
    mypath = Left$(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir$(CurrentDb.Name)))
    mypath3 =  ("""" & mypath & "merge test.docx""")
    ThisDB = CurrentDb.Name
 
    Set oApp = CreateObject("Word.Application")
    Set oWord = oApp.Documents.Open(FileName:=mypath3)
    oApp.Visible = True
    With oWord.MailMerge
           .MainDocumentType = wdFormLetters
            sDBPath = ThisDB
            .OpenDataSource Name:=sDBPath, _
            SQLStatement:="SELECT * FROM [tblWriteToClient]"
    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
    oWord.Close
 
'DoCmd.SetWarnings True

edit - its breaking at line Set oWord = oApp.Documents.Open(FileName:=mypath3)

 

[spikepl]

mypath3 = mypath3 = ("""" & mypath & "merge test.docx""")

returns false, indeed

 

[krowe]

sorry just noticed that and amended

but still comes back false

 

[krowe]

oh, it's working now!

Thanks!