Excel / Outlook COM

[water]

Sure. Using Outlook events. Examples can be found in the Example Scripts section for Outlook.

[UEZ]

Well, I searched the forum and found this thread but not on its first page.

_OL_Example_SentMail_Event.au3 (2012-10-16)
When a mail is sent a copy is stored in the Sent Mail folder. This triggers the ItemAdd event for this folder.
The example script displays the Subject of the new item.
Based on this example script you could write your own mail archiving solution.

This is the right direction but still not what I'm trying to check (close / send) where as send should be triggert regardless whether mail is saved somewhere or not. I usually don't use the Sent folder.

[UEZ]

What is wrong here?

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include "..\Office\Outlook\OutlookEX.au3" ;adjust this path!

Global $hGUI = GUICreate("Test")
GUISetState()

; Excel part
Global $aField9[2] = [9, $xlTextFormat]
Global $aFieldInfo[1] = [$aField9]
Global $sWorkbook = @ScriptDir & "\Test.txt"
Global $sExcel = @ScriptDir & "\Test.xlsx"
Global $oExcel = _Excel_Open(False, False, True, True, True)
Global $oWorkbook = _Excel_BookOpenText($oExcel, $sWorkbook, 2, $xlDelimited, Default, True, ",", $aFieldInfo, ".", Default, Default, 65001)

$oExcel.Windows($oWorkbook.Name).Visible = False

; Create Outlook mail
Global $oOutlook = _OL_Open()
$oOutlook_Mail = $oOutlook.CreateItem(0)
$oEvent_OL = ObjEvent($oOutlook_Mail, "oOutlook_Mail_")

With $oOutlook_Mail
    ;   .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Test"
    .Attachments.Add($sExcel, 1, 1)
    .Display
EndWith

Do
Until GUIGetMsg() = -3

$oEvent_OL = Null
_OL_Close($oOutlook)
_Excel_Close($oExcel, False, True)

Func oOutlook_Mail_ItemAdd($oOL)
    ConsoleWrite("ItemAdd Triggert" & @CRLF)
EndFunc

Func oOutlook_Mail_NewMailEx($oOL)
    ConsoleWrite("New mail Triggert" & @CRLF)
EndFunc

Func oOutlook_Mail_ItemSend($oOL)
    ConsoleWrite("ItemSend Triggert" & @CRLF)
EndFunc

Func oOutlook_Mail_AttachmentAdd($oOL)
    ConsoleWrite("Attachment triggered" & @CRLF)
EndFunc

 

Only Func oOutlook_Mail_AttachmentAdd($oOL) is triggered!

[water]

Will post an example how to use the Write and Unload events tomorrow.

[UEZ]

This seems to a problem how Excel is started (in Multiple Document Interface (MDI) mode). Is it possible to start Excel in Single Document Interface (SDI) mode?

-> https://blogs.office.com/2013/06/03/opening-workbooks-by-running-separate-instances-of-excel/

This will solve probably my problem.

[water]

Can't you add the /X switch to the filetype association? Means that Excel is being started with /X when a user clicks on an Excel file and Windows starts Excel?
I only have Office 2010 so can't test any of the solutions provided on the web

[UEZ]

Changing the default settings in a company environment is not a good idea!

[water]

I see. Will check if there is an "Open Attachment" event to grab.

[UEZ]

Btw, running permanently Excel in SDI mode might cause problems -> https://msdn.microsoft.com/en-us/library/office/dn251093.aspx?f=255&MSPPError=-2147217396

if you running Excel 2013.

I see. Will check if there is an "Open Attachment" event to grab.

That's not a problem only with opening the attachment only. If you run the app without opening any XLSX and start another external Excel sheet the opened Excel sheet will be attached to the app instance and thus it will be unresponsive. If you close the app all attached Excel sheets will be closed, too.

The COM interface handling regarding this issue is very unsatisfying or I'm too stupid for it.

