Using an Access Email to kick off an Access Database macro

[lykaon78]

About 5 times a week I get a specific email that kicks off a 4 day process. On each day of that 4 day process some manual intervention is required on my part. Some of that work is work in a reporting database.

I'm basically wanting outlook to kick-off a procedure that will run in access to take care of that reporting work.

Anyone done something similar to this? Any tips?

I'm accomplished enough in VBA and Access to program all the routines I need to run to complete all the reporting work in Access but I'm just having trouble figuring out how Outlook can tell Access to start that routine.

Any help is appreciated.

 

[Cogman]

If there is any way to schedule this (first monday of the month stuff?) then that would be a better approach than relying on outlook.

If the email is manually generated, you are sort of sunk (unless the person writing you truly only ever writes you to let you know that it is time to kick off the 4 day process). You can look for key words in the email, but ultimately you don't want the 4 day process kicking of because someone replies with the right combo of words to make it happen.

If the email is automatically generated, then this shouldn't be too bad to deal with. I would suggest looking here for how to get started. (look at the top answer, ignore the rest).

http://msdn.microsoft.com/en-us/library/ee814736.aspx

This should be a good place to get started.

http://msdn.microsoft.com/en-us/magazine/cc301799.aspx

This should also help.

 

[lykaon78]

If there is any way to schedule this (first monday of the month stuff?) then that would be a better approach than relying on outlook.

If the email is manually generated, you are sort of sunk (unless the person writing you truly only ever writes you to let you know that it is time to kick off the 4 day process). You can look for key words in the email, but ultimately you don't want the 4 day process kicking of because someone replies with the right combo of words to make it happen.

If the email is automatically generated, then this shouldn't be too bad to deal with. I would suggest looking here for how to get started. (look at the top answer, ignore the rest).

http://msdn.microsoft.com/en-us/library/ee814736.aspx

This should be a good place to get started.

http://msdn.microsoft.com/en-us/magazine/cc301799.aspx

This should also help.

Thanks, I will start there. The email is automatically generated but based on the email receipt of a fax. Fortunately it will be very easy to identify the email when it arrives.

 

[KLin]

You should look into using vba code in outlook to evaluate the subject line of incoming emails. If a match is found, have the vba method open up access and run the reporting database. You can set a macro to start up automatically as soon as an access database file is opened.

 

[Tweak155]

This thread inspired me to write something like this at my work. I got it working already.

It works somewhat how KLin describes but instead of opening Access I just query the DB through VBA and return the results via the body of the e-mail as the 2 links above go over.

My method:

Read subject of e-mail only
Look for indicator characters that this is a query (for me I surround by % symbols)
Use what is in between the % symbols as my query data (this is a unique key for me)
Query the DB's and format the body of the return e-mail based on data retrieved.

I can now get information on tickets via e-mail from anywhere rather than using our company's GUI. Suhweet.

 

[KLin]

This thread inspired me to write something like this at my work. I got it working already.

It works somewhat how KLin describes but instead of opening Access I just query the DB through VBA and return the results via the body of the e-mail as the 2 links above go over.

My method:

Read subject of e-mail only
Look for indicator characters that this is a query (for me I surround by % symbols)
Use what is in between the % symbols as my query data (this is a unique key for me)
Query the DB's and format the body of the return e-mail based on data retrieved.

I can now get information on tickets via e-mail from anywhere rather than using our company's GUI. Suhweet.

Very nice.

 

[beginner99]

Or you could just schedule the report to be run everyday say at 8:00 am assuming that is feasible performance wise. Then you will always already have the report once the mail arrives.

 

[WannaFly]

This thread inspired me to write something like this at my work. I got it working already.

It works somewhat how KLin describes but instead of opening Access I just query the DB through VBA and return the results via the body of the e-mail as the 2 links above go over.

My method:

Read subject of e-mail only
Look for indicator characters that this is a query (for me I surround by % symbols)
Use what is in between the % symbols as my query data (this is a unique key for me)
Query the DB's and format the body of the return e-mail based on data retrieved.

I can now get information on tickets via e-mail from anywhere rather than using our company's GUI. Suhweet.

Just be sure to sanitize/parameterize(is that a word?), even though its for your own person use, an email with the subject of:
Hi, how are %you'; delete from users; --%
could be very dangerous.

 

[Evadman]

Just be sure to sanitize/parameterize(is that a word?), even though its for your own person use, an email with the subject of:
Hi, how are %you'; delete from users; --%
could be very dangerous.

I use the same process that Tweek155 uses, except I have the process hooked to many database systems. Instead of having the query in the body of the email, I put it in an attachment. The body of the email specifies:

The system to run the query against
If the query is DDL or a result set is expected
If a result set, the return type (text/excel/whatever file, name, delimiters, encryption process to use, etc)
The return location (email, ftp, share, etc)
Time to run the query if not immediate

I use a process to make sure that the email is actually one that should be processed and passed on that is beyond using the '%' characters. To simplify, the email must be in an exact format along with passphrases that only I know. Since email is basically unencrypted, I use a rotating passphrase that changes based on several factors including the time, day, month, year, source email, destination system, database user, and past queries. The passphrase changes for each run, and is never the same. I only get 1 chance to enter it correctly each time, or all subsequent attempts are ignored until I reset the system.