Call macro stored in Excel workbook from Outlook's macro

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top