# Solved: Run excel macro when new email is received



## alvesm1 (Jul 16, 2009)

Hello

I need to come up with a simple outlook macro (or script?) that fires off whenever a new email meeting certain criteria is received.

1. new email is received with "please refresh quotes" in the subject
2. outlook runs a macro that causes another macro to run (the second macro is located in a excel workbook which is already running in my office pc)
3. the excel (attached) is simply refreshing market prices and emails whenever the macro is triggered. because the quotes are obtained via a DLL feed I can only get it at work.

the purpose is simply to get live market quotes on my personal email from my work email whenever i need.


thanks in advance for your help.


----------



## vane0326 (May 8, 2006)

First copy this code below and go to TOOLS >> MACRO >> VISUAL BASIC EDITOR then right click on "Microsoft Office Outlook" click on INSERT >> MODULE and paste the code in white area. Then go to the top of the menu bar click TOOLS >> References >> and look for *Microsoft Excel 11.0 Object Library* or *Microsoft Excel 12.0* Object Library and check mark it and save it. close out Oulook and reopen it.

Now go to TOOLs >>RULES and ALERT >> NEW RULE >> click on CHECK MESSAGE WHEN THEY ARRIVE >> CHECK MARK "Specific words in the Subject line" then at the bottom click "Specific words" and type "Please refresh Quotes" (Without the quotes) click add then click next and check mark "run script" then at the bottom box click "a script" and select ok and click finish.


```
Sub CallExcelMacro()
Dim eApp As Excel.Application
'Note: Must have set reference to:
'Microsoft Excel 11.0 Object Library
'This is accessed in the VBE Editor
'Under Tools -> References...
    Set eApp = GetObject(, "Excel.Application") 'Assumes Excel is open,
        'Use CreateObject if Excel is not open.
    eApp.Run "Button1_Click"
End Sub
```


----------



## alvesm1 (Jul 16, 2009)

Thanks vane0326​
I did everything you said but when I am creating th rule and click the "a script" hyperlink it doesn´t list any scripts​
can you help?​
Thanks again​


----------



## vane0326 (May 8, 2006)

I left an attachment that shows if you have created a module in your outlook vba editor. Also, you might have to turn off your macro security. Go to TOOLS >> MACRO >> SECURITY >> and turn off your macro security.


----------



## alvesm1 (Jul 16, 2009)

Thanks for you reply vane but still nothing.

I have my vb editor exactly as it should look like and i have the macro security setting as low, and it´s still not listing any scripts whenever I create a rule.

Thanks again.


----------



## vane0326 (May 8, 2006)

Use this instead. Copy this code below and paste it into the new module.


```
Sub ExcelMacro(MyMail As MailItem)

Dim objMail As Outlook.MailItem
Dim eApp As Excel.Application

'Place path to sav to on next line. Note that you must include the
'final backslash
    Set eApp = GetObject(, "Excel.Application") 'Assumes Excel is open,
        'Use CreateObject if Excel is not open.
    eApp.Run "Button1_Click"

End Sub
```


----------



## alvesm1 (Jul 16, 2009)

Thanks vane, I am away from office until monday the 27th, I will try it then and let you know.


----------



## alvesm1 (Jul 16, 2009)

Perfect it works, thanks indeed Vane.


----------

