Runtime Error 424 on Generation of Mail Merge

Question

I'm very new at VBA and have managed to crib together a code to generate a mail merge from a macro button in excel (the same document as the database), from a very helpful fellow stack overflower.

The macro runs seamlessly most of the time, however I often get the Run-Time error 424 (object required) message. Running the macro again without changing anything always works, so I assume the issue is to do with defining the word document I want to target.

My code is:

Sub Letter_Generator()
Calculate
 MsgBox "Please Select the Word Document (on below taskbar) & select ok to all options."
Dim wdApp As Word.Application

On Error Resume Next

'   open the mail merge layout file
Dim wdDoc As Word.Document

'   Error handling

Set wdApp = GetObject(, "word.application")
If wdApp Is Nothing Then
Set wdApp = GetObject("[LOCATION OF WORD MAIL MERGE DOC]", "word.application") '    End If

On Error GoTo 0
With wdApp

 '   Set wdDoc = .Documents.Open

Set wdDoc = .Documents.Open(Filename:="LOCATION OF WORD MAIL MERGE DOC]") '
wdDoc.Application.Visible = True   

With wdDoc.MailMerge
    .OpenDataSource Name:="[LOCATION OF EXCEL DATABASE]"
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .Execute Pause:=False

End With


wdDoc.Application.Visible = True
     .Selection.WholeStory
     .Selection.Fields.Update

wdDoc.Close SaveChanges:=False

Set wdDoc = Nothing


End With

End Sub

Would appreciate any help on getting rid of the error - as I say I can run the merge fine by running the macro twice, but would rather eliminate any issues now!

Debugging shows that this section is to blame -

Set wdDoc = .Documents.Open(Filename:="K:\Team London & South East\Prop Letter\Prop Letter Bare Bones2.docx")

Thanks in advance!

Answer 1

I Think the Problem is here, that the Object can't be found, because there is not Word instance open. I like the way how you did it and how you check if the wdApp is Empty.

Set wdApp = GetObject(, "word.application")
   If wdApp Is Nothing Then
      Set wdApp = GetObject("[LOCATION OF WORD MAIL MERGE DOC]", "word.application")
   end if

now you just need to check again if the wdApp Variable is Empty. If it is, just create a Word instance and you should be fine.

So i would just add this lines of Code after the code you see above.

if wdApp is Nothing Then
   Set wdApp = CreateObject("Word.Application")
end if