Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call macro stored in Excel workbook from Outlook's macro
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 MailItems 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 MailItems 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(MailItem ID) 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 ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro call, one workbook to another | Excel Programming | |||
Call Macro from another WorkBook? | Excel Programming | |||
How can I call a macro in another workbook? | Excel Programming | |||
How to call a stored procedure in a macro | Excel Programming | |||
Call a macro in other workbook | Excel Programming |