none
Run parameterized PowerShell script in VBA outlook2010 RRS feed

  • Question

  • Hi,

    I have the below requirment:

    If any e-mail comes in my inbox from specific alias/with a specific word, I wan to run a PowerShell scrip which is kept at a shared server location. PoweShell script accepts three arguments. I need to read the e-mail subject line and parse the test and pass part of the text as argument to the PowerShell script.

    [I am using Office 2010, and I want to add a VBA macro to fulfill the purpose]

    Can anybody help me on that?


    Thanks, Avijit Chatterjee


    Tuesday, April 17, 2012 9:14 AM

Answers

  • SubjectLine is a string, don't use Set.
     
        SubjectLine = Mail.Subject

    --
    Ken Slovak
    MVP - Outlook
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
     
     
    "Avijit Chatterjee" <=?utf-8?B?QXZpaml0IENoYXR0ZXJqZWU=?=> wrote in message news:8f8b448b-83a3-41dd-9061-37ae28a60c4c...

    I tried this also. But it is throwing below runtime error at this line only "Set SubjectLine = Mail.subject:

    Compile error: Object required

    Here is my VBA code:

    Public Sub RunAction(Mail As Outlook.MailItem)
        MsgBox ("Started")
        Dim SubjectLine As String
       
        Set SubjectLine = Mail.subject
       
        MsgBox (SubjectLine)
       
        Set ObjPShell = CreateObject("Wscript.Shell")
        PSScript = "powershell.exe E:\CopyBuild.ps1 " & _
                    " '\\msoffilesrv\Projects_MS\ACS\Team Area\v-avchat' " & _
                    " 'E:\Tst' " & _
                    " 'ssssssssssssssss' "
        ObjPShell.Run PSScript
    End Sub


    Thanks, Avijit Chatterjee


    Ken Slovak MVP - Outlook
    Friday, April 20, 2012 1:01 PM
    Moderator
  • Thanks Ken, it's my silly, but a good one :)

    I have removed Set and it's working fine. 

     SubjectLine = Mail.subject

    Thanks for your help.


    Thanks, Avijit Chatterjee

    Monday, April 23, 2012 9:50 AM

