Convert these mails to excel

Please provide a clear example of the desired result in excel.

This sentence is confusing

"and the Process ""C:\Program Files\Common Files\system\ado\msadox.dll" exhibiting suspicious behavior pattern 'HIPS/RegMod-001"  in Colum B"

Hi,

I have saved a mail that i get

From:      Sophos@plc.com on behalf of Sophos Anti-Virus
[sav@ACCAPP.Develop.Group.co.uk]
Sent:      Monday, October 08, 2007 9:30 PM
To:      Sharath Reddy
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of
type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE"
failed. The user does not have the rights to perform the action on the
infected file.

I need headers in excel for

Sent                                                                Machine                File          

Monday, October 08, 2007 9:30 PM                ACCAPP             "C:\WINDOWS\system32\DWRCS.EXE"

Hi,

Using the similar technique as previous answer, save the mail to sample.txt, add the references and use the below code.

Sub Main()

Dim sFilename As String, sFileText As String
sFilename = "c:\temp\sample.txt"
sFileText = ReadFile(sFilename)

Dim re As RegExp
Dim matches As MatchCollection
Dim m As Match
Dim n As Integer  'Counter
Dim sht As Worksheet

Set sht = Application.ThisWorkbook.Worksheets("Sheet1")

Set re = New RegExp
re.IgnoreCase = False
re.Global = True
 
re.Pattern = "Sent:\s*([^\r\n]+)?[\r\n]+([\s\S]*)?"
re.Pattern = re.Pattern & "Machine:\s*([^\r\n]+)?[\r\n]+([\s\S]*)?"
re.Pattern = re.Pattern & "(File ""(.+)?"")"
Debug.Print (re.Pattern)

Set matches = re.Execute(sFileText)
'Debug.Print (matches.Count)

n = 2
For Each m In matches
    'Write the Data
    sht.Cells(n, 1).Value = m.SubMatches(0)
    sht.Cells(n, 2).Value = m.SubMatches(2)
    sht.Cells(n, 3).Value = m.SubMatches(5)
   
    n = n + 1
Next

End Sub

Function ReadFile(ByVal filename As String) As String

  Dim objFSO As Scripting.FileSystemObject
  Set objFSO = New Scripting.FileSystemObject
  If objFSO.FileExists(filename) Then
    Set objTStream = objFSO.OpenTextFile(filename, ForReading)
    strText = objTStream.ReadAll
    Set objTStream = Nothing
    Set objFSO = Nothing
   
    ReadFile = strText
    End If
       
End Function

Hi this works fine if there is only 1 entry .I just put about 20 mails data into the txt file and it starts hanging .I even left it run for 15 min and still no output...

Can paste the txt file here. 5-10 mails should be alright.

Here the data is.


From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Monday, October 08, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Sunday, October 07, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Saturday, October 06, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Friday, October 05, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Thursday, October 04, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Wednesday, October 03, 2007 9:31 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

Scanning "C:\WINDOWS\SoftwareDistribution\EventCache\{1EC6AA57-D272-4A8D-9B55-AEA2249D32CB}.bin" returned SAV Interface error 0xa0040210: The file could not be accessed.

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.



From:      sophos@plc.com on behalf of Sophos Anti-Virus [sav@ACCAPP.deve.Group.co]
Sent:      Tuesday, October 02, 2007 9:30 PM
To:      Sharath
Subject:      SAV message from: ACCAPP

User: NT AUTHORITY\SYSTEM
Scan: Daily
Machine: ACCAPP

File "C:\WINDOWS\system32\DWRCS.EXE" has been identified as suspicious file of type 'Sus/Behav-1014'.
      Please send a sample to Sophos.

The attempt to delete the infected file "C:\WINDOWS\system32\DWRCS.EXE" failed. The user does not have the rights to perform the action on the infected file.


For this data i just get this in excel.

Monday, October 08, 2007 9:30 PM      ACCAPP      C:\WINDOWS\system32\DWRCS.EXE

If i put the same data say 10 times more it hangs

Thanks a lot for the wounderful help..

Hi wanted another macro which can convert all mails in specified folder in outlook to excel

Sendername    Sent to    Date   Time    Subject   body

If there is a way i can post a new Q...Please let me know...

I tried exporting outlook folder to pst but dont like the way i get the data.