[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2098
  • Last Modified:

save excel attachment from outlook email into access table as a record

We have Time Out excel form that users fill out and send as attachment, we would like to import that file into access table as a record and mark that email with attachment as processed so it doesnt duplicate. The end result is a calendar report listing people will be out during a month.
0
Harter
Asked:
Harter
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
Nico BontenbalCommented:
I'm not sure what you question is but it is possible to built something like this. You can even set up rules in Outlook to automatically process the e-mail. But maybe you should take one step back and look at the functional requirements and then try to find the best technique to built this tool. I think there are easier ways to do this then by emailing Excel attachments. If you have SharePoint for example you could use that. And MS Access also has this:
http://office.microsoft.com/en-us/access-help/add-the-data-collected-through-e-mails-to-your-access-database-HA010341539.aspx
It's far from perfect but if it's sufficient it would save you a lot of time.
Or you could set up Google Forms, or something similar. Or create a small asp.net application that collects the data.

But if you are sure the Excel attachments are the best way to go I suggest you start with some VBA code in Access. If you don't know VBA I suggest you find someone who does. Searching through an Outlook mailbox, opening a mail, saving the attachments and then import the data from this attachment is not a piece of code a beginner can write.

I suggest you break the task up in these 4 steps:
1. 'Read' the Outlook inbox from MS Access
2. Open the mail
3. Save the attachment
4. Import the data
5. Move the mail to another folder (that's the technique I would use to 'mark' the mail as processed to avoid duplicates.
You'll be able to find many examples of these separate steps, but be prepared to invest quite a lot of time to combine them all in one working piece of code.
Also make sure everybody uses the same excel template for the data. Protect this Excel sheet so all the users can do is enter specific data in specific columns. You don't want users to enter 'next monday' for example in a column where Access expects a date.
1
 
PatHartmanCommented:
@Nicobo,
The email feature of Access 2010 you linked to has been deprecated in A2013 so I don't think I would create new procedures to use it.  Like many features, this one was flawed and rather than make it better, MS elected to get rid of it entirely.
0
 
Nico BontenbalCommented:
Thanks for pointing that out. I wasn't aware of this.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Helen FeddemaCommented:
This is certainly possible, but a lot more details are needed.  The attachment would have to be saved to a folder, then code written to extract the data from the workbook and place it into one or more Access tables, possibly with data type conversion, aliasing, etc.  As far as marking the email as processed is concerned, you could use one of the standard fields that is not displayed, such as Mileage, or a custom category.  For this to work, the workbook format would have to be standardized -- best to create these workbooks from a template, to ensure consistency.

My ebooks Working with Excel and Working with Outlook could be helpful here.  They deal with exchanging data between Access and Excel, or Access and Outlook, using VBA code.
0
 
HarterAuthor Commented:
Thank you for your comments, here are more details on this: The time out request is sent out as a form (excel template) to several HR people, who then update word document with that information - word document looks like a calendar. Only HR can edit the calendar. I guess the VB code to process the attachment is not going to work because it needs to run on 1 computer, what if that HR person is out of office....
Also, sharepoint form wont work since they would like ability of the time off requestor to edit their requested time, cancel if need be and email notification must be sent out to HR people. Also, there would be no duplication functionality present since the user can ask for overlapping time off, how would the system know the user already requested other time off....
0
 
macarrillo1Commented:
A Excel/Attachments solution does not scale up very well.  How big is your organization and how many users are there?

From what you are describing, a SharePoint Calendar solution might be a better option.  In this scenerio each user would have a personal view of the Calendar and update it as necessary.  The Calendar should have versioning turned on so you can track all changes. HR would see the composit view of this calendar.  Additional functionality could be added to the calendar to track when people make updates, send notification to manager that employee has requested time, Canceled Time, etc. You could even set up workflows to update another list containing employees vacation time that would send notifications to employee and HR if available time is exceeded.
0
 
HarterAuthor Commented:
We have around 150 support staff who will be using the calendar and from what you are describing, there will be 150 versions of the calendar?
0
 
macarrillo1Commented:
Not really 150 versions.  Each employees view(think filter) would only see their entries.  Then there would be an Admin(HR) view that viewed everyone. Only HR would have permissions to see the Admin View.

Although, it might be desirable for the calendar to show everyone requested time off.  As team members would know that they should not ask for time off when the only other person that can do their job is taking off.  It might allow for better coordination.
0
 
HarterAuthor Commented:
Right now everyone can see the time off calendar it word format so they know who is out and plan better coverage for people who are out. HR are the only ones who can edit the document. HR group gets the email and then updates the calendar as time out requests come into the shared mail box. I am not understanding how SharePoint calendar will work for this. Can you elaborate more?
0
 
macarrillo1Commented:
If this is a Public Calendar that makes it easier.  
You would create a calendar for Request days off that everyone can see and edit.  
The calendar would have versioning turned on.  This means that any changes made to the calendar would be tracked.  So If I make an entry and modify it later it would track that change.  It would also track if I changed someone elses time off.  This calendar could have a workflow that sent an email to HR and the person requesting the time (protection against someone changing someone elses request).  Then you could create a separate calendar; Approved Days Off that HR managed and is viewable (read only) to the public.

There are a number of additional functionalities that could be added:
-Notification to Manager that employee is requesting time.
-Approval Request to Manager for employees requested time.
-Prepopulate Approved Days off with Requested Time off but show status of Pending.
-Monthly report of total time taken by employee.
-etc.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now