Activating opened Excel workbook from Outlook

Question

I have a macro in my Outlook that whenever I receive an e-mail with a certain subject, it automatically opens an Excel workbook and pastes a portion of the e-mail subject in a specific cell in one of the worksheets. It works perfectly. Now I need to do this exact same process but pasting this information in an already opened workbook, instead of opening a closed file.

I've tried different solutions from my limited Excel VBA knowledge (ActiveWorkbook, worbooks(filename).activate, etc.) but none of that worked and I have not found anything similar online, as most macros are written as being run from an Excel file and not Outlook.

This is part of our current code, that opens the file and pastes the e-mail subject (which is the "ticker" value) in a specific cell on the "Lista Empresas" worksheet. What I need is a code that does the same, but in an workbook that is already opened (let's call it "test1").

         Dim exapp As Excel.Application
         Dim ExWbk As Workbook
         Dim ExWbk2 As Workbook
         Set exapp = New Excel.Application
         Set ExWbk2 = exapp.Workbooks.Open("\\XXX\ListaEmpresas_ajustado.xlsm", UpdateLinks:=0)
         exapp.Visible = True

         ExWbk2.Sheets("Lista Empresas").Range("P2").Value = ticker
         ExWbk2.Sheets("Lista Empresas").Range("P3").Calculate

Hi Tim. Yes, the excel workbook has a fixed name "ListaEmpresas_ajustado" and should be opened at all times. — ldfabbro

Sam Sam · Accepted Answer · 2019-10-11T13:55:33

There are a few scenarios to handle here. First, is Excel running? If no, then do what you are doing already. If yes - is the correct workbook open? If yes - return it, otherwise open it.

Dim ExWbk2 As Workbook
Dim wb As Workbook
Dim exapp As Excel.Application

On Error Resume Next
Set exapp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    Set exapp = Nothing
End If
On Error GoTo 0

If exapp Is Nothing Then
    ' Excel is not running
    Set exapp = New Excel.Application
    Set ExWbk2 = exapp.Workbooks.Open("\\XXX\ListaEmpresas_ajustado.xlsm", UpdateLinks:=0)
    ExWbk2.Visible = True
Else
    ' Excel is running, but is the right book open?
    For Each wb In exapp.Workbooks
        Debug.Print wb.Name ' <-- This will help you find what to look for
        If wb.Name = "ListaEmpresas_ajustado" Then
            ' Yes, it is!
            Set ExWbk2 = wb
        End If
    Next
    If ExWbk2 Is Nothing Then
        ' No, it wasn't
        Set ExWbk2 = exapp.Workbooks.Open("\\XXX\ListaEmpresas_ajustado.xlsm", UpdateLinks:=0)            
    End If
End If

The trick to find out if Excel is running is GetObject. It will fail if it can't find it.
The for loop is there to allow for finding the correct workbook, based on the name. Adjust as needed.

Activating opened Excel workbook from Outlook

2 Answers