Email Data

A

AliyuKatsina

Registered User.
Local time
Today, 23:58
Joined
Dec 31, 2009
Messages
73
Hi all,

I have a Database that track repayment for housing loans. Each customer usually pays directly to my bank, and the bank sends me an email with customer name and amount paid.
Is there a way to update my table using this email?
 
AliyuKatsina said:
Hi all,

I have a Database that track repayment for housing loans. Each customer usually pays directly to my bank, and the bank sends me an email with customer name and amount paid.
Is there a way to update my table using this email?
Click to expand...

I am not an expert in document parsing, but I'm fairly sure that Emails and other document types can be opened and read via Access VBA.

How well can you rely on the source of your Email? If the Email always has the same structure, such as an automated Email, then a parsing Function could "Read the Email" and extract what you are looking for. In that case, what you want should not only be able to be done, but may have even been done before. If not, then it becomes more difficult to determine the answer to your question.
 
Hi Rookie,

The emails are I receive are automated replies, so they follow a particular patten. If I can extact it as a string, I can use it to update my records.
 
I suspect the bank will be sending you an automatically generated e-mail.

In which case it probably will have a standard format so parsing will be possible although probably fiddly. Although first time you receive a payment you might need to check you have the name of the client the same in the database as the bank uses.

You're going to have to parse out the customer name and the amount paid / date then run some kind of append query to bring the parsed information into some kind of sales receipt table based on the name. Therefore you're going to have a standard format for names and have that as the link. IF you can parse the information might not be too bad. Automatic imports into a table are generally quite easy I would probably do the parsing through queries once imported into access.

There are lots of functions that can be used to parse out a text field

Left
Right
Mid

etc...
 
Last edited:
Thanks LightWave.
My issue is how to parse the information. Can you help?
 
Can you get the information from the e-mail into a memo field in a table ok?
Which version of Access are you using?
 
I am using 2007 version. How can I get the email content to a memo field?
 
re Actual Parsing
Thinking about it might be easier to separate the name out prior to import but if you don't here's something which might work

example...2 records with e-mail bodies...

Mr John Brown paid £300.00 on 16/07/2010
Ms Sue Townsend paid £1000.00 on 5/07/2010

In this statement the tricky part is that the name and amounts and date are different lengths although the format is exactly the same.

So to get the name out you would need to have some code that looks for
Ms
Mr
Mrs
Dr
and counts to 3 usually or 4 if Mrs

Looks for the text "_paid" and counts where it starts
Subtracts 3 or 4 from the number counted in the "_paid" line. This gives the length of the name

Then uses the mid function starting at 3 for Mr Ms or Dr or 4 for Mrs and uses the length calculated above

the Mid Function should then deliver John Brown or whatever name consistently irrespective of their length.

Will need to do similar for amount and date... As they may not start in the same location in the body of the text.
 
Last edited:
Don't forget instr() and instrrev() as search tools to help locate the field.

To parse a text document, search tags are the best option. A Search tag is a set of unique characters that represents the start (and end?) of the data to be searched for, similar to html or xml tags.

In the example lightwave provided, searching for the word paid, and then selecting the "word" that follows, will get you the amount. Subsequently searching for the word on, and then selecting the "word" that follows will get you the date.

Of course, the effectiveness of the process depends on the fact that each line has a defined structure, and that there are no variations.
 
You must log in or register to reply here.

Similar threads

E
Output data from tables into a directory or phonebook
Replies
13
Views
526
MarkK
MarkK
A
Search button for more than one field search
Replies
3
Views
221
Ken Sheridan
Ken Sheridan
A
Automation Error
Replies
10
Views
703
The_Doc_Man
The_Doc_Man
P
Building a MAUI App...
Replies
1
Views
681
Isaac
Isaac
E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
628
Trevor G
Trevor G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom