Error 91 when pasting into outlook from excel using vba

[mecerrato]

I have this posted in stackoverflow as well, link: Error 91 when pasting into outlook from excel using vba

I am a novice coder, use it to automate my business life

I have this code that creates an appointment in outlook and pastes a range of cells. The odd thing is that the code always fails on the first time but then works on the 2nd time. All I do is click on End when the error pops up and then run the macro again and it works. I have been dealing with this error for about 3 months and have tried different things but cannot figure it out.

Can one of you experts wee if you can tell me where I am going wrong with the code? Run-time error '91': Object variable or With block variable not set the error stops at this line of code: objSel.PasteAndFormat (wdChartPicture)

Here is the full code along with 2 screenshots:

Sub Meeting_InvitesNEW()
'this one works the best 03/07/23
Dim olApp As Outlook.Application
Set olApp = Outlook.Application
Set ObjMail = olApp.CreateItem(olMailItem)
Dim Mymail As Outlook.AppointmentItem
Call GetData
'Set the range
Set testRange = Range("$A$10:$AD$500")
'Get the cell or range that the user selected
Set myRange = Selection
'Check if the selection is inside the range.
If Intersect(testRange, myRange) Is Nothing Then
'Selection is NOT inside the range.
    MsgBox "You are not on a loan", vbCritical, "Read me"
Exit Sub
Else
    Worksheets("Manager County Info").Range("14:18").EntireRow.Hidden = False

Application.ScreenUpdating = False
Worksheets("Manager County Info").Range("D7:I18").Copy

    Set Mymail = olApp.CreateItem(olAppointmentItem)

    Mymail.Display
    
    'Application.Wait (Now + TimeValue("00:00:05")) 'wait 5 seconds from now
    
    Dim objItem As Object
    Dim objInsp As Outlook.Inspector
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection

    Set objItem = Mymail
    Set objInsp = objItem.GetInspector
    Set objDoc = objInsp.WordEditor
    Set objWord = objDoc.Application

     With Mymail
     .RequiredAttendees = Worksheets("Loans").Range("k" & ActiveCell.Row).Value
        .Subject = Worksheets("Loans").Range("B" & ActiveCell.Row).Value & " (LGI Builder LO - " & Worksheets("Loans").Range("K" & ActiveCell.Row).Value & ")" _
         & " COE " & Worksheets("Loans").Range("W" & ActiveCell.Row).Value & " (" & Worksheets("Loans").Range("Q" & ActiveCell.Row).Value & ")"
       ' .Location = "Borrower Cell"
        .Start = Worksheets("Loans").Range("M" & ActiveCell.Row).Value & " " & Format(Worksheets("Loans").Range("O" & ActiveCell.Row).Value, "h:mm")
        .Duration = 90
        .Importance = olImportanceHigh
        .ReminderMinutesBeforeStart = 180
        .MeetingStatus = olMeeting
        
        If Worksheets("Loans").Range("K" & ActiveCell.Row).Value = "Mario Cerrato" Then
        .BusyStatus = olBusy
        Else
        .BusyStatus = olFree
        End If
    End With
    
'    Application.Wait (Now + TimeValue("00:00:05")) 'wait 5 seconds from now
    
    Set objSel = objWord.Selection
    objSel.PasteAndFormat (wdChartPicture)
    Set objItem = Nothing
    Set objInsp = Nothing
    Set objDoc = Nothing
    Set objWord = Nothing
    Set objSel = Nothing
    Application.CutCopyMode = False

On Error GoTo 0

Set Mymail = Nothing
Set exclapp = Nothing
Set olApp = Nothing

End If
End Sub

 

[Micron]

No answers at SO at this point so I'll take a whack even though this is a bit beyond my experience.
I think you're not using Option Explicit because objMail is not declared. Not that this is your issue, but it suggests to me that a different object variable isn't set or it's empty. I'm thinking that wdChartPicture has not been assigned a value because I can't see where you actually selected something to be used as the chart image and actually assigned it to anything. Or are you expecting myRange or testRange to be pasted?

 

[mecerrato]

No answers at SO at this point so I'll take a whack even though this is a bit beyond my experience.
I think you're not using Option Explicit because objMail is not declared. Not that this is your issue, but it suggests to me that a different object variable isn't set or it's empty. I'm thinking that wdChartPicture has not been assigned a value because I can't see where you actually selected something to be used as the chart image and actually assigned it to anything. Or are you expecting myRange or testRange to be pasted?

I am sure you have noticed that my code is not written in an expert fashion. I cobbled it together from different resources but am no expert.
Answer:
I am copying a range from another sheet (Manager County Info) in the same excel file, here is the code excerpt:
Worksheets("Manager County Info").Range("D7:I18").Copy

 

[Micron]

Yes, but you're missing my point:

I can't see where you actually selected something to be used as the chart image and actually assigned it to anything

You copied but that's not the same as selecting. After reading this I changed your code to actually select something. This might mess up something that is supposed to happen afterwards but I did get an item with a range as a picture.

Selection object (Word)

Office VBA reference topic

learn.microsoft.com

Application.ScreenUpdating = False
Worksheets("001").Activate
Worksheets("001").Range("D7:I18").Select ' changed from Copy

    Set Mymail = olApp.CreateItem(olAppointmentItem)

I have to change sheet/range names to suit what I have, so be wary of such changes.