Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    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:

    Shipping Label
    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    Joe Blow
    123 Nowhere Lane
    Some Place, US 12345

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
    PACKING SLIP:
    Amazon Marketplace Item: Deux mondes: A Communicative Approach (Student
    Edition) + Listening...
    Listing ID: 0000W000000
    SKU:
    Quantity: 1


    Purchased on: 05-Nov-2004
    Shipped by: Someone@else.com
    Shipping address:

    Ship to: Joe Blow
    Address Line 1: 123 Nowhere Lane
    Address Line 2: If Needed
    City: Some Place
    State/Province/Region: US
    Zip/Postal Code: 12345-0000
    Country: United States

    Buyer Name: Joe Blow

    - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

    Here are the details of your completed Amazon Marketplace sale:

    Order #: 000-0000000-0000000
    Listings: 1
    Total Item Count: 1
    Listing 1: Deux mondes: A Communicative Approach (Student Edition) +
    Listening...


    http://s1.amazon.com/exec/varzea/ts/...000000Y0000000
    Listing ID: 0000A000000
    SKU:
    Quantity: 1
    Buyer's Price: $00.00



    Buyer e-mail: joe@nowhere.com
    Time of sale: 05-Nov-2004 16:27:57
    Shipping speed: expedited



    Amazon commission: ($0.00)
    Additional shipping credit: $0.00
    ---------
    Your earnings (in your Payments account): $00.00


    THANKS A MILLION!!!

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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:
    Components needed:
    (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

    Sounds like an interesting project.

    .
    ~

    Bill

  3. #3
    Join Date
    Nov 2004
    Posts
    7
    Thanks for the info. Should give me a good start. Although being a begginer may prove this task a little difficult...Thanks again

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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
    Print
    Write

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •