none
Call Outlook VBA sub from VSTO RRS feed

  • Question

  • Hello,

    How can VSTO call an Outlook VBA sub ?  I'm porting my code slowly to VSTO, and would like to call the VBA codes until I port the subs over...

    I've tried declaring a variable as Outlook.Application, then the var.Run or var.SubName, it's not finding it..

    I'm using VS 2010...

    Thanks.


    Thanks, Dominic

    Thursday, March 8, 2012 2:27 AM

Answers

  • As far as I know there's no such alternative for Outlook 2010. I haven't tried Helmut's idea, which might work, or the button might be called using Accessibility, but other than that I'd move all the code to the addin. You can't really rely on a VBA procedure existing anyway.

    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "GTA_doum" <=?utf-8?B?R1RBX2RvdW0=?=> wrote in message news:1e9ffa02-343e-43e1-aa07-0744b72607aa...

    Agreed !  It does not work with Outlook 2010, and we are using Outlook 2010 !

    Is there any way to make VSTO and VBA communicate within Outlook ?  It's kind of silly, because VSTO can do whatever with Outlook, but by security, VBA functions/subs cannot be called from outside of Outlook...  VSTO looks very much running from the inside...


    Thanks, Dominic


    Ken Slovak MVP - Outlook
    Thursday, March 8, 2012 4:07 PM
  • Hello,

    I found a way!  What could be triggered from both VSTO and VBA?  The Clipboard!!

    So I used the clipboard to pass messages from one environment to the other.  Here is some few codes that will explain my trick:

    VSTO:

        'p_Procedure is the procedure name to call in VBA within Outlook

        'mObj_ou_UserProperty is to create a custom property to pass an argument to the VBA procedure

        Private Sub p_Call_VBA(p_Procedure As String)
            Dim mObj_of_CommandBars As Microsoft.Office.Core.CommandBars, mObj_ou_Explorer As Outlook.Explorer, mObj_ou_MailItem As Outlook.MailItem, mObj_ou_UserProperty As Outlook.UserProperty

            mObj_ou_Explorer = Globals.Menu_AddIn.Application.ActiveExplorer

            'I want this to run only when one item is selected

            If mObj_ou_Explorer.Selection.Count = 1 Then
                mObj_ou_MailItem = mObj_ou_Explorer.Selection(1)
                mObj_ou_UserProperty = mObj_ou_MailItem.UserProperties.Add("COM AddIn-Azimuth", Outlook.OlUserPropertyType.olText)
                mObj_ou_UserProperty.Value = p_Procedure
                mObj_of_CommandBars = mObj_ou_Explorer.CommandBars

                'Call the clipboard event Copy

                mObj_of_CommandBars.ExecuteMso("Copy")
            End If
        End Sub

    VBA:

    Create a class for Explorer events and trap this event:

    Public WithEvents mpubObj_Explorer As Explorer

    'Trap the clipboard event Copy

    Private Sub mpubObj_Explorer_BeforeItemCopy(Cancel As Boolean)
    Dim mObj_MI As MailItem, mObj_UserProperty As UserProperty

        'Make sure only one item is selected and of type Mail

        If mpubObj_Explorer.Selection.Count = 1 And mpubObj_Explorer.Selection(1).Class = olMail Then
            Set mObj_MI = mpubObj_Explorer.Selection(1)

            'Check to see if the custom property is present in the mail selected

            For Each mObj_UserProperty In mObj_MI.UserProperties
                If mObj_UserProperty.Name = "COM AddIn-Azimuth" Then
                    Select Case mObj_UserProperty.Value
                        Case "Example_Add_project"
                            '...
                        Case "Example_Modify_planning"
                            '...
                    End Select

                    'Remove the custom property, to keep things clean

                    mObj_UserProperty.Delete

                    'Cancel the Copy event.  It makes the call transparent to the user

                    Cancel = True
                    Exit For
                End If
            Next
            Set mObj_UserProperty = Nothing
            Set mObj_MI = Nothing
        End If
    End Sub

    Voilà :)


    Thanks, Dominic

    • Marked as answer by GTA_doum Tuesday, September 25, 2012 3:34 PM
    Monday, September 24, 2012 6:19 PM

