+ Reply to Thread
Results 1 to 5 of 5

Outlook to excel VBA stops searching body after first match

  1. 10-18-2014, 07:06 AM #1
    cgcustomcomputers
    cgcustomcomputers is offline
    Registered User
    Join Date
    10-18-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    5

    Question Outlook to excel VBA stops searching body after first match

    I wrote a code to pull data from outlook to excel, And its 80% working It does pull info but not from the whole email.

    I receive emails in the same format with pricing and other info on them. These are for purchase orders what have more than 1 line usually. They are in this format:

    Item Number : 00001

    Vendor Sales Order Number :

    Vendor Material Number :

    SAP Material Number :

    Vendor Description :

    SAP Description :

    Vendor Quantity : 30.000 EA

    SAP Quantity : 30.000 EA

    Quantity UOM : EA

    Vendor Delivery Date : 20.09.2014

    SAP Delivery Date : 20.09.2014

    Action Request :

    Following details does not match for PO line item 00001

    Vendor Price : USD 0.00 for 1 EA

    SAP Price : USD 0.01 for 1 EA

    Item Number : 00002

    Vendor Sales Order Number :

    Vendor Material Number :

    SAP Material Number :

    Vendor Description :

    SAP Description :

    Vendor Quantity : 70.000 EA

    SAP Quantity : 70.000 EA

    Quantity UOM : EA

    Vendor Price : USD 3.90 for 1 EA

    SAP Price : USD 3.90 for 1 EA

    Vendor Delivery Date : 20.09.2014

    SAP Delivery Date : 20.09.2014

    Action Request :

    Quantity and Requested Date all matched with PO. Item 00002

    As you can see from the code i am pulling multiple things from these emails that have the same beginning string. After it pulls line 1, the code moves to the next email without searching the entire body of the email for further matches. How can i fix this? Stuck 

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 10-20-2014, 02:42 PM #2
    Tinbendr
    Tinbendr is offline
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Outlook to excel VBA stops searching body after first match

    Use debug to display the ubound in the immediate area. Insert this right asfter the vText assignment.
    Please Login or Register  to view this content.
    This number should equal, at least, the number of text lines.

    I would reduce the amount of white space in your If-Thens, too.
    Please Login or Register  to view this content.
    If these keywords also appear in the body, then look for the left most one.
    Please Login or Register  to view this content.
    David
    (*) Reputation points appreciated.
    Reply With QuoteRegister To Reply
  3. 10-22-2014, 04:26 PM #3
    cgcustomcomputers
    cgcustomcomputers is offline
    Registered User
    Join Date
    10-18-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    5

    Re: Outlook to excel VBA stops searching body after first match

    Still is not pulling the other matches further down. And those spaces are there because that is exactly how every template is laid out. Example of a multiple line email template here :

    Item Number : 00001
    Vendor Sales Order Number :
    Vendor Material Number :
    SAP Material Number : 000000000000000
    Vendor Description :
    SAP Description : Item Description here
    Vendor Quantity : 30.000 EA
    SAP Quantity : 30.000 EA
    Quantity UOM : EA
    Vendor Delivery Date : 20.09.2014
    SAP Delivery Date : 20.09.2014
    Action Request :
    Following details does not match for PO line item 00001
    Vendor Price : USD 0.00 for 1 EA
    SAP Price : USD 0.01 for 1 EA


    Item Number : 00002
    Vendor Sales Order Number :
    Vendor Material Number :
    SAP Material Number : 00000000000
    Vendor Description :
    SAP Description : Item descrip here
    Vendor Quantity : 1.00 EA
    SAP Quantity : 1.00 EA
    Quantity UOM : EA
    Vendor Price : USD 1.00 for 1 EA
    SAP Price : USD 1.00 for 1 EA
    Vendor Delivery Date : 20.09.2014
    SAP Delivery Date : 20.09.2014
    Action Request :
    Quantity and Requested Date all matched with PO. Item 00002



    Maybe that can help a bit? really confused with this on why i can not get it to pull ALL the matching info after the standard text for each line item.
    Reply With QuoteRegister To Reply
  4. 10-22-2014, 04:28 PM #4
    cgcustomcomputers
    cgcustomcomputers is offline
    Registered User
    Join Date
    10-18-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    5

    Re: Outlook to excel VBA stops searching body after first match

    We are really looking for the info after "Following details does not match for PO line item XXXXX." If the info matches i dont need it to pull the info necessarily. But it doesnt hurt. Was trying to do this the easiest way i could being im a noob VBA coder.
    Reply With QuoteRegister To Reply
  5. 10-23-2014, 08:41 AM #5
    Tinbendr
    Tinbendr is offline
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Outlook to excel VBA stops searching body after first match

    1. You have to increment rCount
    Please Login or Register  to view this content.
    2. Split removes the delimiter, so it's stripping the colon from the string. It's also stripping all but two of the spaces. (Another reason to use a smaller test phrase and Debug.Print.)
    We are really looking for the info after "Following ...
    Then you'll need to add an extra If-Then, testing for that phrase first (using InStr), then test using the code you've written so far, then test for a blank/empty record signifying the end of record.

    Yes, it'll be a lot of testing, but break it down. If you can't get it, post back.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy URL from Excel to Outlook body
    By Southernw2002 in forum Outlook Formatting & Functions
    Replies: 3
    Last Post: 05-17-2014, 06:26 PM
  2. [SOLVED] Outlook Email with Body from Excel
    By dreicer_Jarr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2013, 03:57 AM
  3. Body of outlook message to Excel
    By dougmorgan in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 05-19-2013, 09:11 AM
  4. Outlook Email Body to Excel
    By Sciortmj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-14-2012, 02:10 PM
  5. import MS Outlook body into Excel
    By wfdude in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2011, 10:14 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1