Automaticall updating an excel file when new email received

Topic

New Reply

I hope I am putting this in the right place since this covers off on both Outlook and Exel.

Here is the problem: I have an excel file for which every hour I am emailed an update for.

What I would like to happen: when a new email arrives the master file is updated with all the new information. sometimes there is information in the new file, sometimes it is just the headers with no info.

The master file has tabs for each day but when new info is added in the name is a revision number for the day.

The name of the recieved file is always different containing date and revision info

ex: rma_hourly_2011-03-31-21_00_04.xls

where the 21 is the release hour in military time.

I am thinking something like the email arives, get moved into a folder, the contents get exported to the master file, the email is marked as read.

I am totally lost in outlook so I have no idea where to start. any thoughts.

Reply | Quote

Replies

I think you need a trigger that fires off a VBS script to update the spreadsheet. Unfortunately this is rather complex and if you are not good at writing VB script you will struggle – I can’t see anyone here writing it for you.

cheers, Paul

Reply | Quote

Conveniently, since all of the file names are unique, you could save the attachments to disk without having overwrites. We have had threads in the past on saving attachments, but I’m not sure whether these were triggered by receiving a new message. Outlook (since 2002?) allows you to connect a VBA procedure to a rule, so if you find a VBA procedure (macro) to examine and save selected attachments, you should be able to fully automate that part. (Note that in the rules interface Microsoft called it a VB Script, but it actually is a VBA procedure that takes the mail item as a parameter.)

In the past, we have had some solutions that monitored a folder for new files. I can’t recall whether these were Windows-based solutions or Office-application based solutions.

Assuming you can get those first two parts to work, then you would “just” need the Excel code to insert the new rows.

Admittedly, a lot of things need to work right for this to happen.

Alternately, you could do everything in Outlook VBA, i.e., have the rule-driven procedure launch the attachment in Excel and automate Excel from Outlook. It’s probably convenient to write your Excel code in Excel, then figure out what little changes are needed to make it work from an Outlook module. For clarity, when you refer to global Excel objects, you might want to preface them with Excel. (e.g., Excel.Application).

Reply | Quote

Outlook (since 2002?) allows you to connect a VBA procedure to a rule…

Could you expand on how to make that work? Using Outlook 2007 on XP SP3 Home, I set up a new rule, but when I select “runa script” the “Select Script” dialog gives me a blank list. I’ve put “public” subs in both ThisOutlookSession and a module, but nothing shows up.

Reply | Quote

The Sub must have a parameter for the message. Microsoft gives this example:

Code:

Sub CustomMailMessageRule(Item As Outlook.MailItem)
   MsgBox "Mail message arrived: " & Item.Subject
End Sub

Source: How to create a script for the Rules Wizard in Outlook

Reply | Quote