Email which runs an excel macro 1

[Cage]

Hello,

I would like to have it so when I receive an email via outlook overnight it automatically runs a macro in excel. Is it possible to run the code below directly from excel, otherwise does anyone know how I can run an excel macro from outlook?

cheers

Cage

------------------
Sub Outlook_macro()

Dim ol As Outlook.Application
Set ol = CreateObject("Outlook.Application")
Dim itms As Outlook.Items
Dim itm As Object
Dim email_received As Boolean

Do
Set itms = ol.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items

For Each itm In itms
s = itm.Subject
If s = "Refresh Ready" Then email_received = True
Next

Loop Until email_received

Call excel_macro

End Sub
----------------------------------

 

[PHV]

Why not an Outlook VBA procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cage]

Good question, I think the reason being that I would have at least two excel sessions open (at least two) and didnt know/think it is possible to distigush between them, therefore I am running the macro from a specific session of Excel. Ideally I would like to have it so the email received by outlook would then run a macro (from outlook) which would manipulate Excel session A then manipulate Excel session B. Is it possible to have session of excel/outlook manipulate another session of excel without using sendkeys or sendmessage?

Cage

 

[PHV]

Take a look at the GetObject function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cage]

Thanks PHV, I have searched on tek-tips for the getobject command and have found two possible ways of controlling another excel session. However is it possible to control an excel session which is currently active, instead of opening a new session.

Regards,

Cage

Dim xlApp As New Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("H:\myspreadsheet.xls")

with xlwb

end with


or

Dim xlApp As Object
Dim xlWB As Object

Set xlApp = GetObject(, "Excel.Application")
Set xlWB = xlApp.Workbooks.Open("h:\myspreadsheet.xls")

with xlwb
[code]
end with

 

[PHV]

After this line:
Set xlApp = GetObject(, "Excel.Application")
You can browse the xlApp.Workbooks collection to see if this is the one you want to control.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244