OutlookEX UDF - Help & Support (IV)

[water]

I corrected the date/time format to DD-MM-YYYY as described in the post I linked to in my above post.
So you should try "[start] > ‘04-01-2021 00:00’"

I haver been able to find a source that exactly describes which date/time format Outlook uses/accepts for which function.
So in fact it is just trial and error 😞

[ReM]

Hallo Water,

the dateformat DD-MM-YYYY mm:hh works for me in both function (ItemCreate and itemFind)

I thing I will prefer this format, of cource it is similar to the local date/Time Format in Windows/Office (German/German) .

best regards 

Reiner

[water]

[HighlanderSword]

Hello,

 

I'm trying to use Jenkins for some Batch work and using Outlookex to send emails

Outlook is already open

I'm using version 1.6 of Outlookex ,  Running on Windows x64

When I compile the script and have Powershell start the process it works fine.

When I have Jenkins start the sane process exe from Powershell it always return and error code of 1 on

the  _OL_Open()

Indicating

"1 - Outlook was already up and running. When using _OL_Open you have to set parameter $bForceClose to True to stop the Outlook process (optional)1 - Outlook was already up and running. When using _OL_Open you have to set parameter $bForceClose to True to stop the Outlook process (optional)"

 

I have tried closing Outlook and have Jenkins start the exe , but I still get the same error code.

 

Is there something else I need to set

 

 

 

 

 

 

[water]

You are checking the wrong return values. The text you posted describes the value of @extended.

If @error = 1 then this means: "Unable to create Outlook Object. @extended is set to the COM error code"

Use _OL_ErrorNotify(3)

In @ScriptDir & "\Outlook_Debug.txt" you will find detailed error information.

[Dalius]

Hello,

I working on some emails assigning project, the idea is to automatically assign email by category, depending on subject or some text in body.

I have few questions:

1. How can I access different store (as per below store 5, with name "SCO")  to get new email notification using "oOApp_NewMailEx" (this will be used to trigger email assignment function).

I guessing that I need to put some kind of store ID in parameter below:

$oOApp.Session.GetItemFromID($sOL_EntryId, "StoreID")

Store count: 6
Store 1: Displayname: dalius
Store 2: Displayname:
Store 3: Displayname: 
Store 4: Displayname: 
Store 5: Displayname: SCO
Store 6: Displayname: 

2. How is possible to do "_OL_ItemFind" with filter using [Categories]='', or [Categories]="", both ways do not return any result in Array.

When I filter only with [UnRead]=True, then it works.

$aItems = _OL_ItemFind($oOutlook, $aFolder[1], $olMail, "[Categories]='' and [UnRead]=True", "", "", "Subject,Body,Categories", "")

Thank you.

[water]

Welcome to AutoIt and the forum!

1. Start here:

2. You want to retrieve all items without set categories? Right?

[Dalius]

Hello,

thank you for welcome

1. I will go thru forum, I was trying to retrieve $StoreID, get result as per below ant tried to use $StoreID as second parameter ($sOL_EntryId, "$StoreID"), but didn't worked;

2. Yes, I wonder maybe I should create new Array with filtered emails without Category, from first Array which I get with unread emails?

Thanks.

[water]

I'm not sure you can use Categories this way. Categories is a list of assigned Categories.
Will have to investigate if a Jet query allows to search such a list.

Do want to query a specific Category like "[Categories]='red category'"?

[Dalius]

So basic idea is to create automatic emails assigning to right employer.

I want to find Emails in inbox, which do not have any Category assigned yet, then check Subject or Body for specific keywords.

Then compare keyword with Excel where some list of keywords (which could be in email, because incoming emails, not always has standard Subject and etc.) which has a specific Category (employer name), then assign this category to email.

There will be about 2000 keywords maybe more, and about 30 Categories

First idea was to create rules, but to have 2000 rules as I know is impossible, because it can change from time to time, waste of tome to create and maintain it manually. Even creating rules automatically is illogical because it will make Outlook crash.

So script in the beginning of work day should collect all unassigned emails (which were received after working hours or during weekend) and assign them to right employer's.

Then during the day constantly check incoming emails on new email event and assig it right away.

[water]

Thanks for this detailed information!

First I will check if it is really possible to get the NewMail or ItemAdd event for another mailbox.
Then I will play with the Categories.

Stay tuned

[water]

