Run parameterized PowerShell script in VBA outlook2010

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

Answer 1

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

Answer 2

check this link this may help you out

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

Answer 3

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

Answer 4

try only

Set SubjectLine = Mail.Subject

does it work?

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

please add checking in your RunAction procedure if passed Mail is not null (for example show msgbox if null)

Answer 9

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

Answer 10

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