+ Reply to Thread
Results 1 to 22 of 22

How to parse data in Outlook email using HTML version of email back to excel

  1. #1
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    How to parse data in Outlook email using HTML version of email back to excel

    Basically I have a VBA macros that pulls parsed data from emails in a specified outlook folder. It pulls a Price ($x.xx) and a name which are then both used in a vlookup in an accounting spreadsheet.

    The problem Im having is that when I parse the name, I have to pull everything between "Shipping Address:" and "United States". That returns a string that looks like this:

    John Doe380 W 23RD STSAN BERNARDINO, CA 92405-3718

    if I select the cell and click the "wrap text" button it shows up like this:

    John Doe
    380 W 23RD ST
    SAN BERNARDINO, CA 92405-3718

    SO, HOW DO I only parse that first line that has the name and isolate it. Or even run a quick macro to trim just down to the first line?

    Thanks.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: How to parse data in Outlook email using HTML version of email back to excel

    It sounds like the lines in the address are separated by a line feed/carriage return.

    Can you post the code you are using to pull the address?

  3. #3
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Quote Originally Posted by Norie View Post
    It sounds like the lines in the address are separated by a line feed/carriage return.

    Can you post the code you are using to pull the address?
    Absolutely:

    Please Login or Register  to view this content.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Is ShippingName the variable that will have the address?

    If it is try this.
    Please Login or Register  to view this content.
    This code assumes that the lines of the address are split by Chr(10), the new line character.

    If the code doesn't work you can try other characters.

    Actually there might be a way to find out the character.

    If we take your example of John Doe.

    Please Login or Register  to view this content.
    This should return the first character after the name.

  5. #5
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    So i added that first bit of code here:

    Please Login or Register  to view this content.
    And changed the .Cells line to reflect that...didnt work. What am I doing wrong...where does it get added?

  6. #6
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    I added

    Dim strName As String

    to the top of the macros, ran it and it worked but it returned blank cells in the B column...I think theres a blank line before the first name.
    Last edited by bnasty; 10-30-2012 at 11:19 PM.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: How to parse data in Outlook email using HTML version of email back to excel

    I'm assuming that ShippingName has the address and if that's correct then you've put the code I posted in the right place.

    Another assumption I was making was that the lines were separated by Chr(10), that might be wrong.

    I'm really kind of guessing there though - Chr(10) is what you get when you enter multiple lines in a cell with ALT+Enter.


    Oh, forgot to ask - how didn't it work?

  8. #8
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    I could run a macro to turn the whole column into wrapped text. Is there a way to just trim that first line instead of working with the parse? And yeah, the name, address etc is all in that ShippingName.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Sorry, little bit confused - you say the code is working but it returns blanks?

    That's not really working.

    If there is a blank line before the name I would have thought the earlier Trim would have got rid of it.

    You could try changing (0) to (1) in the code, which will get the 2nd item.

  10. #10
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    BOOOOOOOOOM your awesome!!!!! It works. Just changed that to a (1). I meant like the trim worked but it trimmed the 1st blank line instead of the second with the name. Not sure why the first trim didnt cut it.

    Nother quick question: Now with what I got for strName, how can I just trim that down to the first 20 characters starting with the first letter of the name. That way the this column will match perfectly with the Main accounting page's names and the vlookup can search for exact matches.

  11. #11
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Now I can't get that Vlookup to work if I set it to exact match (False).....

    I literally can see the names are the exact same but it wont pull the price...

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: How to parse data in Outlook email using HTML version of email back to excel

    The chances are that the names look the same but they aren't the same.

    It could just be something simple like a trailing/leading space or, since the data is from HTML, it might be a non-printing character like Char(160).

    Hard to tell without seeing the workbook.

    PS Do you still need help with trimming the name to 20 characters?

  13. #13
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Man this gets more complicated and more complicated. I still need help with the 20 characters but now the bigger problem is this vlookup. Is there any way to cut the column and repaste it printed? I mean the two columns are identical and it won't work. There isnt a leading or trailing space, I've run a

    =substitute(namecell, " ", "") to get rid of all the spaces, ran a vlookup on that and it still isnt working. Ideas?

  14. #14
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Hi -

    Sorry, just passed by.

    What happen if you manually type in the name on the cell? the same string you've got from the HTML value in strName?
    Is the VLookUp works?

    If that would be the case then the strName has something in there, so please upload a workbook with the StrName value

    Regards,
    event

  15. #15
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    TestVlookupWorkbook.xlsx

    There is a test file. The first page is a sample accounting spreadsheet, the vlookup is in column F, the second sheet, "Sheet 1" has all of the names and the corresponding cost info. I hardcoded a name and fake cost amt at the end of the list in red on sheet1 and that worked...

    The macros aren't included...

    Can't figure out WHY this vlookup wont work....
    Last edited by bnasty; 10-31-2012 at 12:19 PM.

  16. #16
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: How to parse data in Outlook email using HTML version of email back to excel

    The names are different.

    When I try this formula on Sheet1 I get FALSE.

    =B455='Ebay File Exchange 10.1 - 10.27'!H3

    When I add CLEAN I get TRUE.

    =CLEAN(B455)='Ebay File Exchange 10.1 - 10.27'!H3

    This means that the value in B455 Sheet1 (Scott Kratovil) doesn't match the value in E3 'Ebay File Exchange 10.1 - 10.27'.

    The use of clean indicates that the value in B455 has a non-printing character.

    I've just done a little test and found that the value on B455 has Chr(13) at the end.

    This might be my fault.

    The address from the email might actually be separated by vbCrLf which is actually 2 characters - Chr(10) and Chr(13).

    Anyway, try this - in the Split code change Chr(10) to vbCrLf.

  17. #17
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Quote Originally Posted by Norie View Post
    The names are different.

    Anyway, try this - in the Split code change Chr(10) to vbCrLf.
    Yeah that worked...

    The lookup only returns about 75% of the results. This is because of 2 reasons,

    1) Not all of orders produce an email in this format. I'll have to write another macros that can parse those emails.

    and

    2) The Buyer name, which is on the main sheet, doesn't always match the shipping name which is found on the parsed results. So I think a better way to go is to use the first line of the address instead. But since first address lines can be very long, I need to trim it to say 20 characters and run the lookup on that. So how would I make that trim on both lists?

  18. #18
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596
    I don't think an address would be a good candidate for a lookup.

    An address can have more variations than a name, eg St, Street, Av, Ave, Avenue etc.
    If posting code please use code tags, see here.

  19. #19
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    True. But the addresses that are being parsed from the emails are originally input from an API that uses the Main sheet's source information. So the addresses will always match. The difference is they each have different character limits. So I'm thinking if I use the lower character limit (20) and change both lists to reflect that, then it should find the exact match.

    In other words, the buyer name vs shipping name won't always match, but the 1st address lines will always match because they are pullling from the same source.

    Another thing I have to do is on the Main sheet, I will have multiple lines from the same buyer if they purchase multiple different products. So I need to combine all the data into one line that has added the price info from each line together. Again, I think I should use the 1st address line as a reference. However since the product names will be different for each line, is there a way to change that field to "combined" for those rows that match based on.

    I think I'm making this whole thing more complicated that it is but at least Im learning a lot.
    Last edited by bnasty; 10-31-2012 at 02:00 PM.

  20. #20
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,596

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Why do you need to limit the no of characters?

  21. #21
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    Because even though the emails I parse use the same address data that the main sheet gets, the emails have a character limit imposed on them. If I try and do a lookup, it won't find the exact match.

    For example the main sheet will have info like this:

    John Doe 34576 Washington Avenue Apt 123 Miami, Fl 33333. United States

    The parsed email will have:

    John Doe
    34576 Washington Av
    Miami, FL 33333


    So if I can trim the main sheet address to the same number of characters as the vlookup table then it will return exact matches. (after I figure out how to combine the rows of course)
    Last edited by bnasty; 10-31-2012 at 02:12 PM.

  22. #22
    Registered User
    Join Date
    10-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: How to parse data in Outlook email using HTML version of email back to excel

    =LEFT(A1,20) to trim

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1