Working example:

expandcollapse popup

#include <OutlookEX.au3>
#include <MsgBoxConstants.au3>

; *****************************************************************************
; Example Script
; Handle Outlook ItemAdd event when a new mail item arrives in your Inbox.
; This example works for an Inbox of another user or a shared mailbox.
; This script loops until Shift-Alt-E is pressed to exit.
; *****************************************************************************
HotKeySet("+!e", "_Exit") ; Shift-Alt-E to Exit the script
Global $sMailbox = "mailbox@company.com\Inbox" ; <== replace with the mailbox you want to monitor
Global $sTitle = "OutlookEX UDF Example Script"
MsgBox($MB_IconInformation, $sTitle, "Hotkey to exit the script: 'Shift-Alt-E'!", 10) ; Wait 10 seconds, then continue

; Start or connect to a running Outlook instance
Global $oOL = _OL_Open()
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling _OL_Open: @error=" & @error & ", @extended=" & @extended & @CRLF)

; Access the Mailbox
Global $aFolder = _OL_FolderAccess($oOL, $sMailbox)
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling _OL_FolderAccess: @error=" & @error & ", @extended=" & @extended & @CRLF)

; Create a collection of the items in this mailbox
Global $oItems = $aFolder[1].Items
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when accessing the folder items: @error=" & @error & ", @extended=" & @extended & @CRLF)

; Create the events for this collection. Outlook calls a function starting with "oOL_" for each event. For the ItemAdd event function oOL_ItemAdd will be called
Global $oTemp = ObjEvent($oItems, "oOL_")
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling ObjEvent: @error=" & @error & ", @extended=" & @extended & @CRLF)
ConsoleWrite("OutlookEX UDF Example Script - waiting for new items to arrive!" & @CRLF)

While 1
    Sleep(10)
WEnd

; ItemAdd event - https://docs.microsoft.com/en-us/office/vba/api/outlook.items.itemadd
Func oOL_ItemAdd($oItem)
    ConsoleWrite("OutlookEX UDF Example Script - new item has arrived!" & @CRLF)
    ConsoleWrite( _
            "From:    " & $oItem.SenderName & @CRLF & _
            "Subject: " & $oItem.Subject & @CRLF & _
            "Class:   " & $oItem.Class & " (43=Mail, 53=MeetingRequest ...)" & @CRLF)
EndFunc   ;==>oOL_ItemAdd

Func _Exit()
    _OL_Close($oOL)
    Exit
EndFunc   ;==>_Exit

 

[Dalius]

Hello,

thank you very much, looks very promising.

 Firstly I got error below:

but I changed from:

Global $sMailbox = "mailbox@company.com\Inbox" ; <== replace with the mailbox you want to monitor

to:

Global $sMailbox = "SCO\Inbox" ; <== replace with the mailbox you want to monitor

I guess it's important how mailbox is called, not which email address it represents.

So result is:

[water]

To search for properties without a value you need to use a DASL query. JET does not support this.
https://docs.microsoft.com/en-us/office/vba/outlook/how-to/search-and-filter/filter-items-that-do-not-have-categories

_OL_ItemSearch supports DASL-queries as the following example shows:

Global $sFilter = "@SQL=" & Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & " is null"
$aResult = _OL_ItemSearch($oOutlook, "*\InBox", $sFilter, "Subject")

The query returns the subject for all items from your inbox without a set category.

[Dalius]

1 hour ago, water said:

To search for properties without a value you need to use a DASL query. JET does not support this.
https://docs.microsoft.com/en-us/office/vba/outlook/how-to/search-and-filter/filter-items-that-do-not-have-categories

_OL_ItemSearch supports DASL-queries as the following example shows:

Global $sFilter = "@SQL=" & Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & " is null"
$aResult = _OL_ItemSearch($oOutlook, "*\InBox", $sFilter, "Subject")

The query returns the subject for all items from your inbox without a set category.

Thank you very much, I will test it at work on monday.

[Dalius]

Hello,

this week I tried to join everything to one place, so finding unassigned emails (without Category) is working. Wanted to share and a bit contribute to all who it might be useful.

Thank you @water for help and guiding.

Maybe code is not very nice and written in completely correct way, but it's working, now will be updating it to work faster.

expandcollapse popup

#cs ----------------------------------------------------------------------------
 AutoIt Version: 3.3.14.5
 Author:         S9101B
 Script Function: Emails Assigning
