CODEWITHSUNDEEP
excelvbaoutlookms-wordruntime-error
GT0028
GT0028

Reputation: 11

Vba runtime error: 91

I am trying to send email using Outlook to every email address in column:A in an Excel sheet and insert a Word document in the body. I have written the following code but it gives me run-time error 91. I am using Office 2013.

Public Sub Create_Outlook_Email()

Dim OutApp As Object, OutMail As Object, OutWordEditor As Object
Dim WordDoc As Object
Dim wordfile As String
Dim rng As Range
Dim row As Range
Dim cell As Range

 'Create new Outlook email
Set rng = Range("a2:a50")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set OutWordEditor = OutMail.GetInspector.WordEditor

For Each row In rng.Rows
    For Each cell In row.Cells

        With OutMail
            .To = cell.Value
            .Subject = "Your emails are moving on " & Range("e2").Value & "- don't get left behind     "

            wordfile = Application.GetOpenFilename(Title:="Select MS Word file", MultiSelect:=False)
            Set WordDoc = GetObject(wordfile)
            WordDoc.Content.Copy
            WordDoc.Close
            OutWordEditor.Content.Paste

            'See if Outlook is using Word to edit messages

            .display
            .send
        End With

        Set OutApp = Nothing
        Set OutMail = Nothing

        Set OutWordEditor = Nothing
        Set WordDoc = Nothing

    Next cell
Next row  

End Sub

Upvotes: 0

Views: 358

Answers (1)

A.S.H
A.S.H

Reputation: 29332

It would be easier to help if you provided information about the line where the error occurs. For instance you have the following error, it is very likely that it is the source of your problem:

Set OutWordEditor = Nothing

You are doing this inside the loop, but then you dont set the OutWordEditor variable in the next iteration. Hence you get "object not set" at the second iteration, at the line OutWordEditor.Content.Paste.

The solution I suggest is that you move these statements to inside the loop:

Set OutMail = OutApp.CreateItem(0)
Set OutWordEditor = OutMail.GetInspector.WordEditor

Besides, you dont need two nested loops, just one:

For Each row In rng.Rows
For Each cell In row.Cells
...
Next
Next

With all the above, your (single loop) becomes like this:

For Each cell In rng.Cells
    If Len(Trim(cell.Value)) = 0 Then Exit For ' <-- to stop at emty cell
    Set OutMail = OutApp.CreateItem(0)
    Set OutWordEditor = OutMail.GetInspector.WordEditor
    ... 'Rest of the loop
Next

Upvotes: 2

Related Questions