Microsoft Excel is waiting for another application to complete an OLE action

[derivsops]

Hi All,

I have a macro this is cutting, pasting, and creating pivot tables. After each time it creates an email. I have done this numerous times before and never had this error. Basically after the first email is generated the message displays. When going to generate the 2nd email it slows down alot then stops for some time while "publishing to temp.htm"...i believe the problem lies in the below code. After some time i receive a message box that says " Microsoft Excel is waiting for another application to complete an OLE action"



'EMAIL CLIENT
Dim oOutlookApp As Object, oOutlookMessage As Object
Dim oFSObj As Object, oFSTextStream As Object
Dim rngeSend As Range, strHTMLBody As String, strTempFilePath As String

'Get the temp folder path
Set oFSObj = CreateObject("Scripting.FilesystemObject")
strTempFilePath = oFSObj.GetSpecialFolder(2)
strTempFilePath = "W:\Equity Team\Temp\temp.htm"

Cells(1, 1).Select
'Now create the HTML file
Set rngeSend = Selection.CurrentRegion
ActiveWorkbook.PublishObjects.add(1, strTempFilePath, rngeSend.Parent.name, rngeSend.Address, 0, "This is a test", "").Publish True

'Create an instance of Outlook (or use existing instance if it already exists
Set oOutlookApp = CreateObject("Outlook.Application")

'Create a mail item
Set oOutlookMessage = oOutlookApp.CreateItem(0)

'Open the HTML file using the FilesystemObject into a TextStream object
Set oFSTextStream = oFSObj.OpenTextFile(strTempFilePath, 1)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = oFSTextStream.ReadAll

[Lamprey]

XL gives control over to Outlook to send the email, but it gets antsy when it doesn't have control for a while and tends to annoy with this message.

You can disable display alerts for the duration of your sub to prevent this from happening:

Dim lDisplayAlerts as Boolean

lDisplayAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
.
.
.
.
Application.DisplayAlerts = lDisplayAlerts

Can't really make a guess as to why the slowdown itself occurs...

[derivsops]

I see...but what I do not understand is why does excel seem to freeze for like 10 minutes?

[derivsops]

other macros that I made work fine...even the ones that display 30-40 emails

[derivsops]

FOUND IT!!!!!!!!!!!!!!!! part of my code would have the last cell in excel = to "blank"....there for VBA would try to publish 65000+ rows which would take sometime to do....thanks for the help!