#ce ----------------------------------------------------------------------------
; Script Start - Add your code below here
#include <OutlookEX.au3>
#include <MsgBoxConstants.au3>
#include <StringConstants.au3>
#include <Excel.au3>
; *****************************************************************************
HotKeySet("+!e", "_Exit") ; Shift-Alt-E to Exit the script
Global $sMailbox = "SCO\Inbox" ; <== replace with the mailbox you want to monitor
Global $sTitle = "OutlookEX UDF Example Script"
Global $sFilter = "@SQL=" & Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & " is null"
Global $aItems
Global $aSubjectArray
Global $aBodyArray
Global $return[2]
Global $found
Global $categoryName
; *****************************************************************************
Global Const $SINGLE_WORD_RECOGNITION = "[a-zA-Z]+"
; *****************************************************************************
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
MsgBox($MB_IconInformation, $sTitle, "Hotkey to exit the script: 'Shift-Alt-E'!", 10) ; Wait 10 seconds, then continue
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
; Start or connect to a running Outlook instance
Global $oOL = _OL_Open()
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling _OL_Open: @error=" & @error & ", @extended=" & @extended & @CRLF)
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
; Access the Mailbox
Global $aFolder = _OL_FolderAccess($oOL, $sMailbox, 6, 0)
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling _OL_FolderAccess: @error=" & @error & ", @extended=" & @extended & @CRLF)
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
; Create a collection of the items in this mailbox
Global $oItems = $aFolder[1].Items
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when accessing the folder items: @error=" & @error & ", @extended=" & @extended & @CRLF)
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
__EmailsWithoutCategoryArray()
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
; Create the events for this collection. Outlook calls a function starting with "oOL_" for each event. For the ItemAdd event function oOL_ItemAdd will be called
Global $oTemp = ObjEvent($oItems, "oOL_")
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling ObjEvent: @error=" & @error & ", @extended=" & @extended & @CRLF)
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
While 1
    Sleep(10)
WEnd
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
;ItemAdd event - https://docs.microsoft.com/en-us/office/vba/api/outlook.items.itemadd
Func oOL_ItemAdd($oItems)
    __EmailsWithoutCategoryArray()
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling oOL_ItemAdd: @error=" & @error & ", @extended=" & @extended & @CRLF)
EndFunc   ;==>oOL_ItemAdd
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func _Exit()
    _OL_Close($oOL)
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling _OL_Close: @error=" & @error & ", @extended=" & @extended & @CRLF)
    Exit
EndFunc   ;==>_Exit
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func __EmailsWithoutCategoryArray()
    $aItems = _OL_ItemFind($oOL, $aFolder[1], $olMail, $sFilter, "", "", "EntryID,Subject,Body", "")
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling _OL_ItemFind: @error=" & @error & ", @extended=" & @extended & @CRLF)
    If IsArray($aItems) Then
        For $i = 1 To UBound($aItems) - 1
            $entryID = $aItems[$i][0]
            $subject = $aItems[$i][1]
            $body = $aItems[$i][2]
            __SubjectToStringArray($subject)
            __OpenFile()
            __ReadFileSubjects($aSubjectArray)
            If $found = True Then
                MsgBox(0, "Category found !!!", "Category found, by email Subject." & @CRLF & _
                        "Category name: " & $return[0])
                        __CloseFile()
                _SettCategory($return[0],$entryID)
            Else
                __BodyToStringArray($body)
                __ReadFileBody($aBodyArray)
                If $found = True Then
                    MsgBox(0, "Category found !!!", "Category found, by email Body." & @CRLF & _
                            "Category name: " & $return[0])
                            __CloseFile()
                    _SettCategory($return[0],$entryID)
                EndIf
            EndIf
        Next
    EndIf
EndFunc   ;==>__EmailsWithoutCategoryArray
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func __SubjectToStringArray($subject)
    $aSubjectArray = StringRegExp($subject, $SINGLE_WORD_RECOGNITION, $STR_REGEXPARRAYGLOBALMATCH)
    If IsArray($aSubjectArray) Then
        Return $aSubjectArray
    EndIf
