CODEWITHSUNDEEP
vbaoutlook
Albin
Albin

Reputation: 1110

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

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?

Upvotes: 0

Views: 99

Answers (1)

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

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.

Upvotes: 1

Related Questions