Extracting Specific Text from Email!!

[hamudi]

Hi Guys,

I need your help as i'm at a lost now.
I have set up Excel to retrieve Emails from a specific folder in Outlook.
Only the email body content will be copied to a column in Excel.

The thing is that in the body content i only need a specific text to be copied to a cell.
For example:
"This product has been release - X2345DL . The Item blablabla"


What i need is only the X2345DL in the cell and nothing else.
X2345DL has not specific format, it could be D2345YY and so on.

Any idea how do i do this?

I have an idea of using a column to retrieve the email body content and
copy/filter out the text i need to another column and deleting the column with the email body content when finish.

Appreciate any help! Thanks!!!

[Zack Barresse]

Hello, and welcome to the board!

Post your code you have so far. We can utilize the Restrict method to refine your search for only those emails which meet the criteria. Can you confirm the criteria in the body of the email will always either 1) precede the content you're looking for with "This product has been release - ", or 2) follow the content you're looking for with ". The Item ", or 3) both 1 and 2?

[hamudi]

this is the code i use to get the emails from outlook to excel.

[vba]
Sub GetFromInbox()
Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Variant
Dim i As Integer
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
'Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
'Pointing To Get The Email from a Specific Folder in Outlook
Set Fldr = olNs.GetDefaultFolder(olFolderInbox).Folders("ATAN")

i = 1
For Each olMail In Fldr.Items
ActiveSheet.Cells(i, 25).Value = olMail.Body
i = i + 1
Next olMail
Set Fldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
End Sub
[/vba]

Yup the email will always contain a specific sentence.
Only the Product Detail will be a variable.

[Zack Barresse]

Yup the email will always contain a specific sentence.
Only the Product Detail will be a variable.

What is that sentence exactly?

[hamudi]

it would be:

The Product for GH111TH – UU788LK has been released.
Please access document to view the detail."

The text that i want from all the emails is from "UU788LK".
But GH111TH also is a variable. Is it possible?

[Zack Barresse]

Would you like them as separate variables, or all together as one? And you want this to go into column Y? Do you want this column cleared before this is ran, then input the data? Or add it to the bottom of the existing data in that column? If replacing all data, do you have a header/title in Y1?

Also, we can't use the Restrict method for this, I forgot you can't use it on the Body. But there are better ways still.

[hamudi]

Together would be better but if either one works it doesn't matter.
Yup i have already set 'Y' column to be cleared after another process is called. Y1 and Y2 have headers now, so it will start from Y3 onwards.

[Zack Barresse]

I had to adjust to test in my environment, then put back the way you had it, but see if this works for you. If your folder is correct, it worked for me.

[vba]Sub GetFromInbox()

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olFolder As Outlook.Folder
Dim olMail As Outlook.MailItem
Dim iRow As Long
Dim sFilterStart As String
Dim sFilterEnd As String
Dim sExtract As String
Dim aExtract() As String
Dim aExtractItems() As String

' Set olApp = New Outlook.Application
' Set olNs = olApp.GetNamespace("MAPI")

Set olApp = GetOL()
Set olNS = GetNS(olApp)

Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
'Pointing To Get The Email from a Specific Folder in Outlook
' Set olFolder = olNS.GetDefaultFolder(olFolderInbox).Folders("ATAN")

'Example text to look for: The Product for GH111TH – UU788LK has been released.
sFilterStart = "The Product for "
sFilterEnd = " has been released."
iRow = 3

For Each olMail In olFolder.Items
If InStr(1, olMail.Body, sFilterStart, vbTextCompare) > 0 Then
aExtract = Split(olMail.Body, sFilterStart)
aExtractItems = Split(aExtract(1), " ")
sExtract = aExtractItems(0) & " - " & aExtractItems(2)
If InStr(1, olMail.Body, sFilterStart & sExtract & sFilterEnd, vbTextCompare) <> 0 Then
ActiveSheet.Cells(iRow, 25).Value = sExtract
iRow = iRow + 1
End If
End If
Next olMail

End Sub

Function GetOL() As Outlook.Application
On Error Resume Next
Set GetOL = GetObject(, "Outlook.Application")
If GetOL Is Nothing Then
Set GetOL = CreateObject("Outlook.Application")
End If
On Error GoTo 0
End Function

Function GetNS(olAppTemp As Outlook.Application) As Outlook.Namespace
Set GetNS = olAppTemp.GetNamespace("MAPI")
End Function[/vba]

As you'll notice I also added a couple extra functions to set the Outlook and Namespace objects with. I tend to prefer this method now, especially if you make routines which access Outlook at all. Writing the same code over and over can be repetitive. With this, set them both in a single line each now.

HTH

[hamudi]

hi zack thanks for the help.
However when i run your code, it didn't even retrieve any email to the excel column.

seems something messed up the
"ActiveSheet.Cells(iRow, 25).Value = sExtract"

[mancubus]

maybe because of sub folder...

here is a piece of code that i use to reach subfolders...
[VBA]

Set olFolder = olNs.Folders("Mailbox - My Name"). _
Folders("Inbox").Folders("My Sub Folder")
[/VBA]

[hamudi]

maybe because of sub folder...

here is a piece of code that i use to reach subfolders...
[vba]

Set olFolder = olNs.Folders("Mailbox - My Name"). _
Folders("Inbox").Folders("My Sub Folder")
[/vba]

I've tried pointing it to inbox still nothing.
My guess there's some adjustment i need to do with this

[VBA]For Each olMail In olFolder.Items
If InStr(1, olMail.Body, sFilterStart, vbTextCompare) > 0 Then
aExtract = Split(olMail.Body, sFilterStart)
aExtractItems = Split(aExtract(1), " ")
sExtract = aExtractItems(0) & " - " & aExtractItems(2)
If InStr(1, olMail.Body, sFilterStart & sExtract & sFilterEnd, vbTextCompare) <> 0 Then
ActiveSheet.Cells(iRow, 25).Value = sExtract
iRow = iRow + 1
End If
End If
Next olMail
[/VBA]

[hamudi]

[VBA]
For Each olMail In Fldr.Items
aExtract() = Split(olMail.Body, sFilterEnd, 2)
ActiveSheet.Cells(iRow, 1).Value = aExtract
iRow = iRow + 1
Next olMail
[/VBA]

So i ran this code and manage to get the 1st part of the content to stay as "The Product for GH111TH – UU788LK", i still need help just to get it to only "UU788LK"