Select patterns from mail body - VBA

Question

I receive multiple mails, where I have to do a pattern search and get the results to an array. is there any easy way of doing it in excel vba?

My mail content looks like this :

Selection : Your Selection

Description : My description

Items :

ITEM.12345.XYZ

ITEM.56789.KHI

ITEM.54567.YHT

This is a sample mail.

............................

I need to grep for all the lines starting with ITEM. I can do it, by this way :

aryLines = Split(MyMail.Body, vbCrLf)

Loop through aryLines

Find the match

I am looking for an easy solution, as the number of matching lines varies and sometimes it can go over 1000 and sometimes less than 10. I want to populate these values to an xls sheet.

score 0 · Answer 1 · edited May 23 '17 at 11:56

I could be wrong though but IMHO splitting the body into the array as you have done is the fastest way. I then loop through the array and in conjunction with INSTR() or LEFT() get the relevant lines. In your case I would you LEFT(). In fact I use it all the time.

For example

aryLines = Split(MyMail.Body, vbCrLf)

For i = LBound(aryLines) To UBound(aryLines)
    If InStr(1, aryLines(i), "ITEM", vbTextCompare) Then

    End If
Next i

The other option is to export the data immediately into Excel after splitting it into the array and then using the .AutoFilter

aryLines = Split(aaa, vbCrLf)

oXLWorksheet.Range("A1").Resize(UBound(aryLines), 1).Value = _
oXLAP.WorksheetFunction.Transpose(aryLines)

Once the data is exported, you can use the Autofilter to delete the rows which you don't want. An example can be seen HERE. This example copies the filtered range but you can edit it to delete the range

The criteria for Autofilter will be Criteria1:="<>ITEM*". This means filter cells which do not begin with ITEM

Select patterns from mail body - VBA

............................

1 Answers1