Error populating email body from word documents

Question

I am working on an excel macro to send a series of emails each with a unique attachment, and one of three template emails that are saved as word documents. Everything is working well, except pulling the body of the email in from the word document. The problem seems to be with WordEditor. I get the following error

Err.Description:The operation failed.
Err.Number:-2147467259
Err.Source:Microsoft Outlook

Here is the code I have tried:

Sub SendDCLEmails()

    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim WordApp As Object
    Dim WordDoc As Object
    Dim DCLFile As String 'Attachment that differs for each email
    Dim DCLCount As Integer 'Number of emails that will be sent
    Dim toList As String
    Dim ccList As String
    Dim CoverLetter As String 'Word document template email
    Dim fileCheckDCL As String
    Dim fileCheckCover As String
    Dim editor As Object
    
    
'Set references to Outlook
    On Error Resume Next
    Set OutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then Set OutlookApp = New Outlook.Application
    On Error GoTo 0
        
'Set references to Word
    On Error Resume Next
    Set WordApp = GetObject(, "Word.Application")
    If Err <> 0 Then Set WordApp = New Word.Application
    On Error GoTo 0
            
    Sheets("Contacts").Select
    
'Create email for each record on "Contacts" tab
    DCLCount = ActiveSheet.UsedRange.Rows.Count - 1

    For i = 1 To DCLCount
    

        DCLFile = Range("AD1").Offset(i, 0).Value & "\" & Range("AE1").Offset(i, 0).Value
        CoverLetter = Range("AF1").Offset(i, 0).Value
        fileCheckDCL = Dir(DCLFile)
        fileCheckCover = Dir(CoverLetter)
        
            
            'Run some validations and generate the toList and ccList variables.
                                 
            Set WordDoc = WordApp.Documents.Open(CoverLetter)
            WordDoc.Content.Copy
                        
        'Create Emails
            Set OutlookMail = OutlookApp.CreateItem(0)
                    
            With OutlookMail
                .Display
                .To = toList
                .CC = ccList
                .Subject = Range("AG1").Offset(i, 0).Value
                Set editor = .GetInspector.WordEditor 'This is where the error occurs.
                editor.Content.Paste
                .Attachments.Add DCLFile
                .Send
            End With
                               
            WordDoc.Close savechanges:=False
        End If
           
        toList = vbNullString
        ccList = vbNullString
        CoverLetter = vbNullString
        DCLFile = vbNullString
        fileCheckDCL = vbNullString
        fileCheckCover = vbNullString
        Set editor = Nothing
        
    Next i
    
    OutlookApp.Quit
    WordApp.Quit

    End Sub

Answer 1

There is no need to use late and early-binding technologies in the VBA macros:

Set OutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then Set OutlookApp = New Outlook.Application

Instead, you need to use one or another. Read more about that in the Using early binding and late binding in Automation article. I'd suggest declaring all objects with real classes (early-binding), it may allow avoiding mistakes with syntax further. And use the New operator in the code instead of CreateObject one.

Set editor = .GetInspector.WordEditor 'This is where the error occurs.

Calling the WordEditor property may sometimes fail if the Inspector is not yet visible and initialized. Try to call the Display method prior getting the Word editor value.

Also instead of relying on Word documents as templates you may create templates in Outlook and use the Application.CreateItemFromTemplate method which creates a new Microsoft Outlook item from an Outlook template (.oft) and returns the new item. Read more about that in the article which I wrote for the technical blog, see How To: Create a new Outlook message based on a template.