Importing an Outlook Email (Body) into Access DB

[Boxcar74]

I think this is a little crazy but I supposed to figure out how to get the body of an email message (Outlook) into an Access DB table, with little to no effort :)

Here is the deal I receive email with the results from an online survey and I need to get the data into Access for reporting proposes.

-------------------------------------
I can get it in Access with a copy/ paste to Excel the use “Text to Columns” Then “Pate Special”/ “Transpose” to get the data into the fight format, and then save as a csv then import to Access. Obviously not the best way

I got the table set up with the appropriate fields.
Also a I have folder from my Inbox linked to Access, a make table query to only have the Body of the email in the table.

But I see no way to Link them or make the Outlook data (from the body of the message) useful.

Even with Export from Outlook to a csv, excel, or text files doesn’t come out right.

Here is a sample of the BODY of the email. The text before the “:” are the Column headings and the f’s and Strongly Agree are the records

training_class_ on: ffffffff
like_about_job: ff
change_one_thin g: fffffffffffffff fffffffff
changes_communi cated: Strongly Agree
changes_communi cated_memo: fffffffffff
tech_skill_need ed: Strongly Agree
have_skill_know ledge_memo: ffff

Really I want know if it is possible to use the linked table from the Outlook to add the survey results to my table.

Honestly I don't think it will work so if you have any suggestions please I'm take any. !!!!

Any info would be great.

[Boxcar74]

Ok, after searching and looking every where for an answer, I could be wrong but it seems the linked inbox table is really only design to work so Access can manipulate Outlook.

So it seems that barring a third party software VB or vba is required to do what I want.

I found two helpful posts on Scripts.

Retrieve Information from Outlook
www.thescripts. com/forum/thread583167-Outlook+Access. html

Application Automation
www.thescripts. com/forum/thread600219.ht ml
Since my vb is very limited I’m looking to two other options. Not really related to Access.
If I find a way relevant to this Post I’ll post it.

If anyone has any other Ideas please let me know.

-- Boxcar

[cyberdwarf]

Hi Boxcar

AFAIK, the only way to do what you want is to create and manipulate an Outlook object within your Access code.

Do you want any further info?

Steve

[Boxcar74]

SURE !!!

Any info would be great!!!

My VB in limited, but if you could start me in the right direction I'd appreciate.
it.

Thanks

[cyberdwarf]

OK, first have a look at one of today's threads called "Access - Outlook Run Time Error".

That should tell you whether your coding is up to it. Then post any further questions either on the same thread, or to this one.

Steve

[Boxcar74]

I look at that one earlier.

It is above my head, but i think i can tinker with it.

I did some VB 6 in college but it’s been a while and I've never used it in Access aside from simple macro’s and event procedures.

I put in a VBA module (in Access) and sadly I get an error "Compile Error: User-defined type not defined" on the 1st line --- Dim Olapp As Outlook.Applica tion.

Did I put the code in the right place “module1”? Could my version of Outlook affect it (2003)? Or did I just do it wrong?

My main concern is that the data needs to be transposed and I'm sure that code is much more complicated.

I don’t think this is something I can do YET, I want to learn how but I think I need to crack the old VB books.

BTW - is there or why isn’t there a data mapping feature in Access, beside the import wizard?

-Boxcar

[cyberdwarf]

I look at that one earlier.
It is above my head, but i think i can tinker with it.
I did some VB 6 in college but it’s been a while and I've never used it in Access aside from simple macro’s and event procedures.
I put in a VBA module (in Access) and sadly I get an error "Compile Error: User-defined type not defined" on the 1st line --- Dim Olapp As Outlook.Applica tion.

Did I put the code in the right place “module1”? Could my version of Outlook affect it (2003)? Or did I just do it wrong?

My main concern is that the data needs to be transposed and I'm sure that code is much more complicated.

I don’t think this is something I can do YET, I want to learn how but I think I need to crack the old VB books.

BTW - is there or why isn’t there a data mapping feature in Access, beside the import wizard?

1. Make sure you set a reference to MS Office in you VBA module
2. Have a very close look at the OutLook methods and properties available in the object model;
3. If you just want to transfer basic details (from/to/msg body, etc) it's not very complicated;
4. Google "Outlook automation vb" and see what turns up ;-)

HTH
Steve

[johngen]

Try this software:
http://www.geniusconne ct.com/

(free version limited to 49 records)

[Boxcar74]

Update: I found a way to make it work for me.
So I figured I’d post the info, if someone else finds themselves in a similar situation.

I didn’t solve the issue the VB(A) way or with that software, though it looks good.

The web page that the survey form is on was created using FrontPage.

When I realized this I know FrontPage can save data to Excel, excel would provide me a much easier import.

But after a quick search I found that FrontPage will create an Access DB for the form.

Info
http://support.microsoft.com/kb/q233406/
Easy Directions
http://www.csbsju.edu/itservices/kno...todatabase.htm


SO, I’m simply going to link the table from the FrontPage/Access DB to my Access DB.


I’m not a big fan of FrontPage (too much MS extra code) but in this instance it made my life much easier. I’m pretty sure that most web development programs can do this as well and if you are good at html you can probably figure it out.

I hope this can help anyone!!


- Boxcar

[Boxcar74]

UPDATE 2
The Powers that be didn't allow a Access DB on that server.
So I found the right way. With VBA and a GetWordCS.

In a module add the code: (this is for Comma's)
Public Function GetCSWord(ByVal s, Indx As Integer, Optional strdelimiter = ",")
'Returns the nth word in a specific field
On Error Resume Next
GetCSWord = Split(s, strdelimiter)(I ndx - 1)
End Function


Then this is the query
field2: GetCSWord([Body],1)

Field2 is The Coulmn Heading and GetCSWord([Body],1) Is the first word in the body of the email.

Example
So if the date (month, Day, Year) are the 3,4,5 words of the Body: Use

Date: GetCSWord([Body],3) & "/" & GetCSWord([Body],4) & "/" & GetCSWord([Body],5)


-- Boxcar