Closing an Excel Application using Outlook VBA after using an Excel function

Question

I am working on a sort of "BOT" for Outlook (using Outlook VBA), in which I receive info by mail, split the mail body, paste it on Excel and execute Excel macros.

After adding the part where I call the Excel macro, I get

'1004 - application-defined or object-defined error'

if I'm running this for the second + time.

xlApp.Application.Run "AINT.Cali_B_Click"

Even though I'm setting my Excel variables to nothing and using .close and .quit, Excel is still running.

How can I end the application reference that is keeping Excel open?

Here's my full code:

Private Sub inboxItems_ItemAdd(ByVal Item As Object)
    On Error GoTo ErrorHandler
    Dim Msg         As Outlook.MailItem
    Dim MessageInfo
    Dim Result
    Dim splitter()  As String
    Dim splitter2() As String
    Dim loopH As String
    Dim str         As Variant
    Dim LoopCali    As Integer
    Dim i, j          As Integer
    Dim xlApp      As Object
    Dim sourceWB   As Object
    Dim Header, QuoteSTG, AINT, Treinamento As Object
    Dim strFile, file_name    As String
    Dim shellcom As String

    i = 1

    If TypeName(Item) = "MailItem" Then
        If InStr(Item.Subject, "BOT") > 0 Then
            splitter = Split(Item.Body, vbCrLf)

            Set xlApp = CreateObject("Excel.Application")
            strFile = "C:\Users\e1257539\Desktop\SMOBOT\SMO_TOOL_BOT.xlsm"
            With xlApp
                .Visible = False
                .EnableEvents = False
                .DisplayAlerts = False
            End With
            Set sourceWB = Workbooks.Open(strFile)
            sourceWB.Activate
            Set Header = sourceWB.Sheets(4)
            Set QuoteSTG = sourceWB.Sheets(13)
            Set AINT = sourceWB.Sheets(7)
            Set Treinamento = sourceWB.Sheets(10)

            file_name = QuoteSTG.Range("A" + CStr(QuoteSTG.Range("B1").Value2)).Value2
            QuoteSTG.Range("A" + CStr(QuoteSTG.Range("B1").Value2)).Value2 = ""

            If splitter(2) = "Calibração" Then
                loopH = splitter(26)
                LoopCali = CInt(loopH)
                sourceWB.Save
                Header.Range("D6").Value2 = splitter(22)
                Header.Range("D8").Value2 = splitter(12)
                Header.Range("F4").Value2 = "AINT"
                Header.Range("F3").Value2 = "EXW"
                Header.Range("C2").Value2 = file_name
                Header.Range("C4").Value2 = "Calibração"
                Header.Range("L2").Value2 = "30"
                Header.Range("K12").Value2 = Item.Subject '< criar string?
                j = 40
                For i = 1 To LoopCali
                    splitter2 = Split(splitter(j), "-")
                    AINT.Range("N7").Value2 = splitter2(0)
                    AINT.Range("N13").Value2 = splitter2(1)
                    j = j + 2
                    If splitter(j) <> "" Then
                        AINT.Range("N14").Value2 = splitter(j)
                    End If 
                    j = j + 2
                    If splitter(j) <> "" Then
                        AINT.Range("N16").Value2 = splitter(j)
                    End If
                    j = j + 2
                    If splitter(j) <> "" Then
                        If splitter2(0) <> "RMT" Then
                            AINT.Range("N15").Value2 = splitter(j)
                        End If
                    End If
                    j = j + 2
                    If splitter(j) <> "" Then
                        AINT.Range("N17").Value2 = splitter(j)
                    End If
                    j = j + 2
                    xlApp.Application.Run "AINT.Cali_B_Click" '< calling the excel sub 
                Next i

            End If

        End If

    End If

    'Closing excel
    MkDir "C:\Users\e1257539\Desktop\SMOBOT\" + file_name
    sourceWB.SaveAs FileName:="C:\Users\e1257539\Desktop\SMOBOT\" + file_name + "\" + file_name
    sourceWB.Close (False)
    xlApp.Quit
    Set xlApp = Nothing
    Set sourceWB = Nothing
    Set AINT = Nothing
    Set QuoteSTG = Nothing
    Set Header = Nothing

ErrorHandler:
    MsgBox Err.Number & " - " & Err.Description
    If Not sourceWB Is Nothing Then
        sourceWB.Close (False)
    End If
    If Not xlApp Is Nothing Then
        xlApp.Quit
    End If
    Set xlApp = Nothing
    Set sourceWB = Nothing
    Set AINT = Nothing
    Set QuoteSTG = Nothing
    Set Header = Nothing
End Sub

If you make Excel visible you may be able to see what the problem is. Sounds like the real problem may be in your Excel VBA, not in your Outlook code. — Tim Williams
You haven't specified the workbook that holds the macro...Try xlApp.Application.Run sourceWB.Name & "AINT.Cali_B_Click" You may have to play with that syntax a little...use your immediate window as a guide. — MBB70
@TimWilliams @MBB70 I took a look at the excel macros, however they just move information from one cell to another, however i'll give it a further look. The macro runs fine, even without especifing the workbook, i tried using xlApp.Application.Run sourceWB.Name & "AINT.Cali_B_Click" but the macro didn't work with this. I tried using Shell "taskkill /f /im excel.exe", vbHide , wich ends the Excel task, however when running for the seccond time with task kill, i get the error "462 - The remote server machine does not exist or is unavailable" — Laharl Krichevki
You’re missing a ! between them workbook name and the macro name. And if the workbook name has spaces it needs to be in single quotes. — Tim Williams

Laharl Krichevki Laharl Krichevki · Accepted Answer · 2019-12-03T13:32:00

Turns out using xlApp.Application.Run "AINT.Cali_B_Click" or xlApp.Application.Run sourceWB.Name & "AINT.Cali_B_Click" left open references on the VBA code.

The way to call the code without lefting any open is using CallByName,

As in Call CallByName(AINT, "Cali_B_Click", VbMethod)

This way the VBA code can call the function and run as many times as needed without the current error.

Closing an Excel Application using Outlook VBA after using an Excel function

1 Answers