Solved

Userform in Outlook connected to Access

Posted on 2014-04-24
6
467 Views
Last Modified: 2014-05-02
Hi Experts,

I work in a small company, where my team of 4 receive perhaps 100 emails a day with various attachments relating to different products.  Currently its a mess how we organize these emails.

As a possible solution I would like to build an Outlook userform which would be used to update an Access database which would record several features of each email received.   In my mind the userform would have the following features:

1) the email would be selected within outlook and then the userform activated. The subject heading of the selected email, time/date etc would be passed to Access automatically.  

2) there is an input box in the userform where the products name the email relates to is entered.   Now there are 500 or so different products with rather long names - some are very similar with perhaps a number on the end only being the difference.  Ideally there would be some sort of ability that where as letters are entered available a product name which contain those letters can be selected from multiple options (from a list of products names contained in Access).  Not sure what would the best way to do this is - but for the tool to be adopted by the team its important this step is easy, quick and pain free!

3) several checkboxes classifying the email into what type (eg order, customer query etc) - there are only 5 or so types here

4) some other input boxes where various comments can be entered - these don't have to be verified.

5) enter button where the info is passed to access

My questions are whether each of these parts are possible (with an emphasis on 1 and 2) and some directions on how to go about it.  As a bit of background I have 5 years or so background in VBA (99% in excel though)/Access and am fairly competent although limited use of useforms.  

Thanks for your time!
Matt
0
Comment
Question by:matt_m
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40023076
Yes you can do it but this might be easier if you do it from the Access side.  Access can link to Outlook tables.  But I don't think Outlook forms can link to Jet/ACE tables so that leaves you writing a lot of code.
Outlook foldersTake a look at what you can see from Access to see if you can possibly automate any of this.  The benefit of doing this from Access is that you can use bound forms.  If you do this as a user form in Outlook, you'll need to write DAO/ADO code to populate the form when you want to view data and code to save the data when you want to update/add it.
0
 

Author Comment

by:matt_m
ID: 40023990
Thanks Pat - yes I expected that there would be lots of ADO code involved - I've done a lot of similar stuff with Excel/Access interaction that's been really successful.  The solution really has to be on the Outlook side as getting the rest of the team to use Access will be a challenge.
0
 

Author Comment

by:matt_m
ID: 40024057
As an update - I've being googling around and have managed to assemble code to connect to Access and populate a table with properties of the currently selected email. What I really need a hand with now is ideas on the best solution to point 2 above.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 37

Accepted Solution

by:
PatHartman earned 250 total points
ID: 40024706
getting the rest of the team to use Access will be a challenge
But they wouldn't be using "Access".  They would be using an application that you created for them which is totally different.

Since I work in Access, I build Access forms rather than user forms.  Doesn't the user form have a combobox control?  You might have to rig up something that does a VLookup() if you don't have a combo.
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 250 total points
ID: 40026230
I have done a number of custom Outlook forms from which data is imported to Access tables.  I don't think a UserForm would work here, because Userforms live in an Outlook project.  The one the user would fill in is in their project, not yours, and in any case UserForms are not bound to data.  So I think you would have to design a custom Outlook form, make sure your users all have this form, and that data they enter comes though to you when the form is received.  Then you can retrieve the data and put it into Access tables.
0
 

Author Closing Comment

by:matt_m
ID: 40038724
Thanks guys - you've really given me some great ideas
0

Featured Post

Schedule a Tour of the ATEN booth at InfoComm 2017

Tour the ATEN booth to see the the Latest Addition to the Modular Matrix Switch Series, New 4K HDMI Over IP Extender and more! Enter ATEN's Ultimate Giveaway Sweepstakes for a chance to win one of several great prizes, including an ATEN US7220 2-Port Thunderbolt 2 Sharing Switch!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question