Error populating email body from word documents

0 votes

I'm developing an excel macro that will enable me to send a set of emails, each with a different attachment, along with one of three pre-written emails that are saved as word documents. The only thing that isn't working properly is importing the email content from the word document. WordEditor appears to be the issue. The next problem message appears.

Err.Description:The operation failed.
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
'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)
        'Create Emails
            Set OutlookMail = OutlookApp.CreateItem(0)
            With OutlookMail
                .To = toList
                .CC = ccList
                .Subject = Range("AG1").Offset(i, 0).Value
                Set editor = .GetInspector.WordEditor 'This is where the error occurs.
                .Attachments.Add DCLFile
            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

    End Sub
Jan 15 in Others by Kithuzzz
• 27,740 points

1 answer to this question.

0 votes

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, choose either one or the other. The article Using early binding and late binding in automation has further information on that. I would advise declaring all objects with genuine classes (early binding), since this may help further prevent syntax errors. Additionally, substitute the New operator for the CreateObject one in the code.

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.

answered Jan 15 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
0 answers

Attempted import error: 'Switch' is not exported from 'react-router-dom'

Code: import React from 'react'; import './App.css'; import NavBar from ...READ MORE

May 15, 2022 in Others by Kichu
• 19,040 points
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 51,240 points
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 51,240 points
0 votes
1 answer

Embed picture in outlook mail body excel vba

The image needs to be added and ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 51,240 points
0 votes
1 answer

VBA How to extract the date and time of arrival of a answered email

Use MailItem.ReceivedTime property. I hope this helps you ...READ MORE

answered Jan 9 in Others by narikkadan
• 51,240 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,240 points
0 votes
1 answer
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP