Outlook manipulation, open mails and open attachments

[rapt3r]

Hi everyone,

im looking for a script to automatically activate a specific outlook inbox and then opening one email after another. In each email i need to open every excel attachment and copy each sheet content one after another into an other excel file in different excel sheets.

Do you thin this is possible with autoit, or would it be to hard to accomplish for a autiit scripting noob?

 

thank you in advance and

kind regards

[JLogan3o13]

@rapt3r welcome to the forum. Yes this is possible, and in fact quite common. Please check the OutlookEX thread in our Examples forum, download the library and try out the examples. If you have any questions, please post them here along with your code and we will do our best to assist

 

[rapt3r]

Thank you for the kind welcome and the reply.

i've insalled the outlookex udf and found the script _OL_ItemAttachmentGet. Unfortunately it seems like i dont really understand how the script works.

I want to select a specific inbox first. This inbox includes several mails where excel files can be found as attachments. I want to open the attachments sequentialy one after another and copy the complete contents of the excel files. I think this would be a good starting point to understand the code.

Maybe someone could help me out figuring out the code.

thank you

[water]

Will post an example as soon as I return to my office.

[water]

As a start: Something like this. Lines marked with "<==" Need to be modified or Extended.

#include <OutlookEX.au3>
#include <Excel.au3>

Global $aAttachments, $oWorkbook
Global $oOL = _OL_Open() ; Connect or start up Outlook
ConsoleWrite("OO: " & @error & @CRLF)
Global $oExcel = _Excel_Open() ; Connect to or start up Excel
ConsoleWrite("EO: " & @error & @CRLF)
Global $aFolder = _OL_FolderAccess($oOL, "", $olFolderInbox) ; Access the specific inbox <==
ConsoleWrite("FA: " & @error & @CRLF)
Global $aItems = _OL_Itemfind($oOL, $aFolder[1], $olMail, '[Subject]="TestXY"', "", "", "EntryID,Subject") ; Search for mail items <==
ConsoleWrite("IF: " & @error & @CRLF)
For $i = 1 To $aItems[0][0] ; Process all found items
    ConsoleWrite("AG: " & @error & @CRLF)
    $aAttachments = _OL_ItemAttachmentGet($oOL, $aItems[$i][0]) ; Get a list of attachments
    If @error = 3 Then ContinueLoop ; Mail without attachments
    For $j = 1 To $aAttachments[0][0]
        FileDelete("C:\temp\Test.xlsx")
        _OL_ItemAttachmentSave($oOL, $aItems[$i][0], Default, $j, "C:\temp\Test.xlsx") ; Assume there are only XLSX attachments and save them <== ?
        ConsoleWrite("AS" & $j & ": " & @error & "-" & @extended & @CRLF)
        $oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\Test.xlsx", True) ; Open workbook
        ; Process the Excel workbook here
        MsgBox(0, "", "...")
        _Excel_BookClose($oWorkbook, False) ; Close workbook
    Next
Next

 

[rapt3r]

thank alot for your effort!

I think this is something i can start with. Although the excel process part will be hard also.

But im getting the following error:

