none
call excel macro from outlook RRS feed

  • Question

  • hi, i wonder where i go wrong, hope someone will help me out here,

    i have a macro that fires in outlook each tim a specific email comes in, it opens an excel file distribute some information, then it save the workbook,

    after doing all of the above i want it should call a macro from excel, somehow i was unsuccessful, here is my code.

    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook

    'here i have all my coding then i end with the next line'

        xlWB.Save
        xlApp.Run "PERSONAL.XLSB!mymacro"

    somehow i keep getting an error that it cant run the macro, can anyone help me out with this?

    Thursday, July 26, 2012 10:05 PM

Answers

  • ok, the problem was you cant have both either outlook and excel holding the same excel sheet at same time, what i did is, after it finish putting in all info in the excel, i ended the macro like this

    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing
    End Sub

    then in the excel sheet, i have a private sub with change_event which will continue the work from there, now everything is working out fine.

    thanks all of you for trying to help me out.

    • Marked as answer by Sol Stein Wednesday, August 8, 2012 12:56 AM
    Wednesday, August 8, 2012 12:56 AM

All replies

  • Personal.xlsb is a normal file, you must open it by procedure and run code.

    If That code make something on another excel data, you should to open in too (in the same session).

    Dim xlApp As Object, xlWkb As Object
    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True ' can be False if you do not wont see reaction, byt make sure is not fail
        Set xlWkb = xlApp.Workbooks.Open(personal_path)
    call procedure_from_personal 'or xlApp.Run procedure

    You can look on this not q.similar example in this article but I think open minded


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, July 27, 2012 6:51 AM
  • You have to use the GetObject function to bind the current instance of Excel to get this to work:

    Dim xlApp As Excel.Application
    Set xlApp = GetObject(, "Excel.Application")
    Call xlApp.Run("[MacroName]")

    You may also have to check the "Microsoft Excel xx.x Object Library" item ("xx.x" differs depending on your version of Office) in the References dialog within the VB Editor in order for Outlook to connect properly with Excel.

    Note that if you have multiple Modules in your Excel sheet, you may have to disambiguate the function call by using:

    Call xlApp.Run("[ModuleName].[MacroName]")

    Also note that if Excel is not open when you make the GetObject call, VBA will throw an error.

    Also also note that if there's more than one Workbook open in Excel, your macro might get confused as to which Workbook it's supposed to be operating on when you run it.  You may want to add an Optional argument to your Excel macro through which you can pass the name of the Workbook the macro should operate on:

    Sub mymacro(Optional wbName as String = "")
       Dim xlWB as Workbook
       If wbName = "" Then
          Set xlWb = ActiveWorkbook
       Else
          Set xlWb = Workbooks(wbName)
       End If
    
       ' Rest of your code
    
    End Sub

    The call to run the macro from the Outlook code would then look like:

    Call xlApp.Run("[MacroName]",xlWB.Name)
    Good luck!
    Friday, July 27, 2012 1:00 PM
  • Auto2, are you alive?

    My solution showing you late binding method, Brian early.

    No differences besides add excel control in references project.

    Are you handled with that?


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Monday, July 30, 2012 7:55 AM
  • Oskar,

    I tried your late-bound approach -- when I CreateObject() an Excel.Application and Workbooks.Add to it in Excel 2010, I get a window that doesn't have the Ribbon showing.  Is there a call to show the Ribbon?

    -Brian

    Monday, July 30, 2012 10:56 AM
  • sorry, all of you i was out of office, i looked over all replies, basically i tried already everything, let me tell you the excel is getting opened it could do work in it but when it comes to call the macro it cant find it, i went into the page in debug mode, in excel i noticed that i cant find the personal macro workbook, i need to close it, and then reopen it then i will find it, it seems when the VBA in outlook is opening the excel page somehow it cant open my personal macro workbook, why is that?
    Monday, July 30, 2012 1:55 PM
  • let me give more details, the macro in outlook is opening the excel page, it puts in some data, till here is everything working fine, then it needs to call the macro in excel here is where i get the error, it cant find the macro, well its true it cant find it because when outlook is opening the excel page somehow the personal macro workbook is not opening with it, so thats why it cant find it.

    but why is this happening?

    my office is 2007

    in refrenced its checked the excel library.

    Monday, July 30, 2012 2:12 PM
  • Auto2,

    This is another part of the problem I was having when trying to use Oskar's approach: using CreateObject to open Excel was creating an instance of Excel without any Add-Ins loaded.

    If you're trying to run the Outlook macro without already having opened Excel, then this is probably the issue.

    Try opening and binding Excel as follows:

    Dim xlApp As Excel.Application
    Call CreateObject("Shell.Application").ShellExecute("excel.exe")
    ' Might need to insert a second or two of wait time here
    Set xlApp = GetObject(, "Excel.Application")

    -Brian

    Monday, July 30, 2012 2:38 PM
  • Gentleman do not panic.

    Personal is auto open file - you can run in without another session or not.

    Late binding (Excel is installed of course), no XL references reqaried

    1st in excel:

    In Outlook:

    works as well ;]

    'For another usage, xlWkb file.
        On Error GoTo dalej
        Set xlWkb = xlApp.Workbooks.Open(personal_path)
    dalej:
        Set xlWkb = xlApp.Workbooks(Dir(personal_path))
        Debug.Print xlWkb.Name 'workin ;]


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved


    Monday, July 30, 2012 2:45 PM
  • Brian,

    i tried your code, which opened a new workbook called Book1.xlsx then it opened my excel sheet it put in the data, then when it needs to run the macro it dont find the macro!

    i went in to the VBA in my excel sheet and i didnt find my personal macro workbbok as well, at the same time when i look in the opened Book1 in VBA i did find it, looks very funny, something is missing here.

    and Oskar Shon,

    its the same thing, in my excel page which is test.xlsm you cant find there my personal workbook, i dont know why.

    Monday, July 30, 2012 4:45 PM
  • the only way i can find my personal workbook after outlook put in data in the sheet, was if i opened the file and i closed it, then reopen it then i find it.
    Monday, July 30, 2012 4:47 PM
  • What exactly do you mean when you say, "my personal workbook"?

    Is this a regular Excel workbook that you've created to hold your code?  And, are you saying that the code in this workbook isn't visible to the instances of Excel that are being opened from your code in Outlook?

    If this is the case, to get this to work I think you'd either have to (1) already have "my personal workbook" open in Excel and use my GetObject late-bound approach; or (2) call Excel.Workbooks.Open([path to "my personal workbook"]) after using either my or Oskar's approach but before trying to run the macro.

    An alternative would be to put your personal code into a custom Add-In (this tutorial is for office XP, but the only major difference is that you would save to a .xlam file instead of an .xla file) and use my late-bound approach.  (I was unable to access Excel code in my own custom add-ins when I used Oskar's early-bound approach.)

    -Brian

    Monday, July 30, 2012 5:50 PM
  • Brian, when i talk from MyPersonalWorkbook i mean to say the Personal.xlsb where i store all my codes, since late-bound and early-bound is getting very confused for me, can you explain me, in a easy way, how to do let say your version?

    well, i tried to have open an excel, and then run the macro, but what happens is , that on the file that outlook put in the data, "lets call it Data.xlsm" the personal.xlsb is not visible there. only in the new created excel wb, so thats why even i call the macro but the macro cant run, because in data.xlsb personal.xlsb is not activated.

    maybe i have you wrong somewhere, post me steps, and i will follow that and i will tell you the errors.

    thank you very much.

    Monday, July 30, 2012 8:28 PM
  • Fair enough, sorry that I haven't been writing clearly!  I think I know why it's not working for you, but I could easily be wrong.  Here's how I'd implement my approach:

    Try using the following at the start of your code in Outlook, to open Excel and open/create the necessary workbooks:

    Dim xlApp as Excel.Application
    Dim persWb as Excel.Workbook, dataWb as Excel.Workbook
    Dim nowTime as Single
    
    ' Open Excel and attach to xlApp
    Call CreateObject("Shell.Application").ShellExecute("excel.exe")
    nowTime = Timer : Do While Timer <= nowTime + 2 : DoEvents : Loop ' This waits 2 sec for Excel to open
    Set xlApp = GetObject(, "Excel.Application")
    
    ' Open the workbook with the personal code
    Set persWb = xlApp.Workbooks.Open("Path to personal workbook")
    nowTime = Timer : Do While Timer <= nowTime + 2 : DoEvents : Loop ' This waits 2 sec for the workbook to open
    
    ' Create a new workbook for the data output
    Set dataWb = xlApp.Workbooks.Add
    

    Use dataWb for all of your subsequent data output & manipulation from Outlook.  When you're ready to call your custom code in the personal Excel workbook, use these commands:

    dataWb.Activate ' Makes sure the macro(s) will operate on dataWb
    Call xlApp.Run("modulename.mymacro")

    "modulename" is the name of the module that shows in the Project Explorer; for example, in one of my workbooks "ValExtract" is the name of the single module:

    "mymacro" is the name of the specific subroutine itself.  So, if your "mymacro" were in my "ValExtract" module, I would run it using:

    Call xlApp.Run("ValExtract.mymacro")

    Now try running your macro in Outlook, starting with Excel completely closed.

    -Brian

    Monday, July 30, 2012 9:08 PM
  • From the beginning

    In Excels Personal file I wrote code changing color and font in opened file:

    Sub My_code()
    With Cells(1, 1)
        .Font.Bold = True
        .Interior.ColorIndex = 3
    End With
    End Sub
    

    I save it and close excel.

    I want to run this code on declared file from Outlook.

    In Outlooks VBE I've wrote:

    Sub Run_Excel_file_and_code_from_personal()
    Dim xlApp As Object, xlWkb As Object, xlMy_file As Object
    'change this path to your personal file
    Const personal_path$ = "c:\Users\Oskar\AppData\Roaming\Microsoft\Excel\XLSTART\Personal.xlsb"
    'my file do modyfication
    Const my_file_path$ = "c:\Temp\test_file.xlsx"
    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True 'False
    On Error GoTo dalej
        'if not automatycly open
        Set xlWkb = xlApp.Workbooks.Open(personal_path)
    dalej:
    On Error GoTo blad
        'no is set for sure
        Set xlWkb = xlApp.Workbooks(Dir(personal_path))
        'open my excel file
        Set xlMy_file = xlApp.Workbooks.Open(my_file_path)
            'run macto from personal
            xlApp.Run "Personal.xlsb!My_code"
            'close with save
            xlMy_file.Close True
            'close application
            xlApp.Quit
        Exit Sub
    blad:
        MsgBox Err.Number & " " & Err.Description, _
               vbExclamation, "VBATools.pl"
    End Sub
    

    Thats all folks


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    • Marked as answer by Sol Stein Monday, July 30, 2012 10:24 PM
    • Unmarked as answer by Sol Stein Monday, July 30, 2012 11:15 PM
    Monday, July 30, 2012 9:32 PM
  • i tried brians code, but still not working.

    i am going to give here my whole code, please review it, and tell me where i am wrong.

    actuelly i have a rule when a specific sender sens an email, a script is runs a macro which looks in the body of the email, then it takes some info to my excel sheet, and then i would like excel should run a macro.

    Sub CopyToExcell(MyMail As MailItem)


    Dim xlWB As Object
    Dim xlSheet As Object
    Dim oRng As Range
    Dim I As Long
    Dim bXStarted As Boolean
    Const strPath As String = "Test.xlsm"
    Const sFilePath As String = "C:\Users\xxx\Desktop\"

    Dim xlApp As Object

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Application.StatusBar = "Please wait while Excel source is opened ... "
        Set xlApp = CreateObject("Excel.Application")
        bXStarted = True
    End If
    xlApp.Visible = True
    On Error GoTo 0

    'Open the workbook to input the data
    Set xlWB = xlApp.Workbooks.Open(sFilePath & strPath)
    Set xlSheet = xlWB.Sheets("Sheet1")

    'Process each selected record
    Dim vText As Variant
    Dim sText As String
    Dim vItem As Variant
        sText = MyMail.Body
        vText = Split(sText, Chr(13))
        'Find the next empty line of the worksheet
    Dim rCount As Long
        rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(xlUp).Row
        rCount = rCount + 1

        'Check each line of text in the message body
        For I = UBound(vText) To 0 Step -1
            If InStr(1, vText(I), "Name:") > 0 Then
                vItem = Split(vText(I), Chr(58))
                xlSheet.Range("A" & rCount) = Trim(vItem(1))
            End If

            If InStr(1, vText(I), "Phone:") > 0 Then
                vItem = Split(vText(I), Chr(58))
                xlSheet.Range("B" & rCount) = Trim(vItem(1))
            End If


        Next I

    xlWB.Save
    xlWB.Close
    xlApp.Quit
    Call callexcelmacro

    End Sub

    then i made a new module where i put brians code.

    Sub callexcelmacro()
    Dim xlApp As Excel.Application
    Dim persWb As Excel.Workbook, dataWb As Excel.Workbook
    Dim nowTime As Single

    ' Open Excel and attach to xlApp
    Call CreateObject("Shell.Application").ShellExecute("excel.exe")
    nowTime = Timer: Do While Timer <= nowTime + 2: DoEvents: Loop    ' This waits 2 sec for Excel to open
    Set xlApp = GetObject(, "Excel.Application")

    ' Open the workbook with the personal code
    Set persWb = xlApp.Workbooks.Open("C:\Users\xxx\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB")
    nowTime = Timer: Do While Timer <= nowTime + 2: DoEvents: Loop    ' This waits 2 sec for the workbook to open

    ' Create a new workbook for the data output
    Set dataWb = xlApp.Workbooks.Add
    dataWb.Activate ' Makes sure the macro(s) will operate on dataWb
    Call xlApp.Run("module1.search")
    End Sub

    so where do i go wrong?

    Monday, July 30, 2012 9:47 PM
  • it seems that Oskar Shon, did the trick.

    still having some problems, the excel now is making me some problems, will try to deal with it, if will be unable then i will post.

    Monday, July 30, 2012 10:24 PM
  • after the macro in excel is running i get an error, "the object invoked has disconnected from its client"

    what can i do to stop this error?

    Monday, July 30, 2012 10:32 PM
  • ok, i am finish with that error, but here is what i get, after the macro is finish it closes the excel after few seconds, it pops up a msg test.xlsm now available for editing choose Read-Write to open it for editing,

    how can i stop that from happening?

    Monday, July 30, 2012 11:20 PM
  • You should Macro and Connections options set on enable in Excel [File/option/trust/..]as default.

    also you must know did file you're editing was closed (nobody use this file that moment)

    To check it use this function:

    Private Function WorkbookIsOpen(wbname) As Boolean
    '   Returns TRUE if the workbook is open
        Dim x As Workbook
        On Error Resume Next
        Set x = Workbooks(wbname)
        If Err = 0 Then WorkbookIsOpen = True _
            Else WorkbookIsOpen = False
    End Function

    p.s.

    if you're used

    sFilePath As String = "C:\Users\xxx\Desktop\"

    Check did your personal file not loaded et start from XLSTART folder first. You cant load 2 files the same name.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved


    • Edited by VBAToolsMVP Wednesday, August 1, 2012 7:34 AM
    Wednesday, August 1, 2012 7:32 AM
  • thanks, for your reply, my macro in excel is set enable, and no body uses this file at that time, besides outlook putting in the data, then excel playing around with the data, the thing you mention about my filepath, i am not so sure what you mean, basiclly as you see up in my code, outlook opens that path, and put in the data, when it finnish it closes the excel the workbook, then it call another module in outlook which i put your code in it, it opens that path again together with my personal.xlsb.

    everything works out fine after its finnish i get a popup "test.xlsm now available for editing choose Read-Write to open it for editing"

    it drives me crazy, i was busy with that two days searching the web, but no clue how to fix that.???

    Wednesday, August 1, 2012 11:43 PM
  • ok, the problem was you cant have both either outlook and excel holding the same excel sheet at same time, what i did is, after it finish putting in all info in the excel, i ended the macro like this

    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing
    End Sub

    then in the excel sheet, i have a private sub with change_event which will continue the work from there, now everything is working out fine.

    thanks all of you for trying to help me out.

    • Marked as answer by Sol Stein Wednesday, August 8, 2012 12:56 AM
    Wednesday, August 8, 2012 12:56 AM