Solved Error 424 Object Required when creating word template (1 Viewer)

[IceDarkness]

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:

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.

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:

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

 

[Gasman]

So what does CreateWord contain?

Set oWord = CreateWord

 

[IceDarkness]

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]

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]

I'm just trying your code now.
However found this for opening templates?

https://bettersolutions.com/word/templates/vba-code.htm

 

[Gasman]

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]

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]

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]

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]

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]

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]

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?

 

[IceDarkness]

It says "Microsoft Word" in the msgbox.

 

[Gasman]

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]

It's not, that's the thing. It's Microsoft Word every time even when it fails.

 

[Gasman]

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]

oDoc is after oWord is defined so it's empty when it tries to use the contents of oWord.

 

[Deleted member 147267]

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]

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]

Hi,

You have this line:

' ...
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