G
Gvaram
My target is to initiate macros in excel file when Outlook receives a new mail.
For this purposes I have 2 special Subs both in Excel file and Outlook:
1. Sub in Outlook (“NewMailEx†in “ThisOutlookSessionâ€), which gets
MailItem’s EntryID of newly received mail message (and must initiate macros
to run in excell)
2. Sub in “C:\Test.xls†(“GetDataFromMailâ€), which opens mailitem's
attachment (I have to supply MailItem’s EntryID), copies it in one of it's
sheets and then begins processing of data provided there.
For clarity I'm providing both of them:
EXCEL FILE:
Sub GetDataFromMail(MailItemID as string)
Dim Attach As Outlook.Attachments
Dim myItem As Outlook.MailItem
Set myItem = Outlook.Application.Session.GetItemFromID(MailItemID)
Set Attach = myItem.Attachments
Attach.Item(1).SaveAsFile ("C:\Book1200.xls")
Workbooks.Open Filename:="C:\Book1200.xls"
Sheets("Data").Select
Cells.Select
Selection.Copy
Windows("Test.xls").Activate
Sheets("DataProcessing").Select
Cells.Select
ActiveSheet.Paste
Windows("Book1200.xls").Activate
ActiveWindow.Close SaveChanges:=False
Windows("Test.xls").Activate
Range("A1").Select
Kill ("C:\Book1200.xls")
Call StartDataProcessing 'This is another function in the
same Excel file
Else: End If
End Sub
OUTLOOK:
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
Dim MyMeil As Outlook.MailItem
Dim intInitial As Integer
Dim intFinal As Integer
Dim strEntryID As String
Dim intLength As Integer
intInitial = 1
intLength = Len(EntryIDCollection)
intFinal = InStr(intInitial, EntryIDCollection, ",")
strEntryID = Strings.Mid(EntryIDCollection, intInitial, (intLength -
intInitial) + 1)
Set MyMeil = Application.Session.GetItemFromID(strEntryID)
If MyMeil.Attachments.Count 0 Then
If Right(MyMeil.Attachments.Item(1).FileName, 4) = ".xls" And
InStr(1, MyMeil.Subject, "App - ")>0 Then
'!!!!!???? Call ??????????.GetDataFromMail(strEntryID) ' HERE IS MY PROBLEM
MyMeil.UnRead = False
MyMeil.FlagIcon = olBlueFlagIcon
MyMeil.Save
Else: End If
Else: End If
End Sub
Individually, both Subs are functioning well, but I don't know how to call
"GetDataFromMail" from "Application_NewMailEx" so
that new mail with the special excel file attachment to be processed
automatically.
Pleeease somebody help me to do this ...
For this purposes I have 2 special Subs both in Excel file and Outlook:
1. Sub in Outlook (“NewMailEx†in “ThisOutlookSessionâ€), which gets
MailItem’s EntryID of newly received mail message (and must initiate macros
to run in excell)
2. Sub in “C:\Test.xls†(“GetDataFromMailâ€), which opens mailitem's
attachment (I have to supply MailItem’s EntryID), copies it in one of it's
sheets and then begins processing of data provided there.
For clarity I'm providing both of them:
EXCEL FILE:
Sub GetDataFromMail(MailItemID as string)
Dim Attach As Outlook.Attachments
Dim myItem As Outlook.MailItem
Set myItem = Outlook.Application.Session.GetItemFromID(MailItemID)
Set Attach = myItem.Attachments
Attach.Item(1).SaveAsFile ("C:\Book1200.xls")
Workbooks.Open Filename:="C:\Book1200.xls"
Sheets("Data").Select
Cells.Select
Selection.Copy
Windows("Test.xls").Activate
Sheets("DataProcessing").Select
Cells.Select
ActiveSheet.Paste
Windows("Book1200.xls").Activate
ActiveWindow.Close SaveChanges:=False
Windows("Test.xls").Activate
Range("A1").Select
Kill ("C:\Book1200.xls")
Call StartDataProcessing 'This is another function in the
same Excel file
Else: End If
End Sub
OUTLOOK:
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
Dim MyMeil As Outlook.MailItem
Dim intInitial As Integer
Dim intFinal As Integer
Dim strEntryID As String
Dim intLength As Integer
intInitial = 1
intLength = Len(EntryIDCollection)
intFinal = InStr(intInitial, EntryIDCollection, ",")
strEntryID = Strings.Mid(EntryIDCollection, intInitial, (intLength -
intInitial) + 1)
Set MyMeil = Application.Session.GetItemFromID(strEntryID)
If MyMeil.Attachments.Count 0 Then
If Right(MyMeil.Attachments.Item(1).FileName, 4) = ".xls" And
InStr(1, MyMeil.Subject, "App - ")>0 Then
'!!!!!???? Call ??????????.GetDataFromMail(strEntryID) ' HERE IS MY PROBLEM
MyMeil.UnRead = False
MyMeil.FlagIcon = olBlueFlagIcon
MyMeil.Save
Else: End If
Else: End If
End Sub
Individually, both Subs are functioning well, but I don't know how to call
"GetDataFromMail" from "Application_NewMailEx" so
that new mail with the special excel file attachment to be processed
automatically.
Pleeease somebody help me to do this ...