VBA Hangs

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

VBA Hangs

Post by jstevens »

This bit of code works fine the first time however subsequent runs encounter a message: Microsoft Excel is waiting for another application to complete an OLE action. One thing to note is that the Outlook application is closed during Excel VBA code execution. If the Outlook application is open the I can run the code multiple times without a challenge. The objective is to keep the Outlook application closed.

Code: Select all

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

    Set OutApp = CreateObject("Outlook.Application")  'This is where it hangs the second time the code is run.  Email does go out the first time.
    Set OutMail = OutApp.CreateItem(olMailItem)

        With OutMail
            .To = "MyEmailAddress@gmail.com"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Text Subject line"
            .Body = "Hi there...text only"
   
            .Send 
        End With
        
    Set OutMail = Nothing
    Set OutApp = Nothing
Regards,
John

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA Hangs

Post by HansV »

Above the line Set OutApp = Nothing, insert

Code: Select all

    OutApp.Quit
P.S. In my experience, automating Outlook from another application (such as Excel) works better if Outlook is already running. If not, messages may remain in the Outbox folder until the next time you start Outlook manually instead of being sent immediately.
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: VBA Hangs

Post by jstevens »

Thanks Hans!
Regards,
John