none
Macro to export Purchase Order email from Outlook to Excel RRS feed

  • Question

  • Hey,

    I'm looking for a macro that will export certain fields from a PO email into Excel. The PO email is formatted a little strangely, so the codes in the other threads don't seem to be working for me.

    This is the information I would need:

    • Subject
    • Ship To address (1)
    • Ship To address (2) if applicable
    • Ship To name
    • Ship To city
    • Ship To State
    • Ship to Zip
    • Item Code (SKU code)
    • Qty
    • Total Price

    Here's what the e-mail looks like

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

    ---------------------------------------
    -                              PO #:CS12685196

          To:  john doe,

        From:  Jewelry Operations

     Subject:  PO# CS12658718       Sent:  8/28/2012 9:35:12 AM
    -----------------------------------------------------------------
    ORDER INFORMATION

    Account/Customer #:
    Purchase Order #:    CS12658718 

    *******************************
    *   MUST SHIP BY: 08/29/2012  *
    *******************************
    Ship To:  Barbara Peri
              4870 Jordan Rd
              P O Box 13
              Amersterdam, PA 17987, United States


    Ship Method: Third-Party
    Send Ground shipments:
    Via: UPS, account # Y36935
              -----------------------------------
              -----------------------------------

    Delivery Type: Residential

    QTY     ITEM CODE               DESCRIPTION                  PRICE
    ------- -----------------       -----------------            -------
    1   ST9328-95               (ST9328-95) Step Edge  $26.00
    *** Ground                  Brush Finish Black Carbon
                                Fiber 9 mm Comfort Fit Mens
                                Tungsten Wedding Band Ring
                                Size: 9.5
    ---------------------------------------------------   ----------
                                                 TOTAL:   $26.00

    Payment Method:  Net Terms (bill to our account)

    Any help would be very appreciated! Thank you

     

    • Edited by oravo Wednesday, August 29, 2012 8:23 PM
    Wednesday, August 29, 2012 8:17 PM

All replies

  • This is very similar to http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/f1ab97d9-8fef-46cc-bbe0-e597370ed1c2

    It is just a matter of establishing which bits of the message to place in which table cells. In order to do that, it would be necessary to have access to an original message (not one that has been copied to the forum nor one which has been forwarded with added information either manually or by the forwarding process).

    For this to work all the messages must be similar in format. If some of the messages have multiple items then how are they arranged and how are they to be handled?

    It is not clear what you mean by Ship to Address (2) in the context of your example. Do you mean a second address line?

    Extracting data from diverse documents is never straightforward. It would make things a lot simpler if you re-organised your e-mailing ordering system to use a field based form along the lines of http://www.gmayor.com/ExtractDataFromForms.htm


    Graham Mayor - Word MVP
    www.gmayor.com


    Thursday, August 30, 2012 6:17 AM
  • You must be sure, that form of this mail is still.

    Then you can assign body to string and split() by line or by word to excels right column by rows.

    Good to wrote code/script and use as rule in Outlook, to automatically separate data when you receive a message from specific customer.

    Take a look at this sample (similar with your problem): Automatyzacja 2 - Wyszukanie danych w wiadomości Outlooka i dopisanie do pliku aplikacji Excel

    Translate it for your self using google or other.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Thursday, August 30, 2012 9:15 AM
    Answerer