Search code examples
excelvbaemailoutlook

Extract specific data in Outlook emails contained in brackets and export to specific rows in an Excel s/sheet

I need a Macro in Excel to scan all Outlook emails in a specific folder of my mailbox, which extracts any data from the Subject field, email body and auto signature that is contained in brackets and send it to individual rows in Excel. However I would like to use markers R1, R2, R3, R4, R5, R6 so that the data in brackets marked R1 is spat out into Row 1 of my Excel s/sheet, data in brackets marked R2 is spat out into Row 2 etc.

Markers R1-R6 are part of the original email

Example

I am completely inexperienced with this and have been trying to combine different bits of VBA code for my Excel Macro to make all this work but am not getting very far. There is no where I can find that specifically answers this question, try as I may. I would be very grateful for your help :)

Solution

  • This can help you for the text handling part.

    Under the premise that the order of the email parts is fixed ie R5 is always first and R3 second and so on.

    You will use two important functions:

    1. FIND - helps you to find any character within a text string when the search can be started from the middle of the string.
    2. MID - helps you extract text from the middle of a string.

    By using the FIND function we will find the first ( and the ) after it. If I put the text in A1 the formula will look like this:

    • Start of R5: =FIND("(",A1,1) The result is 26.
    • End of R5: =FIND(")",A1,FIND("(",A1,1)) the result is 56.

    Now R5 can be fully extracted with the MID function:

    =MID(A1,FIND("(",A1,1)+1,FIND(")",A1,FIND("(",A1,1))-FIND("(",A1,1)-1)

    the result is: R5 James Bond Film Production

    And so you move forward, each time looking for the next ( when you start the search where the previous ) ended.

    Good luck