How to fix Microsoft VBA Run-time error: ‘4605’ This method or property is not available because the Clipboard is empty or not valid.

Question

I have an Excel spreadsheet to keep track of students marks and created a code to email parents their student's marks. However, I often get the following error:
"Run-time error: ‘4605’. This method or property is not available because the Clipboard is empty or not valid."
Usually a few emails go out fine but then it hangs up at various 'paste' lines.

Any help on how to fix this would be appreciated. Here is the code:

Sub sendall()
Dim odata As New DataObject

For r = 1 To 1000
If InStr(Sheet1.Cells(r + 1, 1), "@") > 0 Then
esendtable (r)

odata.SetText Text:=Empty
odata.PutInClipboard

End If
Next

End Sub





Sub esendtable(r As Integer)

Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object

Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)


With newEmail
    '.To = Sheet1.Range("A1").Text
    .To = Sheet1.Cells(r + 1, 1).Text
    .CC = ""
    .BCC = ""
    .Subject = "Math 10 progress update"
    .Body = "Here is the Math 10 progress update for " & Sheet1.Cells(r, 1).Text & "." & vbCrLf & "If you have any questions, please email me anytime." & vbCrLf & "Thanks," & vbCrLf & "Bob Smith" & vbCrLf & "******@sd42.ca"
    .display
    
    Set xInspect = newEmail.GetInspector
    Set pageEditor = xInspect.WordEditor
    
    Sheet1.Range(Cells(r, 13), Cells(r + 1, 33)).Copy
    
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
     Sheet1.Range(Cells(r, 35), Cells(r + 1, 36)).Copy
    
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
    Sheet1.Range(Cells(r, 2), Cells(r + 8, 8)).Copy
    
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
    Sheet1.Range(Cells(r, 9), Cells(r + 5, 11)).Copy
    
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
    
    Sheet2.Range(Sheet2.Cells(7, 1), Sheet2.Cells(25, 2)).Copy
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
    
    Sheet3.Range(Sheet3.Cells(1, 1), Sheet3.Cells(28, 7)).Copy
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display

    .Send
    Set pageEditor = Nothing
    Set xInspect = Nothing
End With


Set newEmail = Nothing
Set outlook = Nothing

End Sub

Answer 1

Hi,

I searched on the internet for your answer.

Here's what I found that may help you-

https://answers.microsoft.com/en-us/msoffice/forum/all/run-time-error-4605-this-method-or-property-is-not/bada4269-3807-4705-aea6-a9db24b17a99

https://answers.microsoft.com/en-us/msoffice/forum/all/run-time-error-4605-this-method-is-or-property-is/3316da28-b980-4818-90ba-6c36de7bc868

Best Regards.