Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Automaticall updating an excel file when new email received

    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.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 976 Times in 906 Posts
    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

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    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).

  4. #4
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Quote Originally Posted by jscher2000 View Post
    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.
    PJ in FL

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    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

  6. The Following User Says Thank You to jscher2000 For This Useful Post:

    pjustice57 (2011-04-05)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •