Call macro stored in Excel workbook from Outlook's macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to run macro function "ExcelTestMacro" stored in "C:\Test.xls"
(which is already open), when the new mail massage comes.

I can call macro in Outlook from Excel easily for example by :
Call Outlook.Application.SendNewMail("(e-mail address removed)", "Test","C:\test.xls")

But I could not call Excel macro from Outlook that way.
I allowed access to Visual basic Project and tried:
Set MyExBook = Excel.Application.Workbooks.open("C:\Test.xls")
call MyExBook.ExcelTestMacro
but there appears Run-Time error 'Object does not support this property or
method'. Of course I'm doing something wrong...

Somebody please help and tell me what to do
 
Am Sun, 1 Oct 2006 14:16:01 -0700 schrieb Gvaram:

The Excel Application object knows a Run method. Probably that helps.
 
I tried "Run" but there is the same result.
I don't know exactly, but I think the problem is in the way I'm connecting
to the excel file. I tried:
1. GetObject("C:\Test.xls")
2. Excel.Application.Workbooks.Open("C:\Test.xls")

I don't know is there any other way to open excel file and call function
stored there? Logically, if excel macro can call outlook macros, Outlook
should do the same also - both of them are microsoft products and use VBA.
But how???

Pleeease help....
 
Am Mon, 2 Oct 2006 09:56:02 -0700 schrieb Gvaram:

Please read the VBA help for the Application.Run function, there´s also a
sample. As I understand it, you don´t need to open the workbook, but need
the Excel Application object.

Calling Outook VBA methods from outside isn´t documented, i.e. it works for
the time being but you can´t rely on that. And there´s no reason that it
must work for any other application, too.
 
Michael,
I read this help topic but had some problems - in outlook I wrote:
Excel.Application.run("C:Test.xls!GetDataFromMail","aaaaa") , but it coused
error "The macro 'C:\Test.xls!GetDataFromMail' could not be found."

I'd like to describe the whole process in detailes to make it clear:
My target is to initiate macros in excel file when Outlook receives a new
mail . For this purpose I wrote in "ThisOutlookSession" following Sub:

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

With this Sub I'm getting EntryID of recently received mail message.
On the other hand, in Excel file "C:\Test.xls" (which is already open) I
have another Sub which opens mailitem's attachment (I know EntryID from Sub
provided above), copies it in one of it's sheets and then begins processing
of data provided there:

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
Call DataProcessing 'This is another function in Excel
Kill ("C:\Book1200.xls")
Else: End If
End Sub

Individually, both Subs are functioning well.
So, the target is to call "GetDataFromMail" from "Application_NewMailEx", so
that new mail with the special excel file attachments must be processed
automatically.

Please help me to do this or tell me other solution...
 
Am Tue, 3 Oct 2006 10:39:02 -0700 schrieb Gvaram:

Sorry, more I don´t know about that. Maybe it´s better you ask in an Excel
group.
 
Back
Top