==> Subscript used on non-accessible variable.:
Global $aItems = _OL_Itemfind($oOL, $aFolder[1], $olMail, '[Subject]="*Test*"', "", "", "EntryID,Subject")
Global $aItems = _OL_Itemfind($oOL, $aFolder^ ERROR

 

I want to check every mail in the "Test" Inbox, without any restrictions in the subject line. Then open the xlsx attachments (its only xlsx), copy the complete content of the first sheet in the attachment workbook (ctrl+A > copy) and finally past the copied contents into another new workbook in the first worksheet. Then repeat: open the next attachment, copy the contents and paste into the already opened new workbook into the second worksheet etc.

Its really not my plan to get the code completely done by you, but it seems that the code is to difficult for me to understand.

 

thank you

[water]

You need to modify the _OL_FolderAccess statement to access the test inbox. What kind of store do we talk about (shared etc.)?
For better readability I reduced error checking to a simple ConsoleWrite.
Can you please post your modified script plus all messages from the SciTE output pane?

 

[rapt3r]

Ok, i did further tests and got it working. You are very helpful.

I'm getting the attachments to open one after another -> done

 

Now comes the hard part i think. The Excel process part.

First of all i need to build in some restrictions when opening before copying the contents of the excel attachments -> 1. check Cell B4 = "2017"     2. check Cell W4 = "test"

If both restrictions are true, then copy all contents into another Excel workbook in Sheet 1, next attachment both true, copy into the new workbook in sheet 2 etc.

 

I would appreciate if you could help me out in this case also.

[water]

I would start with something like this:

; Do this steps just one time
Global $oOutBook = _Excel_BookNew($oExcel, 1) ; Create a new workbook with a single sheet
Global $iCopy2Sheet = 0 ; Sheet Number where to copy data to

; Do this steps for every workbook
Global $sCellB4 = _Excel_RangeRead($oWorkbook, Default, "B4") ; Read cells
Global $sCellW4 = _Excel_RangeRead($oWorkbook, Default, "W4")
If $sCellB4 = "2017" And $sCellW4 = "test" Then
    If $iCopy2Sheet > 0 Then 
        _Excel_SheetAdd($oOutBook, -1, False, 1) ; Add a new sheet to the new workbook
    EndIf
    $iCopy2Sheet = $iCopy2Sheet + 1
    _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oWorkbook.Activesheet.UsedRange, Default) ; Copy the Activesheet to the clipboard
    _Excel_RangeCopyPaste($oWorkbook.Activesheet, Default, $oOutBook.Worksheets($iCopy2Sheet).Range("A1") ; Copy the clipboard to the new workbook
EndIf

 

[rapt3r]

ok we are getting closer.

the code runs until the copied content has to be pasted into the new workbook.

It opens the attachment and copy the content fine. Then im getting this error:

==> Error parsing function call.:
_Excel_RangeCopyPaste($oWorkbook.Activesheet, Default, $oOutBook.Worksheets($iCopy2Sheet).Range("A1")
_Excel_RangeCopyPaste($oWorkbook.Activesheet, Default, $oOutBook.Worksheets($iCopy2Sheet).Range("A1"^ ERROR
>Exit code: 1    Time: 2.63

 

[water]

My bad
Should be:

_Excel_RangeCopyPaste($oWorkbook.Activesheet, Default, $oOutBook.Worksheets($iCopy2Sheet).Range("A1"))

 

[rapt3r]

No, its my bad that i have not figured this out myself...

You are a genius, thank you alot. Working perfectly.

Now i will be adding some more parts to the code and will check, whether i can get it working myself. I will reply tomorrow.

[water]

Glad you like my solution  

[rapt3r]

ok, as promised my feedback. and yes, i love your solution.

i have modified the code abit and its doing perfectly what i want.

 

But now i have two points that i have to fix somehow.

1. Beside .xlsx attachments, there are also .xlsm attachments. -> so i need to include the xlsm files also. Is this possible?

2. When im running the script, there is a little problem with the "$iCopy2Sheet" counter. The script includes all attachments i want, but it randomly skips the sheets in the new workbook. Like: sheet1 = first attachment, sheet2= second attachment, sheet3= empty, sheet4 = third attachment etc. Maybe it runs to fast or something?

 

//EDIT

i have done abit research abount point 2 and found out that the script will skip a sheet and let it be empty, if the mail with excel attachments contains also embedded images in the text. If the mail does contain only excel attachments without any embedded images everything is fine... So i assume we have to filter out all kind of images in the mail items?

Edited June 15, 2017 by rapt3r

[rapt3r]

ok i figured it out myself.

Problem is that:

_OL_ItemAttachmentGet

This function lists not only the attachments which you see in the upper place of the mail in the attachment area. It lists also every attachment of the mail, that is embedded in the text (like pictures).

So pictures were saved in C:\temp\Test.xlsx (as corrupted files). Unfortunately the Copy2Sheet counter was triggered, even though the IF statement should not be triggered (because of the corrupted xlsx file in temp folder). Therefore i got alot of skipped sheets.

I solved this problem like this:

Global $sCellB4 = _Excel_RangeRead($oWorkbook, Default, "B4") ; Read cells
        Global $sCellC4 = _Excel_RangeRead($oWorkbook, Default, "C4") ; Read cells
        Global $sCellAK13 = _Excel_RangeRead($oWorkbook, Default, "AK13")
        If @error = 1 Then ContinueLoop

I was looking for an error in the _Excel_RangeRead statement (because the corrupted xlsx file cant be read properly). And if the xlsx file cant be read correctly, it will continueloop to the next attachment (which is a proper excel file again).