Edited November 11, 2015 by UEZ

[water]

So you need a solution where every workbook is opened in a new instance of Excel (independant of how it is being opened).
Unfortunately I have no idea how to accomplish this

[UEZ]

Anyhow, if you can create the example for event grabbing for OL events it would be great.

Maybe I or somebody else will need it some day...

THX!

 

Edit: found a hint here https://social.msdn.microsoft.com/Forums/office/en-US/25794163-d33b-478b-af6a-7dd0bf437380/how-to-make-sure-that-opening-a-workbook-starts-a-new-excel-process-and-does-not-use-the-existing?forum=exceldev

Application app1 = new Microsoft.Office.Interop.Excel.Application(); This line of code creates a new instance of excel everytime you run your code .

I don't know how to use it in Autoit yet...

Edited November 11, 2015 by UEZ

[water]

That's exactly what _Excel_Open does when parameter $bForceNew is set to True.
That's what you are already doing in your code in post #21.

[water]

This script triggers display and attachment add events.

#include <MsgBoxConstants.au3>
#include <OutlookEX.au3> ;adjust this path!

Global $oOutlook = _OL_Open()
$oEvent_OL = ObjEvent($oOutlook, "oOutlook_Mail_")
$oOutlook_Mail = $oOutlook.CreateItem(0)
$oEvent_Item = ObjEvent($oOutlook_Mail, "oOutlook_Item_")
$oOutlook_Mail.Display
Sleep(10000)
$oEvent_OL = Null
_OL_Close($oOutlook)

; Mail is being displayed
Volatile Func oOutlook_Item_Open(ByRef $bCancel)
    ConsoleWrite("Display mail triggered" & @CRLF)
    If MsgBox($MB_YESNO, "Question", "Should the mail be displayed?") = $IDNO Then $bCancel = True ; Do not display the mail
EndFunc

; Attachment is added to the mail
Func oOutlook_Item_AttachmentAdd(ByRef $oAttachment)
    ConsoleWrite("Attachment " & $oAttachment.DisplayName & " added." & @CRLF)
EndFunc

The problem with your code was caused because you "attached" the events to the wrong object (should be mail item not application).
An example for the sent mail item can be found in the examples that come with the UDF.

Something else missing?

[UEZ]

That's exactly what _Excel_Open does when parameter $bForceNew is set to True.
That's what you are already doing in your code in post #21.

But it doesn't work with Office 2013. When no Excel was opened before, only one Excel.exe is opened and all other instances are attachted to this process automatically. 

 

This script triggers display and attachment add events.

#include <MsgBoxConstants.au3>
#include <OutlookEX.au3> ;adjust this path!

Global $oOutlook = _OL_Open()
$oEvent_OL = ObjEvent($oOutlook, "oOutlook_Mail_")
$oOutlook_Mail = $oOutlook.CreateItem(0)
$oEvent_Item = ObjEvent($oOutlook_Mail, "oOutlook_Item_")
$oOutlook_Mail.Display
Sleep(10000)
$oEvent_OL = Null
_OL_Close($oOutlook)

; Mail is being displayed
Volatile Func oOutlook_Item_Open(ByRef $bCancel)
    ConsoleWrite("Display mail triggered" & @CRLF)
    If MsgBox($MB_YESNO, "Question", "Should the mail be displayed?") = $IDNO Then $bCancel = True ; Do not display the mail
EndFunc

; Attachment is added to the mail
Func oOutlook_Item_AttachmentAdd(ByRef $oAttachment)
    ConsoleWrite("Attachment " & $oAttachment.DisplayName & " added." & @CRLF)
EndFunc

The problem with your code was caused because you "attached" the events to the wrong object (should be mail item not application).
An example for the sent mail item can be found in the examples that come with the UDF.

Something else missing?

Thanks, now it works. Open triggers only the new OL window - not when the attachment was started.

Should be

