automating macros

[selkov]

How can I right a macro that will run when a specific email arrives?

[Killian]

Open the Outlook VBE
Select ThisOutlookSession in the Project Explorer
Select Application from the first dropdown above the code window
Select Application_NewMail from the event dropdown
what ever code you type in this procedure will run when a new mail item arrives in your inbox
You will have to test the new mail to check it meets your criteria (i.e. sender or subject) and run your code if it's met

[selkov]

Thanks for such a quick response.

I will play with that & let you know what happens.


Eds

[selkov]

This is still beyond me.
I need more help.

Outlook right now finds the email from "RL" & will move it to the sub folder "RL\*" * represents the subdirectory based on the subject matter. For this example let us use teh subject of ".zip". All emailf from "RL" with a .zip attachment are moved to "RL\zip".

At this point I need to run a macro that will open the email, open the zip file, open the .xls attachment & run an excel macro.

I have made no attempt at sytax as I do not know where to start. So below is a basic outline.

I am invisioning something along the lines of
sub new mail id()
nm= [subject of new mail]
if nm = [criteria] then run macro 1
end sub

macro1()
open new mail
open *.xls
run [xls macro "process"]

Can anyone supply me with some code that will do this or something like this so that I can learn from it?

[Killian]

hi again

well I'm at home for the weekend now and I don't have outlook here to try stuff out but I think you might be a little ambitious with this one.

Lets take it one step at a time...
We know we can save the attachment but I don't think it's possible to open it (virus distribution would be scarily easy). Even so, VBA doesn't know how to operate a zip file. I suppose though, Outlook could store to file path and pass it to a windows shell command since zip folders are supported in XP. I'd have to look that up.
Then, if we know the name of the extracted file, we could create an instance of XL from outlook, open the file and run the macro
Is the macro in the attached file already? Man, this is getting complicated...

I think you need to look at two separate processes here: using VBA in outlook to save the zip files to a folder then, having extracted your files, use VBA in Excel to do stuff there
I could be wrong... maybe if you get a bit more feedback from the forum you can decide on a structure thats going to work and we'll take it from there

Hope this helps...
K :-)

[selkov]

What i am doing manually right now is from within Outlook clicking on the email,, this open the email. Clicking on the .zip attachment, this opens an expolorer window that shows me the .xls file [actaully i use powerdesk explorere not ms explorer by default]
then i click on the .xls file wich opens excel. I then click the macro button & i am done.

How do I get VBA to read the Subject of an email?
outlook has a "Rule" that says "Perform Custom Action" can I ge tthis to run a macro ?
How Do I write a custom action?

I now I am greedy.
I do appreciate your time though.

Eds

[Zack Barresse]

Hi there,

This may be of interest to you ...

http://puremis.net/excel/cgi-bin/click.cgi?cnt=c015&url=http://puremis.net/excel/code/015.shtml

[selkov]

Yes & no.
It posses some interesting options, however the KEY for me is to have it automatically executed from within outlook WHEN the EMAIL arrives.

Put there is possibilities.


thanks

[mdmackillop]

Another possible approach: (never tried it, but how I would think about going about it)
MD

Within outlook
1. Save the zipfile with a fixed name to a known location
2. Open an Excel file containing an AutoOpen macro
Within Excel
1. Run the code to extract the zip file ( I assume this can be done)
2. Run the process on the extracted file