Unanswered: Extracting data from IE web page or MS Outlook email
I recieve emails from Amazon.com. They include shipping information, price of item, order number and so on. The emails are always formated the same. Is there a way to pull specific data, like the info I listed above, from either Internet Explorer (WebMail) or MS Outlook and put it into an Access DB? Here is a portion of one of the emails I get. The underlined info is what I need to extract...Any ideas would be helpful:
I think your best bet would be to do the email processing in MS Outlook with a macro. You can use DAO or ADO for direct access to your MS Access Database Table, which is support in MS Office VB.
Process flow would go like this:
(A). Create an Array of Data Eliments to Extract from messages (you could put this in a table for ease of maintenance) It would include field Size and data id info. Example
Name | Identifier | Length
"Listing ID", "Listing ID:", 11
(B). Function to check if Rcpt has already been entered.
(C). Function to write data to the access table
Process Flow with Outlook VB
1. Check active message, activate process with button press in outlook
2. check if message format is a Amazon Rcpt. Show msg and Exit if not.
3. Access record set of Data Eliments
4. Check Rcpt Number to see if it is already in the DB
5. If in DB, Show message and Exit, Else continue
6. Loop through message body line by line
7. If Trim(line) is not "", compare to data Eliments recordset
8. If Like "Identifier*" , strData = Right(Trim(line), nLength)
9. Call write to table Function: Call fAddtoRcptTable(strName, strData)
10. Next Line
I am assuming the receipt is in HTML. If so you can right click in the email and view the source code. Then save the source code to a file. Put the file into a folder for incoming receipts and write your code in Access. You will need to learn how to parse through the file using VBA commands. You'll want to learn about:
FreeFile - Get the next file ID available
Open - Open a file (also Append, Input, Output, etc.)
EOF - End of File indicator
Close - Close a file
Kill - Delete a file
Name - Rename/Move a file
I hope that you are not a true beginner, because this might be a little difficult to do. And on top of that, if Amazon changes the formatting of their receipts than you might be tweaking your code again.