All replies

  • Hello Dominic,

    see

    http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/b1ec3c9d-1567-47d2-8629-83d0a6c04423/

    You can call VBA Code by using  the "Application.Run" function.

    Greets, Helmut


    Helmut Obertanner [http://www.obertanner.de] [http://www.outlooksharp.de]

    Thursday, March 8, 2012 7:55 AM
    Answerer
  • Hello,

    Outlook does not have the Run method !  As I mentioned "I've tried declaring a variable as Outlook.Application, then the var.Run or var.SubName, it's not finding it..", those tricks works with Excel and Word, but not Outlook !


    Thanks, Dominic

    Thursday, March 8, 2012 2:16 PM
  • The usual approach is to put the VBA code into the Outlook ThisOutlookSession class module, declared as a Public Sub or Function. A Sub named
    MySub() would then be called, using late binding, as Outlook.Application.MySub. This would require using Reflection to access MySub.
     
    In my tests this method no longer works (it was never officially supported) in Outlook 2010.

    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "GTA_doum" <=?utf-8?B?R1RBX2RvdW0=?=> wrote in message news:05856a71-d7f4-4ed8-87fe-ba255ac606ca...

    Hello,

    Outlook does not have the Run method !  As I mentioned "I've tried declaring a variable as Outlook.Application, then the var.Run or var.SubName, it's not finding it..", those tricks works with Excel and Word, but not Outlook !


    Thanks, Dominic


    Ken Slovak MVP - Outlook
    Thursday, March 8, 2012 3:43 PM
  • Agreed !  It does not work with Outlook 2010, and we are using Outlook 2010 !

    Is there any way to make VSTO and VBA communicate within Outlook ?  It's kind of silly, because VSTO can do whatever with Outlook, but by security, VBA functions/subs cannot be called from outside of Outlook...  VSTO looks very much running from the inside...


    Thanks, Dominic

    Thursday, March 8, 2012 3:48 PM
  • Yes,

    did the Test a second ago:

    I tried using LateBinding, because I thought it's not possible using the PIA's.
    Then you are out of Luck as I know.

    Maybe you have a chance by getting the COMAddIn Object and then via the VBAEditor-Module.
    Don't know if there is a public Method that can run a Macro as when it's called from a Button.

    -- Ahh wait - here comes another Idea in mind. Theoretically you can define a custom hidden button and execute the button. Here you can define a Macro as far as I know. But I would go and port the macro code to the AddIn.

    Greets - Helmut

    private void button1_Click(object sender, EventArgs e) {
    
        object olApp;
    
        // is there an existing application object ?
        if (Process.GetProcessesByName("OUTLOOK").Any()) {
            // use the GetActiveObject method to attach an existing application object
            olApp = Marshal.GetActiveObject("Outlook.Application");
        } else {
            olApp = Activator.CreateInstance(Type.GetTypeFromProgID("Outlook.Application"));
        }
    
        var objectType = olApp.GetType();
        objectType.InvokeMember("ATest", System.Reflection.BindingFlags.InvokeMethod, null, olApp, new[]{});
    
        olApp = null;
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }


    Helmut Obertanner [http://www.obertanner.de] [http://www.outlooksharp.de]

    Thursday, March 8, 2012 3:55 PM
    Answerer
  • As far as I know there's no such alternative for Outlook 2010. I haven't tried Helmut's idea, which might work, or the button might be called using Accessibility, but other than that I'd move all the code to the addin. You can't really rely on a VBA procedure existing anyway.

    --
    Ken Slovak
    [MVP - Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    Reminder Manager, Extended Reminders, Attachment Options
    http://www.slovaktech.com/products.htm
     
     
    "GTA_doum" <=?utf-8?B?R1RBX2RvdW0=?=> wrote in message news:1e9ffa02-343e-43e1-aa07-0744b72607aa...

    Agreed !  It does not work with Outlook 2010, and we are using Outlook 2010 !

    Is there any way to make VSTO and VBA communicate within Outlook ?  It's kind of silly, because VSTO can do whatever with Outlook, but by security, VBA functions/subs cannot be called from outside of Outlook...  VSTO looks very much running from the inside...


    Thanks, Dominic


    Ken Slovak MVP - Outlook
    Thursday, March 8, 2012 4:07 PM
  • Found this http://social.msdn.microsoft.com/Forums/da-DK/outlookdev/thread/0968784e-15ba-460d-a391-5638cfcd06b3

    They tried the same trick, but it has the same restrictions, cannot call VBA !


    Thanks, Dominic

    Thursday, March 8, 2012 5:22 PM
  • Hello,

    I found a way!  What could be triggered from both VSTO and VBA?  The Clipboard!!

    So I used the clipboard to pass messages from one environment to the other.  Here is some few codes that will explain my trick:

    VSTO:

        'p_Procedure is the procedure name to call in VBA within Outlook

        'mObj_ou_UserProperty is to create a custom property to pass an argument to the VBA procedure

        Private Sub p_Call_VBA(p_Procedure As String)
            Dim mObj_of_CommandBars As Microsoft.Office.Core.CommandBars, mObj_ou_Explorer As Outlook.Explorer, mObj_ou_MailItem As Outlook.MailItem, mObj_ou_UserProperty As Outlook.UserProperty

            mObj_ou_Explorer = Globals.Menu_AddIn.Application.ActiveExplorer

            'I want this to run only when one item is selected

            If mObj_ou_Explorer.Selection.Count = 1 Then
                mObj_ou_MailItem = mObj_ou_Explorer.Selection(1)
                mObj_ou_UserProperty = mObj_ou_MailItem.UserProperties.Add("COM AddIn-Azimuth", Outlook.OlUserPropertyType.olText)
                mObj_ou_UserProperty.Value = p_Procedure
                mObj_of_CommandBars = mObj_ou_Explorer.CommandBars

                'Call the clipboard event Copy

                mObj_of_CommandBars.ExecuteMso("Copy")
            End If
        End Sub

    VBA:

    Create a class for Explorer events and trap this event:

    Public WithEvents mpubObj_Explorer As Explorer

    'Trap the clipboard event Copy

    Private Sub mpubObj_Explorer_BeforeItemCopy(Cancel As Boolean)
    Dim mObj_MI As MailItem, mObj_UserProperty As UserProperty

        'Make sure only one item is selected and of type Mail

        If mpubObj_Explorer.Selection.Count = 1 And mpubObj_Explorer.Selection(1).Class = olMail Then
            Set mObj_MI = mpubObj_Explorer.Selection(1)

            'Check to see if the custom property is present in the mail selected

            For Each mObj_UserProperty In mObj_MI.UserProperties
                If mObj_UserProperty.Name = "COM AddIn-Azimuth" Then
                    Select Case mObj_UserProperty.Value
                        Case "Example_Add_project"
                            '...
                        Case "Example_Modify_planning"
                            '...
                    End Select

                    'Remove the custom property, to keep things clean

                    mObj_UserProperty.Delete

                    'Cancel the Copy event.  It makes the call transparent to the user

                    Cancel = True
                    Exit For
                End If
            Next
            Set mObj_UserProperty = Nothing
            Set mObj_MI = Nothing
        End If
    End Sub

    Voilà :)


    Thanks, Dominic

    • Marked as answer by GTA_doum Tuesday, September 25, 2012 3:34 PM
    Monday, September 24, 2012 6:19 PM