Excel application object seems to "halt" when called in Outlook via VBA

Question

I want to create a backup file when sending an email. The following code works fine if I do a step by step debug it works fine. Without it a manually need to kill the Excel task otherwise the whole thing hangs:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Call SaveACopy(Item)
    End Sub

Sub SaveACopy(Item As Object)
    Const olMsg As Long = 3

    Dim m As MailItem
    Dim savePath As String

    If TypeName(Item) <> "MailItem" Then Exit Sub

    Set m = Item
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = False

    Dim fd As Office.FileDialog
    Set fd = xlApp.Application.FileDialog(msoFileDialogFolderPicker)

    Dim selectedItem As Variant
    If fd.Show = -1 Then    
        For Each selectedItem In fd.SelectedItems
            savePath = selectedItem & "\"
            savePath = savePath & Format(Now(), "yyyy-mm-dd - hhNNss")
            savePath = savePath & ".msg"
            m.SaveAs savePath, olMsg
            Next
        End If

    Set fd = Nothing
        xlApp.Quit
    Set xlApp = Nothing

    End Sub

Any ideas?

Just a wild guess that the file is locked. Left hand trying to save, right hand trying to send, neither knows about the other and won't let go. What happens if you throw a delay in there? Something like `Sleep`, `Application.Ontime` or heaven forbid a `DoEvents` loop or modal form window..... — ProfoundlyOblivious, Oct 17 '19 at 23:18
or what happens if you access `FileDialog` in a separate function that collects the folders into an array and returns them. Then you can loop through the array and save the msg. Not sure it will help, but splitting the actions may help with memory or whatever is causing Excel to stick. — Scott Holtzman, Oct 18 '19 at 00:03
@ProfoundlyOblivious yeah, the DoEvent before the if-Block did half the trick. Now the selection Window plops up but then it freezes again. I put finally put a DoEvent before every line, then it works. Got to figure it which one it actually needs by trail and error. Anyway feel free to write an answer. — Albin, Oct 18 '19 at 04:07
@ScottHoltzman DoEvents works fine, but I'll try your idea as well when I get the chance. — Albin, Oct 18 '19 at 04:12
Do you have the same problem if you delay the download? Like, what happens if you wait a could minutes after sending? — ProfoundlyOblivious, Oct 18 '19 at 06:46
Currently you're download process starts immediately after the send event. I'm curious if you still have the same problem a couple mins after the message was sent. Assigning acategory or a flag is an easy way to keep track what has and has not been saved. No idea if it will help, it's just a thought. — ProfoundlyOblivious, Oct 18 '19 at 07:12
@ProfoundlyOblivious Donwload? do you mean the save procedure? This is all local... (except for sending out the mail and saving it in the outbox imap folder) — Albin, Oct 18 '19 at 07:22
@ProfoundlyOblivious Ah ok, how would you suggest to add the "break"? Using the sleep command? — Albin, Oct 18 '19 at 15:39
Personally, I don't use `Sleep` for anything longer than 1 second so I wouldn't use it here. This is only a test to see if you experience the same behavior, so I'd trigger the job manually to see if the items save faster. If they don't they scrap the idea and continue troubleshooting. If they do, reevaluate to identify your ideal backup method - a one minute delay on backup isn't a good idea if you are likely to turn the computer once the send is complete. But as for the delay itself, check this out... https://stackoverflow.com/questions/12257985/outlook-vba-run-a-code-every-half-an-hour — ProfoundlyOblivious, Oct 18 '19 at 16:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/201109/discussion-between-profoundlyoblivious-and-albin). — ProfoundlyOblivious, Oct 18 '19 at 16:22
Similar questions where slowing the processing was the solution. 1 - [delay](https://stackoverflow.com/questions/48743005/vba-code-works-in-debug-mode-but-fails-in-run-mode). 2 - [display](https://stackoverflow.com/questions/35157284/vba-outlook-olmeetingstatus-olmeetingcanceled-works-on-debug-only-win7-outloo). If these and all you can think of fail you could try a MsgBox after the save. — niton, Oct 18 '19 at 18:38

score 1 · Answer 1 · answered Oct 18 '19 at 04:56

1

Using a liberal application of DoEvents to solve a problem is not unlike fixing a hole in your car's oil pan by adding more oil.

Whatever was causing that bind is still there and your program will run faster if you can get it straightened out.

answered Oct 18 '19 at 04:56

ProfoundlyOblivious

1,455
1
6
12

Too early... it still doesn't work, I'll do some testing and let you know. I'll give the +1 anyway already. – Albin Oct 18 '19 at 05:11

Excel application object seems to "halt" when called in Outlook via VBA

1 Answers1