Can it be done using VBA in Excel?

[jrobin5881]

I'm asking for opinion here before I start on a project to see if I'm expecting to much from VBA.

At work there is a query that runs each Monday and Wednesday against a database and it sends me an email in outlook to tell me that my data is ready. I click the link and it opens a dialog box to allow me to view or save the data in excel. I save the files to a shared drive on the LAN and I have a template workbook that runs VBA for me and provides me a finished report from several database files.

What I was wondering was could I extend the current macro stored in the template workbook to automatically go into my email, find the message and open it to run the query against the database and store it on the shared drive in the LAN? or is this too ambitious and beyond the scope of VBA?

Any thoughts or opinions?

 

[xlbo]

Should be doable I would've thought

You can open & control Outlook using VBA (although the object model is quite "unique"

To be honest though, you probably don;t really need to use Outlook - just use your Excel Template file to run the query in the database and save to the LAN. There are loads of examples of how to run a query in a database if you search the archives here.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ClulessChris]

Some of what your asking is certainly possible from Excel Or Outlook (2000 or later). The issue may be following the link in the message, depending on:
What the link is too, and does this change each week?
What format is the mail?
is there any other text in the mail field that contains the link.

Do you even need to access this message or could you not run the query direct from Excel?

Everybody is somebodys Nutter.

 

[jrobin5881]

I'm seeing that the message in Outlook is in HTML format and below is a copy/pasted example. I don't think I can run the query directly from excel because I have to open this other application, select the stored query and run it. Thats why I use the subscription service that it has so that part is done for me automatically.

This subscription is from the new eFlash system. Your Query is ready to be retrieved. Click this link to RETRIEVE REPORT.

To unsubscribe or to make changes to your subscription service please click Here.

 

[pbrodsky]

What does the link itself (the URL) look like? Does the URL change, and if it changes, are do the changes follow a pattern? Does the service require a login/password?

 

[jrobin5881]

The link says RETRIEVE REPORT and it doesn't ask for a password or login if you click the link. But, if I access the application outside of the email I have to.

 

[matrixindicator]

Hey Jrobin, what kind of database are you talking, is it access (or a few access databases) ?

If it is access, access can save himself a file (result) on the LAN and open an excel file that runs on this data your data. To be complete you can list a few actions and we could try to say if this can be done automatically along vba. Just for help, not obliged.