All replies

  • What exactly do you need help with?
     
    You can use the Application.NewMailEx() event in the ThisOutlookSession class module to tell when items come in. You get a list of the EntryID's of the items that came in. You can get an Outlook MailItem from the id using NameSpace.GetItemFromID, and check the item for whatever you want.
     
    Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
        Dim oNS As Outlook.NameSpace
        Dim obj As Object
        Dim oMail As Outlook.MailItem
     
        Set oNS = Application.GetNameSpace("MAPI")
        Set obj = oNS.GetItemFromID(EntryIDCollection)
     
        If obj.Class = olMail Then
            Set oMail = obj
            ' get oMail.Subject and any other mail properties desired
            ' call the script
        End If
     
        Set oNS = Nothing
        Set obj = Nothing
        Set oMail = Nothing
    End Sub

    --
    Ken Slovak
    MVP - Outlook
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
     
     
    "Avijit Chatterjee" <=?utf-8?B?QXZpaml0IENoYXR0ZXJqZWU=?=> wrote in message news:0a1d5337-761a-4901-9e9b-39a44537947b...

    Hi,

    I have the below requirment:

    If any e-mail comes in my inbox from specific alias/with a specific word, I wan to run a PowerShell scrip which is kept at a shared server location. PoweShell script accepts three arguments. I need to read the e-mail subject line and parse the test and pass part of the text as argument to the PowerShell script.

    [I am using Office 2010, and I want to add a VBA macro to fulfill the purpose]

    Can anybody help me on that?


    Thanks, Avijit Chatterjee



    Ken Slovak MVP - Outlook
    Tuesday, April 17, 2012 1:43 PM
    Moderator
  • check this link this may help you out

    http://www.phillyexug.org/aggregator/sources/5?page=4

    Thursday, April 19, 2012 12:13 PM
  • Hi,

    Thanks for your reply. It is really helpful. Here I am adding the piece of Outlook 2010 VBA code that I have tried to run the build copy sript. Here I am calling CopyBuild.ps1 with the required parameter (inside a VBA module) and it's working fine.

    Public Sub RunCopyBuild(Mail As Outlook.MailItem)
        Set ObjPShell = CreateObject("Wscript.Shell")
        PSScript = "powershell.exe -noexit E:\CopyBuild.ps1 " & _
                    " '\\msoffilesrv\Projects_MS\ACS\Team Area\v-avchat' " & _
                    " 'E:\Tst' " & _
                    " 'ssssssssssssssss' "
        ObjPShell.Run PSScript
    End Sub

    Queries:

    RunCopyBuild(Mail As Outlook.MailItem) runs when an e-mail comes into my account(rule has been defined), and I need to parse the subject line of that e-mail to get the build number to be copied.

    Ex.

    Subject line is like that: "Build release notification: Branch:rd_sydney_stable, Version:6002.18005.amd64fre.rd_sydney_stable.120414-1510 Build status- Completed"

    Here I need only the build version like "6002.18005.amd64fre.rd_sydney_stable.120414-1510" so that I can pass this as parameter to CopyBuild.ps1.

    I tried with below but it is throwing error:

    Dim SubjectLine As String

    Set SubjectLine = Mail.Subject.ToString() 
    MsgBox (SubjectLine)

    Can anyone help me on that?


    Thanks, Avijit Chatterjee

    Friday, April 20, 2012 6:41 AM
  • try only

    Set SubjectLine = Mail.Subject

    does it work?

    Friday, April 20, 2012 8:14 AM
  • I tried this also. But it is throwing below runtime error at this line only "Set SubjectLine = Mail.subject:

    Compile error: Object required

    Here is my VBA code:

    Public Sub RunAction(Mail As Outlook.MailItem)
        MsgBox ("Started")
        Dim SubjectLine As String
       
        Set SubjectLine = Mail.subject
       
        MsgBox (SubjectLine)
       
        Set ObjPShell = CreateObject("Wscript.Shell")
        PSScript = "powershell.exe E:\CopyBuild.ps1 " & _
                    " '\\msoffilesrv\Projects_MS\ACS\Team Area\v-avchat' " & _
                    " 'E:\Tst' " & _
                    " 'ssssssssssssssss' "
        ObjPShell.Run PSScript
    End Sub


    Thanks, Avijit Chatterjee

    Friday, April 20, 2012 8:45 AM
  • how do you invoke RunAction method? show that code with all path needed to determine how you get access to MailItem that you pass as a parameter

    Friday, April 20, 2012 9:04 AM
  • I set a rule like. when e-mail comes from this alias, run a script. I choose this script to run "RunAction". This script methods has been written inside a module in VBA editor.

    Please let me know if you need any specific info.


    Thanks, Avijit Chatterjee

    Friday, April 20, 2012 9:19 AM
  • please add checking in your RunAction procedure if passed Mail is not null (for example show msgbox if null)
    Friday, April 20, 2012 9:59 AM
  • SubjectLine is a string, don't use Set.
     
        SubjectLine = Mail.Subject

    --
    Ken Slovak
    MVP - Outlook
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
     
     
    "Avijit Chatterjee" <=?utf-8?B?QXZpaml0IENoYXR0ZXJqZWU=?=> wrote in message news:8f8b448b-83a3-41dd-9061-37ae28a60c4c...

    I tried this also. But it is throwing below runtime error at this line only "Set SubjectLine = Mail.subject:

    Compile error: Object required

    Here is my VBA code:

    Public Sub RunAction(Mail As Outlook.MailItem)
        MsgBox ("Started")
        Dim SubjectLine As String
       
        Set SubjectLine = Mail.subject
       
        MsgBox (SubjectLine)
       
        Set ObjPShell = CreateObject("Wscript.Shell")
        PSScript = "powershell.exe E:\CopyBuild.ps1 " & _
                    " '\\msoffilesrv\Projects_MS\ACS\Team Area\v-avchat' " & _
                    " 'E:\Tst' " & _
                    " 'ssssssssssssssss' "
        ObjPShell.Run PSScript
    End Sub


    Thanks, Avijit Chatterjee


    Ken Slovak MVP - Outlook
    Friday, April 20, 2012 1:01 PM
    Moderator
  • Thanks Ken, it's my silly, but a good one :)

    I have removed Set and it's working fine. 

     SubjectLine = Mail.subject

    Thanks for your help.


    Thanks, Avijit Chatterjee

    Monday, April 23, 2012 9:50 AM