2

I am new to the vba and trying to solve my situation wherein we recieve multiple mail like below:

we would like to create a database in excel for all the mails which are in my specific folder


Package Summary:

Client: XYZ

Price (USD): 3,000

Time: 1 Week

Project Id: 21312


and some more text......

here we would like to capture the information for Client, Price (USD), Time, Project Id.

Have tried below code which capture the information and stores in excel file.

Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
'Dim i As Integer

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Dummy").Folders("New Dummy")

'i = 1

For Each OutlookMail In Folder.Items

    Dim sText As String

    sText = OutlookMail.Body
    Dim Reg1 As RegExp
    Dim M1 As MatchCollection
    Dim M As Match
    Dim vText, vText2, vText3, vText4 As Variant
    Dim i As Integer

    Set Reg1 = New RegExp

   ' \s* = invisible spaces
   ' \d* = match digits
   ' \w* = match alphanumeric
       For i = 1 To 9

            With Reg1
                Select Case i
                Case 1
                    .Pattern = "(Client[:]([\w-\s]*)\s*)\n"
                    .Global = False                    
                Case 2
                    .Pattern = "(([\d]*\,[\d]*))\s*\n"
                    .Global = False
                Case 3
                    .Pattern = "(Time[:]([\w-\s]*)\s*)\n"
                    .Global = False
                Case 4
                    .Pattern = "(Project Id[:]([\w-\s]*)\s*)\n"
                    .Global = False

                End Select

            End With

              If Reg1.Test(sText) Then
                Set M1 = Reg1.Execute(sText)
                 Select Case i
                          Case 1
                              For Each M In M1
                                  vText = Trim(M.SubMatches(1))
                              Next
                          Case 2
                              For Each M In M1
                                  vText2 = Trim(M.SubMatches(1))
                              Next
                          Case 3
                              For Each M In M1
                                  vText3 = Trim(M.SubMatches(1))
                              Next
                          Case 4
                              For Each M In M1
                                  vText4 = Trim(M.SubMatches(1))
                              Next

                End Select

              End If
        Next i

    Range("a1000").End(xlUp).Offset(1, 0).Value = vText
    Range("b1000").End(xlUp).Offset(1, 0).Value = vText2
    Range("c1000").End(xlUp).Offset(1, 0).Value = vText3
    Range("d1000").End(xlUp).Offset(1, 0).Value = vText4
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

Challenges:

Challenge 1: if the heading Price (USD) changes to Price (GBP) still its storing the value, which should not be. it should only store the value only if the matching text found.

i tried "(Price (USD) [:] ([\d]\,[\d]))\s*\n" however its not working.

Challenge 2: for Project id, value is coming with underscore as well which i am unable to exclude.

Would really appreciate if one can help me solving the above 2 challenge from my code.

or else suggest any better approach for the same.

2
  • See regex101.com/r/yu2uW6/1 Commented Jun 5, 2019 at 11:34
  • Thats really cool, is it possible for you to let me know the required edits on case 2 in my code Commented Jun 5, 2019 at 11:40

1 Answer 1

1

You may use

Client:\s*(.*)[\r\n][\s\S]*?^Price \(USD\):\s*(.*)[\r\n][\s\S]*?^Time:\s*(.*)[\r\n][\s\S]*?^Project Id:\s*(\w+)

Make sure you set Reg1.Multiline = True.

See the regex demo

The Client details will be in M.SubMatches(0) (Group 1), price info will be in M.SubMatches(1) (Group 2), time details in M.SubMatches(2) (Group 3), and the project ID will be in M.SubMatches(3) (Group 4).

If you need to remove underscores from Group 4, the project ID, just use a post-processing step:

vText4 = Replace(M.SubMatches(3), "_", "")
Sign up to request clarification or add additional context in comments.

8 Comments

Thanks a lot Wiktor, You are a savior!
Hey Wiktor, sorry i have not mentioned the full case here. so lets say that incase we have recived any inbetween text than the code is not working. see regex101.com/r/yu2uW6/3
however i just wanted to capture the previous defined value only
Hey wiktor, i am really inspired with your quick help. can you also help me/ share the link from which we can learn the same.
@ayushvarshney I learnt it on SO. I can suggest doing all lessons at regexone.com, reading through regular-expressions.info, regex SO tag description (with many other links to great online resources), and the community SO post called What does the regex mean. Also, rexegg.com is worth having a look at.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.