Reading mail attachements
 

Hi XL Experts,

I receive mails daily with excel attachements from a specified source
and I need to open the attachement to check if a specified word is
there in that attachement.

If found then I need to reply to that mail with some specific info,
otherwise(if the word not found) ignore the mail.

Could someone kindly advise on this.


--
Deepu
------------------------------------------------------------------------
Deepu's Profile: http://www.excelforum.com/member.php...o&userid=21162
View this thread: http://www.excelforum.com/showthread...hreadid=380458

Reading mail attachements
 

Deepu:

you'll need some fancy stuff:

I just hope you're fairly competent with VBA.
if you are not and the following code is abacadabra:
then I'm sorry, but I'm not going to explain this to a novice..

use advanced search to find the items in outlook's inbox.
(adv search is asynchronous.. So you'll need a class module
to capture the search complete event in outlook.

when search complete is triggered you must
loop the items in the outlook.Search.Results


Here's a beginning for the outlook part of things.
you'll need to adapt and add code to ...

open the attachments..
find the "attribute" aka specific info
and do something.




CLASSMODULE named COutlookSearch
'
Option Explicit

Private Declare Sub Sleep Lib "kernel32.dll" ( _
ByVal dwMilliseconds As Long)

Dim WithEvents olApp As Outlook.Application
Dim bWaiting As Boolean

Const tagSS = "Search Subject"
Const tagAT = "Search with Attachment"


Sub SearchSubject(ByVal sSubject$, _
Optional ByVal sScope$ = "Inbox", _
Optional ByVal bThisMonth As Boolean)
Const csFILTER$ = _
"urn:schemas:mailheader:subject LIKE '%|s|%'"
Dim sFilter$


sFilter = Replace(csFILTER, "|s|", sSubject)
If bThisMonth Then
sFilter = sFilter & _
" AND %thismonth(urn:schemas:httpmail:datereceived)%"
End If
While bWaiting
DoEvents
Sleep 200
Wend
bWaiting = True
Set olApp = New Outlook.Application
Call olApp.AdvancedSearch(sScope, sFilter, True, tagSS)
End Sub

Sub SearchWithAttachment(ByVal iReadFlag%, _
Optional ByVal sScope$ = "Inbox")

Const csFILTER$ = "urn:schemas:httpmail:hasattachment = 1"
Dim sFilter$
Select Case iReadFlag
Case 0 'Unread
sFilter = csFILTER & " AND urn:schemas:httpmail:read = 0"
Case 1 'Read
sFilter = csFILTER & " AND urn:schemas:httpmail:read = 1"
Case Else 'ignore
sFilter = csFILTER
End Select
While bWaiting
DoEvents
Sleep 200
Wend
bWaiting = True
Set olApp = New Outlook.Application
Call olApp.AdvancedSearch(sScope, sFilter, True, tagAT)

End Sub

Private Sub olApp_AdvancedSearchComplete( _
ByVal SearchObject As Outlook.Search)

Select Case SearchObject.Tag
Case tagSS: Call SearchComplete_SS(SearchObject)
Case tagAT: Call SearchComplete_AT(SearchObject)
Case Else: MsgBox _
"Unknown search has completed. Tag:" & SearchObject.Tag
End Select
Set olApp = Nothing
bWaiting = False

End Sub
Private Sub SearchComplete_SS(olSearch As Outlook.Search)
Dim i%, n&
With olSearch.Results
If .Count = 0 Then
MsgBox "No items were found", vbExclamation, _
olSearch.Tag
Else
ActiveSheet.Range("a:a").Clear
For i = 1 To .Count
With .Item(i)
ActiveSheet.Hyperlinks.Add _
anchor:=ActiveSheet.Range("A1").Cells(i, 1), _
Address:="outlook:" & .EntryID, _
TextToDisplay:=.Subject
End With
Next
End If
End With

End Sub

Private Sub SearchComplete_AT(olSearch As Outlook.Search)
Dim i%, n&
With olSearch.Results
If .Count = 0 Then
MsgBox "No items were found", vbExclamation, _
olSearch.Tag
Else
ActiveSheet.Range("B:B").Clear
For i = 1 To .Count
With .Item(i)
ActiveSheet.Hyperlinks.Add _
anchor:=ActiveSheet.Range("B1").Cells(i, 1), _
Address:="outlook:" & .EntryID, _
TextToDisplay:=.Subject
End With
Next
End If
End With
End Sub

'<<<<<<<<<<<<<<<<<<<<<<<<<<



NORMAL module:
'
Option Explicit

Dim mclsOLS As COutlookSearch

Sub CreateMailLinks()
Set mclsOLS = New COutlookSearch
mclsOLS.SearchSubject "info", , False
mclsOLS.SearchWithAttachment -1
End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Deepu wrote :


Hi XL Experts,

I receive mails daily with excel attachements from a specified source
and I need to open the attachement to check if a specified word is
there in that attachement.

If found then I need to reply to that mail with some specific info,
otherwise(if the word not found) ignore the mail.

Could someone kindly advise on this.

Reading mail attachements
 

Hi KeepitCool,

Thanx a ton for your reply. And you guessed it rightly it all sounds
abracadabra to me.. :mad:


I want a macro to be triggered whenever a mail comes from a specified
source with an attachement. And I don't want to search my inbox for the
existing mails, as I want to automate this for future course of action.

Well I don't expect you to explain the code, but atleast guide me where
to use this code..

Thanx for your time.


--
Deepu
------------------------------------------------------------------------
Deepu's Profile: http://www.excelforum.com/member.php...o&userid=21162
View this thread: http://www.excelforum.com/showthread...hreadid=380458

Reading mail attachements
 

Deepu,

KISS:
dont program..
create a rule in Outlook to alert you.
then do whatever needs to be done manually.

Else:

Classmodules are for experienced programmers only.
I dont want to sit down and write the complete code for you.
maybe by googling you may find a more appropriate example.

If you have enough experience than you can analyse
my earlier code and adapt as needed with relative ease.

create a classmodule in excel.

the class must logon/start outlook when instantiated.
the class must monitor the newmail event in outlook.
and run a procedure when the event is triggered.

the class must terminate the outlook.application variable
when it (or it's container) is terminated

my earlier code does similar things.. but reacts to seach_complete
event.





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Deepu wrote :


Hi KeepitCool,

Thanx a ton for your reply. And you guessed it rightly it all sounds
abracadabra to me.. :mad:


I want a macro to be triggered whenever a mail comes from a specified
source with an attachement. And I don't want to search my inbox for
the existing mails, as I want to automate this for future course of
action.

Well I don't expect you to explain the code, but atleast guide me
where to use this code..

Thanx for your time.

Reading mail attachements
 

kIc,

Outlook 2002 and higher have VBA too, and you can kick off VBA Proc
from an Outlook rule(this option really rules BTW;). This would make it
a *lot* easier, but proably still too hard for Deepu.(Sorry Deepu;)

Dm Unseen