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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for pointing that out. I wasn't aware of this.
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.
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.
ASKER
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....
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....
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.
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.
ASKER
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?
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.
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.