EndFunc   ;==>__SubjectToStringArray
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func __BodyToStringArray($body)
    $aBodyArray = StringRegExp($body, $SINGLE_WORD_RECOGNITION, $STR_REGEXPARRAYGLOBALMATCH)
    If IsArray($aBodyArray) Then
        Return $aBodyArray
    EndIf
EndFunc   ;==>__BodyToStringArray
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func _SettCategory($categoryName,$entryID)
    _OL_ItemModify($oOL, $entryID, $aFolder[3], "Categories="&$categoryName)
If @error Then Exit MsgBox($MB_IconError, $sTitle, "Error when calling _OL_ItemModify: @error=" & @error & ", @extended=" & @extended & @CRLF)
EndFunc   ;==>_AddCategory
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func __OpenFile()
    $sWorkbook = @ScriptDir & "\LIST.xlsx"
    Global $oExcel = _Excel_Open(False, False, False, False, False)
    Global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook, True, False)
    Global $dealsList = _Excel_RangeRead($oWorkbook, "List")
EndFunc   ;==>__OpenFile
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func __CloseFile()
    _Excel_Close($oExcel, False, True)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Close Example", "Error closing the Excel application." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Sleep(2000)
EndFunc   ;==>__CloseFile
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func __ReadFileSubjects($compareArray)
    Local $subject
    $found = False
    For $i = 1 To 4
        $categoryName = $dealsList[$i][1]
        $subject = $dealsList[$i][2]
        If $found = False Then
            For $a = 0 To UBound($compareArray) - 1
                If $subject = $compareArray[$a] Then
                    $found = True
                    ExitLoop
                EndIf
            Next
        EndIf
        $subject = $dealsList[$i][3]
        If $found = False Then
            For $b = 0 To UBound($compareArray) - 1
                If $subject = $compareArray[$b] Then
                    $found = True
                    ExitLoop
                EndIf
            Next
        EndIf
        $subject = $dealsList[$i][4]
        If $found = False Then
            For $c = 0 To UBound($compareArray) - 1
                If $subject = $compareArray[$c] Then
                    $found = True
                    ExitLoop
                EndIf
            Next
        EndIf
        $subject = $dealsList[$i][5]
        If $found = False Then
            For $d = 0 To UBound($compareArray) - 1
                If $subject = $compareArray[$d] Then
                    $found = True
                    ExitLoop
                EndIf
            Next
        EndIf
        $return[0] = $categoryName
        $return[1] = $found
        If $found = True Then
            Return $return
        EndIf
    Next
EndFunc   ;==>__ReadFileSubjects
;---------------------------------------------------------------------------------------------------------------------------------------------------------------
Func __ReadFileBody($compareArray)
    Local $body
    $found = False
    For $i = 1 To 4
        $categoryName = $dealsList[$i][1]
        $body = $dealsList[$i][6]
        If $found = False Then
            For $a = 0 To UBound($compareArray) - 1
                If $body = $compareArray[$a] Then
                    $found = True
                    ExitLoop
                EndIf
            Next
        EndIf
        $return[0] = $categoryName
        $return[1] = $found
        If $found = True Then
            Return $return
        EndIf
    Next
EndFunc   ;==>__ReadFileBody
;---------------------------------------------------------------------------------------------------------------------------------------------------------------

 

[water]

To enhance speed I suggest to move the Excel stuff to the beginning (__OpenFile) and to the exit funtion _Exit (__CloseFile).
As you do not change the file there is no need to call this functions for every mail.

If/Then/Else/EndIf that hold just a single statement can be reduced to a single line:

If IsArray($aBodyArray) Then
    Return $aBodyArray
EndIf

will become

If IsArray($aBodyArray) Then Return $aBodyArray

shortens your script and enhances readability.

[Dalius]

Hello,

maybe any idea how to get to read email body from all emails which are in communication loop, because now when I reading email which is a reply to older email, I can access only newest email body, but not the older email in the same communication line.

Is there any possibility to get email id of older email which is related to newest reply email.

Any idea how emails are connected together with the same topic in Outlook?

Thank you.

[water]

That's called a "Conversation" in Outlook.

Use function _OL_ConversationGet to get all items of such a conversation.
Be sure that the store where the mail resides is "conversation enabled". This info is returned by function _OL_StoreGet.

[Dalius]

Just checked regarding conversations enabling, in Outlook is like below

But when I check with _OL_StoreGet, there is no True or False "|15 - True if the Store is Conversation enabled", that marked line represents store with which I working.