Collecting data in MS Outlook

[David]

Hi,
I try to collect data through MS Outlook 2007 in HTML format. In the wizard
I switched on the option automatically process replies. Unfortunately I
always get an error message that I have no permission to add data into MS
Access database despite the fact I'm owner of the database and I'm a member
of the admin group. The replies are filled in correctly. The database uses
user-level security and is in MS Access 2003 format, but of course I have
installed MS Access 2007 on my computer. The database isn't open exclusively.
If I try to export data manually, the same error message occurs.
Any help would be appreciated.
David

 

[Arvin Meyer [MVP]]

Your question may be better answered in an Outlook newsgroup which is
appropriate to your problem. This newsgroup is dedicated to the Microsoft
Access database product. The Microsoft website may have misdirected you.

 

[David]

Hi Arvin,
Thank you for your answer. I think my question is related to Access, because
I'm trying to collect external data which I would like to use afterwards in
Access database. Actually my post should be placed in section Import/Export
data. Of course I put it also there, but unfortunately nobody has answered.
So to put my post here is my second chance.
Thank you for your interest.
David

 

[Arvin Meyer [MVP]]

Here is some code I have that I haven't tested. Also if you want to save
these inthe database, just read the variables into a recirdset using
..AddNew:

Try the code below. You will also need the Microsoft Outlook 11.0 Object
Library selected in the References list (or whatever version of Outlook you
are using).

Dim fdrInBox As Outlook.MAPIFolder
Dim MailItem As Outlook.MailItem
Dim vSubject As String
Dim vBody As String
Dim vSender As String
Dim vSenderName As String
Dim vBCC As String
Dim vSentDate as Date

Set fdrInBox = GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
For Each MailItem In fdrInBox.Items
vSubject = MailItem.Subject
vSender = MailItem.SenderEMailAddress
vBody = MailItem.Body
vBCC = MailItem.BCC
vSenderName = MailItem.SenderName
vSentDate = MailItem.SentOn
Next
Set fdrInBox = Nothing
Set MailItem = Nothing

This code cycles through all the e-mails in your In Box and copies the
various fields to variables. In practice, of course, you would copy them to
an array or a table within the For-Next loop. You will probably also need to
filter out the particular e-mails that you are interested in based on the
Subject field (or whatever). Anyway this will give you a starting point if
this is what you are after.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David]

Hi Arvin,
Thank you for your suggestion, but I think it doesn't solve my problem.
Collecting data in MS Outlook ensures that the required data are
automatically stored in MS Access database. The procedure is described for
example here
http://office.microsoft.com/en-us/access/HA102527131033.aspx?pid=CH100739911033.
If I use your OLE Automation solution, I should create a procedure which goes
through the e-mail body (MailItem.Body), recognizes the required data stored
in the body and saves it into a table in the database. The second
disadvantage of your solution is that it isn't automated. Of course I can
create a scheduled task in MS Windows Vista to open the database, run the
code and close the database, but I think it isn't smart, because I don't know
who will respond the e-mail, when and how many times.
Anyway thank you for your help.
David

 

[Arvin Meyer [MVP]]

Hi David,

The link you showed only uses Microsoft Outlook 2007 and Microsoft Access
2007 using ONLY the ACCDB/ACCDE format. I believe that your recipient also
must be using Outlook 2007 as well.

The MDB format that you are using does not support the above. You can (and
must if you want this feature) convert to the new format. I also suggest
that you build a Trusted Location for your database.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David]

Hi Arvin,
Thank you for your comment. You are right. If I convert my database to
Access 2007 format, it functions. I derived benefit from the book Microsoft
Office Access 2007 Inside Out by John L. Viescas and Jeff Conrad. In chapter
6 there is written that this new feature is implemented in Access 2007. But
nothing about required Access format. I tried collecting data also in Outlook
2003 and it functioned. This is also mentioned in the book. If you use HTML
format for collecting data, any e-mail client which can work with HTML format
is supported.

When I was converting my database, I encountered another problem. I first
tried to save a copy of my database by clicking on Office button -> Save As
-> Access 2007 Database. Unfortunately an error occurred that the user David
opened the database exclusively. But actually the database wasn't open
exclusively. Even David is the owner of the database with admin permission.
As I mentioned in my first post I use user-level security. Then I created a
blank database in Access 2007 format and copied all the objects into it. But
the disadvantage is that I can't copy or create custom toolbars. And
therefore I can't use my database in Access 2007 format.
Thank you for your interest.
David

 

[Arvin Meyer [MVP]]

In addition User Level Security is not supported in the new format. The
Toolbar problem may not be as big a problem as you think if you can add a
menu form as a small popup form, or even put the commands you need directly
on the new form. You can also create custom ribbons to do the work of your
previous custom toolbars.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David]

Hi Arvin,
Thank you for your reply. All my databases use Access 2003 format, so Access
2007 format is quite new for me. In the below mentioned book there is
described how to create custom ribbons through XML. The authors acknowledge
it's a little bit complicated. And I agree. But I think I could figure it out.

What I can't figure out when converting to Access 2007 format is user-level
security absence. I was very frightened when I read your post. In the book
there is written I have only two possibilities: to convert the database to
SQL Server or to use SharePoint Server. Unfortunately I'm not familiar with
SQL Server and SharePoint Server. Both possibilities need some other
expenses. So the best choice for me is to stay in Access 2003 format and
collect data by another way. I'm curious if Access 2010 has the same
restrictions.
Thank you for your help.

David

 

[Arvin Meyer [MVP]]

Actually, a 3rd possibility is to not convert to the new format. Leave it as
an MDB. You can still collect data via email, but it is more difficult. You
can write code to read the subject and scan the body for keywords. Even
easier is a product written to work with Outlook called Easy Survey:

http://www.mapilab.com/outlook/easy_survey/

I haven't used it in several years, but as I recall, I was able to read
responses into an Access 2000 format database.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Disclaimer: Any code or opinions are offered here as is. Some of that
code has been well tested for number of years. Some of it is untested
"aircode" typed directly into the post. Some may be code from other
authors. Some of the products recommended have been purchased and
used by the author. Others have been furnished by their manufacturers.
Still others have not been personally tested, but have been
recommended by others whom this author respects.

You can thank the FTC of the USA for making this disclaimer necessary.

 

[David]

Hi Arvin,
Thank you for your suggestion. I looked at Easy Survey web page. I think it
shouldn't be a problem to create own code in agreement with below mentioned
procedure. But as I wrote in my 3rd post, I think the problem is the
procedure isn't automated. It would be great to write a code in Outlook. When
such e-mail arrives, then Outlook opens Access database, runs code in Access
for data processing and then closes Access database. Unfortunately I'm not
familiar with programming in Outlook.
Thank you very much for your help.

David

 

[Arvin Meyer [MVP]]

I have only a small experience in programming with the Outlook object model.
There are 2 folks who are excellent however and have written books which
will help you. They are Sue Moser and Randy Byrne. Search Amazon for their
books using their names, and I'm sure you'll find something. You may also
get more help at the Outlook newsgroup.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com