Volatile Func oOutlook_Mail_AttachmentRead($oOL)
    ConsoleWrite("AttachmentRead triggered" & @CRLF)
EndFunc

 

Thanks for your patient.

Edited November 11, 2015 by UEZ

[water]

Open is not meant to be triggered when the attachment is being added. It is triggered when the Inspector is being opened to display the mail item.

 

[UEZ]

Let say I've started Excel this way:

Global Const $iPID_Excel = ShellExecute("Excel.exe", "/r /e /x", $sExcelPath, $SHEX_OPEN, @SW_HIDE)

Is it possible to use the instance parameter of ObjGet() function to attach to the Excel instance which was opened as above shown?

Any idea?

Edited November 12, 2015 by UEZ

[water]

As ShellExecute returns a Process-ID and ObjGet needs an integer as the index of the instance you want to access, you need a way to translate the PID to an instance number.

Untested:
Start Excel with ShellExecute and then search for the Process-ID in the array returned by ProcessList("Excel.exe"). Use the index of the corresponding entry for ObjGet.

Edited November 12, 2015 by water

[UEZ]

As ShellExecute returns a Process-ID and ObjGet needs an integer as the index of the instance you want to access, you need a way to translate the PID to an instance number.

Untested:
Start Excel with ShellExecute and then search for the Process-ID in the array returned by ProcessList("Excel.exe"). Use the index of the corresponding entry for ObjGet.

That's not working.

#include <Array.au3>

Global Const $iPID_Excel = ShellExecute("Excel.exe", "/r /e /x", "", $SHEX_OPEN, @SW_SHOW)
Sleep(2000)


$aExcel_Process_Info = ProcessList("Excel.exe")
$iPos = _ArraySearch($aExcel_Process_Info, $iPID_Excel)

Global $oExcel = ObjGet("", "Excel.Application", $iPos)
If @error Then
    ConsoleWrite("ERROR" & @CRLF)
    Exit
EndIf

E.g. when I have no Excel opened and start the script it doesn't work -> error.

But when I start Excel "normally" then it works. It connects to the standard instance. I assume "instance" is an integer starting from 1 to n.

[UEZ]

As ShellExecute returns a Process-ID and ObjGet needs an integer as the index of the instance you want to access, you need a way to translate the PID to an instance number.

Untested:
Start Excel with ShellExecute and then search for the Process-ID in the array returned by ProcessList("Excel.exe"). Use the index of the corresponding entry for ObjGet.

That's not working.

#include <Array.au3>

Global Const $iPID_Excel = ShellExecute("Excel.exe", "/r /e /x", "", $SHEX_OPEN, @SW_SHOW)
Sleep(2000)


$aExcel_Process_Info = ProcessList("Excel.exe")
$iPos = _ArraySearch($aExcel_Process_Info, $iPID_Excel)

Global $oExcel = ObjGet("", "Excel.Application", $iPos)
If @error Then
    ConsoleWrite("ERROR" & @CRLF)
    Exit
EndIf

E.g. when I have no Excel opened and start the script it doesn't work -> error.

But when I start Excel "normally" then it works. It connects to the standard instance. I assume "instance" is an integer starting from 1 to n.

[water]

Your script works fine here. Only modification I made is to replace $SHEX_OPEN with "".
The script works if there is an instance already running or not.

#include <Array.au3>

Global Const $iPID_Excel = ShellExecute("Excel.exe", "/r /e /x", "", "", @SW_SHOW)
ConsoleWrite("PID: " & $iPID_Excel & @CRLF)
Sleep(2000)

$aExcel_Process_Info = ProcessList("Excel.exe")
_ArrayDisplay($aExcel_Process_Info)
$iPos = _ArraySearch($aExcel_Process_Info, $iPID_Excel)
ConsoleWrite("iPos: " & $iPos & @CRLF)
Global $oExcel = ObjGet("", "Excel.Application", $iPos)
ConsoleWrite("ObjGet error: " & @error & @CRLF)