Solved Error 424 Object Required when creating word template (2 Viewers)

IceDarkness

New member
Local time
Today, 18:12
Joined
May 25, 2020
Messages
13
Hi, what I'm trying to do is open a word template from Access, populate some information and then attach it to an email. This is working fine in Office 2019 at home but our work computers are running office 2010 and it seems that after every two/three attempts, it fails with Object Required error. It's this line that's failing:

Code:
Set oDoc = oWord.Documents.Add("F:\whatever.dotx")

oWord definitely contains the Word application when it's failing so I don't really understand why it's doing it after 3/4 attempts. The CreateWord function is at the bottom of the code. Before I was just using CreateObject("Word.Application") but wasn't sure if I should be using GetObject so I found a function to combine both.

Code:
Private Sub Command154_Click()
Dim RecordID As Integer, OrgName As String, RecordComment As Variant, ContactName As String, ContactEmail As String, CName As Variant, ContractEndD As Variant
Dim ContactFirst1, ContactFirst, oWordTbl As Object, newrow As Integer, rowNew As Object, i As Integer, wdDoNotSaveChanges, myAttachments, ContractEnd, newfilename As String

OrgName = Me.OrganisationName
ContactName = Me.ContactName
ContactEmail = Me.Email_1
CName = Me.CName1
RecordID = Me.CommID
RecordComment = ContactName & " - " & ContactEmail
ContractEndD = Me.ContractEndDate
If IsNull(ContractEndD) Then ContractEndD = "<span style='background:yellow;mso-highlight:yellow'>[DD/MM/YYYY]</span>"

If Not IsNull(ContactName) Then
ContactFirst1 = Split(ContactName)
ContactFirst = ContactFirst1(0)
End If

Dim oWord As Object, iRecCount As Integer, iFldCount As Integer, j As Integer

Set oWord = CreateWord
       oWord.Visible = True

       Dim oDoc As Object

  Set oDoc = oWord.Documents.Add("F:\whatever.dotx")


    Dim cnStr As String
Dim cn As ADODB.Connection
Dim cnRs As New ADODB.Recordset
Set cn = CurrentProject.Connection

cnRs.Open "SELECT CName1, CEmail1,TypeOfContact FROM Comms WHERE ContactID = " & Me.ContactID & ";", cn, adOpenKeyset

    With cnRs
If .RecordCount <> 0 Then
            .MoveLast   'Ensure proper count
            iRecCount = .RecordCount    'Number of records returned by the table/query
            .MoveFirst
            iFldCount = .Fields.Count   'Number of fields/columns returned by the table/query
    End If

    Debug.Print iRecCount

     Set oWordTbl = oDoc.Tables(1)

For newrow = 1 To iRecCount
Set rowNew = oWordTbl.Rows.Add(BeforeRow:=oWordTbl.Rows(3))
Next newrow

            'Build our data rows
            For i = 1 To iRecCount
               oWordTbl.Cell(i + 2, 2) = Nz(cnRs![CName1], "")
            oWordTbl.Cell(i + 2, 3) = Nz(cnRs![CEmail1], "")
            oWordTbl.Cell(i + 2, 4) = Nz(cnRs![TypeOfContact], "")
                .MoveNext
            Next i


    End With
    With oDoc
        .SaveAs "H:\whatever.docx"
        .Close SaveChanges:=wdDoNotSaveChanges     
    End With

If oWord.Documents.Count = 0 Then oWord.Quit

'----------------------------

Dim objOutlook As Object, objEmail As Object, EmailTemplate As String
Set objOutlook = CreateObject("Outlook.application")
EmailTemplate = "F:\whatever.oft"
Set objEmail = objOutlook.CreateItemFromTemplate(EmailTemplate)
With objEmail
Set myAttachments = .Attachments
myAttachments.Remove 1
         myAttachments.Add "H:\whatever.docx"

    .To = Nz(ContactEmail)
    .Display
End With

newfilename = Format(Now(), "yyyy-mm-dd-hh-mm-ss") & " - " & OrgName & ".docx"

Name "H:\whatever.docx" As "H:\whatever\" & newfilename
Set objOutlook = Nothing
Set objEmail = Nothing

Set oWord = Nothing
Set oDoc = Nothing

  End Sub

CreateWord function:

Code:
Private Function CreateWord(Optional bVisible As Boolean = True) As Object



    Dim oTempWD As Object



    On Error Resume Next

    Set oTempWD = GetObject(, "Word.Application")



    If Err.Number <> 0 Then

        Err.Clear

        On Error GoTo ERROR_HANDLER

        Set oTempWD = CreateObject("Word.Application")

    End If



    oTempWD.Visible = bVisible

    Set CreateWord = oTempWD



    On Error GoTo 0

    Exit Function



