rapt3r

Outlook manipulation, open mails and open attachments

16 posts in this topic

#1 ·  Posted

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

Share this post


Link to post
Share on other sites



#2 ·  Posted

@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 :)

 

1 person likes this

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

#3 ·  Posted

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

Share this post


Link to post
Share on other sites

#4 ·  Posted

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted

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

Share this post


Link to post
Share on other sites

#7 ·  Posted

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?

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#8 ·  Posted

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted

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

 

Share this post


Link to post
Share on other sites

#11 ·  Posted

My bad :>
Should be:

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#12 ·  Posted

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.

Share this post


Link to post
Share on other sites

#13 ·  Posted

Glad you like my solution ;) 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 by rapt3r

Share this post


Link to post
Share on other sites

#15 ·  Posted

:)

Share this post


Link to post
Share on other sites

#16 ·  Posted

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).

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now