473,322 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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_thing: ffffffffffffffffffffffff
changes_communicated: Strongly Agree
changes_communicated_memo: fffffffffff
tech_skill_needed: Strongly Agree
have_skill_knowledge_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.
Jun 4 '07 #1
9 12120
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.html
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
Jun 6 '07 #2
cyberdwarf
218 Expert 100+
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
Jun 6 '07 #3
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
Jun 6 '07 #4
cyberdwarf
218 Expert 100+
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
Jun 6 '07 #5
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.Application.

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
Jun 6 '07 #6
cyberdwarf
218 Expert 100+
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.Application.

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
Jun 6 '07 #7
Try this software:
http://www.geniusconnect.com/

(free version limited to 49 records)
Jun 8 '07 #8
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
Jun 8 '07 #9
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)(Indx - 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
Jun 12 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: PitchBlackSheep | last post by:
I have an office of Real Estate agents who use our (single) Access 2000 database to keep track of clients. Our agents email our clients everyday using Outlook 2000. I'm trying to find a way to...
0
by: VP | last post by:
g'day, i am posting a problem i have encountered with creating an email using outlook through some basic c# code. the problem arises when using different email editors in outlook. At the moment...
8
by: Wayne | last post by:
My supervisor performs the following steps to generate an email notifying recipients of new problem reports: 1) Generate an Access report containing the columnar data and export it to a .rtf...
1
by: Mr T | last post by:
I know how to send email from Access and I know how to create a custom form in Outlook. but.... How do I put the email info from Access into the Outlook custom form ??? Dim MyDB As Database Dim...
3
by: Jeff | last post by:
I am trying to send emails from my application (not using a plugin, that will come later), I can create an email and add attachments. I can fill in the body etc. Word works for the editor. So...
16
by: Kosmos | last post by:
Good afternoon everyone, just wondering if anyone knew if it's possible to send meetings or appointments through email when you run VBA or SQL code in Access 2003? The following is the code I've been...
5
by: Kosmos | last post by:
I have traveled the world and the seven seas and I have yet to come up with an answer to this question.... So I'm adding an attachment to an email from access... The following is the code: ...
6
by: LeoK | last post by:
Hey I am so close from finishing this database I just need one more issue solve thanks. Basically I have a code within a form that opens up outlook and fill in the body of the email, among other...
6
by: dlblack | last post by:
I am using the following code to send emails with attachments from Access using Outlook. The code works fine if Outlook is already open and emails are sent as expected via Outlook. When Outlook is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.