ERROR_HANDLER:

    Select Case Err.Number



        Case Else

            MsgBox "Error " & Err.Number & vbCr & _

                " (" & Err.Description & ") in procedure CreateWord."

            Err.Clear

    End Select



End Function
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
So what does CreateWord contain? :(

Code:
Set oWord = CreateWord
 

IceDarkness

New member
Local time
Today, 18:12
Joined
May 25, 2020
Messages
13
It's at the bottom of the code. Before I was just using CreateObject("Word.Application") but wasn't sure if I should be using GetObject so I found a function to combine both. It still fails even without the separate function.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
Ok, sorry.
I don't believe you can do that unless oWord is a global object.?

I would just create the word object in the routine.
A quick Google should show you how, if your previous attempts were not working.?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
OK, I have used your function and opened a word document?

I had to make the function public though.? Where is your function located?, mine was in a module.
 

IceDarkness

New member
Local time
Today, 18:12
Joined
May 25, 2020
Messages
13
It's in the same form Sub.

But it doesn't really matter as the error still happens even if I was creating oWord in Command154_Click.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
It's in the same form Sub.

But it doesn't really matter as the error still happens even if I was creating oWord in Command154_Click.
Well, it is something up with your system then?, as I have just proved your code works, (well as far as opening the document), even with the function.
I might use that technique in future, so thank you for pointing it out.

Do you need any addition references? like OLE Automation ?
 

IceDarkness

New member
Local time
Today, 18:12
Joined
May 25, 2020
Messages
13
I'm wondering if it is a reference issue but surely it would be failing every time if it was.

OLE Automation is enabled on both computers.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
Well the only thing I can think off is that for some reason you are not getting a word object when you use that function.?
Not because of the function, but some situation means it is not created?

You could perhaps test for the object, perhaps call the function again once more, test again and quit with message if still not created.?
Ideally determine what is causing the issue, but that could take a while and not practical in your situation.?
 

IceDarkness

New member
Local time
Today, 18:12
Joined
May 25, 2020
Messages
13
I added a Watch to the variable with various breakpoints to see if it was empty or not when it fails. It's filled with the Word Document everytime which is puzzling.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
You have no error routine in the command click event?
Put one in with a message box of oWord.Name
See when that gets shown?
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
It says "Microsoft Word" in the msgbox.
Well it will everytime it manages to create the object :). I'm expecting it to show nothing sometime?
You could change it to a debug.print with a NOW() element if it interferes to much with the process.?
 

IceDarkness

New member
Local time
Today, 18:12
Joined
May 25, 2020
Messages
13
It's not, that's the thing. It's Microsoft Word every time even when it fails.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
It's not, that's the thing. It's Microsoft Word every time even when it fails.
Ok, at least we are getting somewhere.
Do the same for oDoc.

I assume the file is the same each time?
 

IceDarkness

New member
Local time
Today, 18:12
Joined
May 25, 2020
Messages
13
oDoc is after oWord is defined so it's empty when it tries to use the contents of oWord.
 

Micron

AWF VIP
Local time
Today, 14:12
Joined
Oct 20, 2018
Messages
3,476
It could be the path isn't resolved. This error can be raised by any part of the SET statement failing - even if the object qualifier is valid (oWord)
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:12
Joined
Sep 21, 2011
Messages
14,046
oDoc is after oWord is defined so it's empty when it tries to use the contents of oWord.
OK, use some other property.?

There are two objects on that line where you say the error occurs.
I believe we have proved that oWord is being created *every* time.?
That leaves the other object.?

Personally I Dim all my variables at the top of the code,not sprinkle them through the code, but that is because I am an old Cobol guy and we had to do it that way. :)

Worth moving it anyway, to where oWord is?

All I am trying to do is get to the bottom of the anomaly?, I do not know why it is not working either.:(
 

cheekybuddha

AWF VIP
Local time
Today, 18:12
Joined
Jul 21, 2014
Messages
2,237
Hi,

You have this line:
Code:
' ...
Dim ContactFirst1, ContactFirst, oWordTbl As Object, newrow As Integer, rowNew As Object, i As Integer, wdDoNotSaveChanges, myAttachments, ContractEnd, newfilename As String
' ...
You declare a variable wdDoNotSaveChanges as an implicit Variant. I guess this is because you are using late-binding.

You never set its value before you use it, so rely on its default value being 0. Perhaps this is causing unexpected results, when you pass an empty variant to the .Save method

You would be better off declaring it separately as a constant:
Const wdDoNotSaveChanges As Integer = 0

hth,

d
 

Users who are viewing this thread

Top Bottom