Re: Parsing data
- From: "Geoff" <geoff@xxxxxxxxxx>
- Date: Mon, 11 Dec 2006 01:08:41 -0000
Hi Eric,
I am using Outlook but do not understand how I would approach this. Do I
write VBA to parse the data and insert it directly to an Access table
through
an ADODB connection or is there some other method?
Not through an ADO connection, no. You would write the VBA code either in
Access or Outlook, using Automation, which allows VBA to control another
application.
If you were writing the VBA code in Access, your code would start Outlook,
get the data from Outlook and insert the data into the appropriate Access
table. (In this scenario, Access is known as the Automation Client and
Outlook is the Automation Server, ie Outlook is serving up the data to
Access. You would need to create a reference to the Outlook object model -
Tools, References in VBA.)
Alternatively, if you were writing the VBA code in Outlook, your code would
start Access, open the appropriate Access table and write new records to the
appropriate table. (In this second scenario, Outlook is the Automation
Client and Access is the Automation Server. You would need to create a
reference to the Access object model.)
If this is a VBA solution would it be easier to do the VBA in Access
(having
imported the file to a table from Outlook) and then delete this original
table having inserted the parsed data to a new table?
Deciding which is the easier place to write your code depends on several
things. It might be easier to write the code in Access and use Outlook as
the Automation Server. There are some issues when using Access as an
Automation Server. (The experts have written class modules to simplify
using Access as a Server.)
The other factor to consider is where the emails are in Outlook and what you
are prepared to do with them after their data has been captured into Access.
What is acceptable to you so that an email's data is only transferred to
Access once? The easiest solution might be to put all the emails awaiting
to be processed in one Outlook folder and move them (automatically using VBA
during processing) to a different Outlook folder after their data has been
transferred.
I was hoping that there was a method using a query where I could select
data
from a Memo field - do you know if this is possible?
I see why you posted to this newsgroup.
This might be theoretically possible, but I don't think it would be the
right approach. It would add an unnecessary degree of complexity and the
problem is complicated enough.
In outline, to extract the relevant data from all the emails in one Outlook
folder, your VBA code in Access would need to do the following:
1. Start Outlook.
2. Point an Outlook MAPIFolder object variable to the Outlook folder that
contains the emails.
3. Loop through all the emails in the folder. (Emails are known as
MailItems in Outlook's object model.)
4. In the loop, point an Outlook MailItem object variable to each email
in turn.
5. Capture the MailItem's Body property into a string variable. The Body
property returns the plain text of the email message.
6. Examine the string variable for the lines containing the data you want
to capture.
7. Use ADO or DAO to add records to the appropriate Access table.
As you see, there are a number of steps to be performed. None of it is too
difficult, but it can take a while to get it all in place. Obviously, you
need to decide whether it's worth it given the number of emails you have to
process.
The Sue Mosher book would have all the code you need. I expect there's tons
of stuff on the net too.
Geoff.
"Eric" <Eric@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9543CCAD-C115-464C-95FE-93FC894F527B@xxxxxxxxxxxxxxxx
Hi Geoff,
I am using Outlook but do not understand how I would approach this. Do I
write VBA to parse the data and insert it directly to an Access table
through
an ADODB connection or is there some other method?
If this is a VBA solution would it be easier to do the VBA in Access
(having
imported the file to a table from Outlook) and then delete this original
table having inserted the parsed data to a new table?
I was hoping that there was a method using a query where I could select
data
from a Memo field - do you know if this is possible?
"Geoff" wrote:
Eric,
You could achieve your objective if Microsoft Outlook were your email
client. Outlook (not Outlook Express) supports Automation.
Programming Outlook can seem odd because Outlook's object model is
unique.
One good resource to get you started would be "Microsoft Outlook
Programming, Jumpstart for Administrators, Developers and Power Users" by
Sue Mosher.
Geoff
"Eric" <Eric@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:37E4FD96-E89A-48D9-A953-71613A51AEC0@xxxxxxxxxxxxxxxx
I receive regular e-mails of the same basic format with which I would
like
to
create an access DB,
The body of the mail will contain something like:
Name: Someone's name (unknown length)
Email: someones@email (unknown length)
etc, etc.
When i import the data into Access 2003 the e-mail body forms a single
field, and I would like to get the data out into a new table where the
fields
are:
Name
etc, etc.
Any ideas?
All suggestions appreciated.
.
- Follow-Ups:
- Re: Parsing data
- From: Eric
- Re: Parsing data
- References:
- Re: Parsing data
- From: Geoff
- Re: Parsing data
- From: Eric
- Re: Parsing data
- Prev by Date: Re: Count Query doubles results
- Next by Date: Re: To Query or to VBA and threading?
- Previous by thread: Re: Parsing data
- Next by thread: Re: Parsing data
- Index(es):
Relevant Pages
|
|
