asked on

How to get 'Received: ' header info from junk email folder in Outlook to Excel

Hello, I'm trying to analyze spams to see where they are coming from. So all I want is, subject name, sender, Received (header) to excel.
Outlook built-in export tool and others outside allow to export subject name and sender name, but not 'Received:' header info.

I think there's a way to pull out the IP address on 'Received' header information of emails from a folder of Outlook or a folder where MSG files reside either using visual basic or powershell.

Does anyone know how to do this??

ASKER CERTIFIED SOLUTION

John

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crcsupport

ASKER

I asked the question after doing that for 300 emails and feel like I got carpal turnel syndrom

SOLUTION

John

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crcsupport

ASKER

I'm getting to the bottom of it. Actually I analyzed spams for past 3 days, identified only a handful of Cloud host have been sending most of our spams. Then, I found those cloud hosting service's all IP pool shows 87% of spam, looks like majority of their customers are spammers. So, I puled out the cloud hosts' IP address pool and start blocking them. Now my spam rate dropped noticeably. I like to do this in an automated way, can't click and copy/paste to get sender IP

SOLUTION

John

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crcsupport

ASKER

Anyone knows answer or tips to the original question, please reply

crcsupport

ASKER

I made the script!!!!
Anyone can use free, this is cool. You select all emails you want to get header info, then it will create csv file with;
email address, subject, IP(from Received Header), Received Header.

*Modify the file path and regular expression on the function to extract Received header info to your own...

' This script exports email to csv file with email address, subject, sender IP, Received Header info from outlook email
' Used Functions: file object, file save, file write, regular expression, date/time as file name, string
' author: crcsupport(EE) + Rgonzo1971(EE) + BlueDevilFan(EE)
' date: 12/17/2014

Private Declare Function ShellExecute Lib "shell32.dll" _
  Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Sub PrintMsgsWithInetHeaders()
    Dim olkMsg As Outlook.MailItem, _
        objFSO As Object, _
        objFile As Object, _
        intCounter As Integer, _
        strTempFile As String, _
        strFilename As String, _
        objIP As String, _
        objReceivedHeader As String
        
        
    intCounter = 1
    strSafeDate = DatePart("yyyy", Date) & Right("0" & DatePart("m", Date), 2) & Right("0" & DatePart("d", Date), 2)
    strSafeTime = Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)
    strTempFile = "c:\temp\" & strSafeDate & "-" & strSafeTime & ".csv"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(strTempFile)
    For Each olkMsg In Application.ActiveExplorer.Selection
               
                ' objFile.Write GetInetHeaders(olkMsg) & vbCrLf & vbCrLf
                objReceivedHeader = ExtractReceivedHeader(GetInetHeaders(olkMsg))
                objIP = ExtractIP(objReceivedHeader)
                objFile.Write olkMsg.SenderEmailAddress & ", " & Left(olkMsg.Subject, 50) & ", " & objIP & ", " & objReceivedHeader & vbCrLf & vbCrLf
                
                
        intCounter = intCounter + 1
    Next
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
    Set olkMsg = Nothing
End Sub

Function GetInetHeaders(olkMsg As Outlook.MailItem) As String
    ' Purpose: Returns the internet headers of a message.'
    ' Written: 4/28/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: 2007'
    Const PR_TRANSPORT_MESSAGE_HEADERS = "http://schemas.microsoft.com/mapi/proptag/0x007D001E"
    Dim olkPA As Outlook.PropertyAccessor
    Set olkPA = olkMsg.PropertyAccessor
    GetInetHeaders = olkPA.GetProperty(PR_TRANSPORT_MESSAGE_HEADERS)
    
    Set olkPA = Nothing
End Function

Function ExtractIP(strText As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(strText)

If allMatches.Count <> 0 Then
    result = allMatches.Item(0).Value
End If

ExtractIP = result


End Function

Function ExtractReceivedHeader(strText As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "Received:.*[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}.*by\smail.yoursmtpserver.com"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(strText)

If allMatches.Count <> 0 Then
    result = allMatches.Item(0).Value
End If

ExtractReceivedHeader = result


End Function

Open in new window

the csv file will look like this;

affordableburialinsurance@latest-toner.eu	 [[SPAM]] - Burial life insurance for less than a c	 64.6.104.208	 Received: from divans.latest-toner.eu ([64.6.104.208]) by mail.yousmtpserver.com	
AlaskaVacations@3tries.country	 [[SPAM]] - Deep discounts for Alaskan Cruises - Me	 188.68.164.101	 Received: from 0381fe0b.3tries.country ([188.68.164.101]) by mail.yousmtpserver.com	
asseenontv@catfish.rocks	 [[SPAM]] - Browse These As Seen On TV Bargains to 	 79.120.211.191	 Received: from mail.catfish.rocks ([79.120.211.191]) by mail.yousmtpserver.com	
atza@klutztit.com	 [[SPAM]] - This winter your power bill will be zer	 198.2.203.3	 Received: from storm.klutztit.com ([198.2.203.3]) by mail.yousmtpserver.com	
atza@phonycalm.com	 [[SPAM]] - Never receive a power bill for winter -	 76.73.99.50	 Received: from jesup.phonycalm.com ([76.73.99.50]) by mail.yousmtpserver.com	
Blood_Pressure_Fix@difending.com	 [[SPAM]] - Will your high BP kill you? It almost k	 206.190.147.116	 Received: from viru116.difending.com ([206.190.147.116]) by mail.yousmtpserver.com	
Blood_Pressure_Fix@intaresting.com	 [[SPAM]] - 1 weird food that KILLED my blood press	 192.187.102.166	 Received: from mai166.intaresting.com ([192.187.102.166]) by mail.yousmtpserver.com	
cableservice@catfish.rocks	 [[SPAM]] - Browse Cable Service Specials and Save 	 79.120.211.191	 Received: from mail.catfish.rocks ([79.120.211.191]) by mail.yousmtpserver.com	
Camellia@plyerlaid.com	 [[SPAM]] - I put my hearing aid in the garbage - M	 104.148.31.83	 Received: from tew.plyerlaid.com ([104.148.31.83]) by mail.yousmtpserver.com	
Daniela@mallardfitness.com	 [[SPAM]] - Sick of failure? - Message was found to	 69.4.81.57	 Received: from bamail1.mallardfitness.com ([69.4.81.57]) by mail.yousmtpserver.com	
Fulton@confron.info	 [[SPAM]] - Thank you for your recent visit.  - Mes	 173.232.157.148	 Received: from confron.info ([173.232.157.148]) by mail.yousmtpserver.com	
Haley@pacbonehealing.com	 Bone restoration | healing-Osteoporosis	 69.4.81.46	 Received: from irgrl.pacbonehealing.com ([69.4.81.46]) by mail.yousmtpserver.com	
Kaitlyn+Barnes@dtposteohealing.com	 The truth about Osteoporosis	 (MayoMD)	 69.4.81.43	
ledcoloredcandles@zan011vpshostmint.link	 LED Colored Candles.  BuyOne/GetOne 12-16.14.	 23.228.97.3	 Received: from zan011vpshostmint.link ([23.228.97.3]) by mail.yousmtpserver.com	
ledcoloredcandles@zan011vpshostmint.link	 LED Colored Candles.  BuyOne/GetOne 12-16.14.	 23.228.97.3	 Received: from zan011vpshostmint.link ([23.228.97.3]) by mail.yousmtpserver.com	
mailer@infusionmail.com	 Don't Miss Out On the Two Most Exciting Events of 	 208.76.24.54	 Received: from mta-c-24-54.infusionmail.com ([208.76.24.54]) by mail.yousmtpserver.com	
Mark@parksskinsoperfect.com	 [[SPAM]] - She looks 20 again... (Pics) - Message 	 69.4.81.50	 Received: from mxb-00122801.parksskinsoperfect.com ([69.4.81.50]) by mail.yousmtpserver.com	
Moreno@yoursearchforthin.com	 [[SPAM]] - Stay slim this season - Message was fou	 69.4.81.56	 Received: from lauriesdiamonds.yoursearchforthin.com ([69.4.81.56]) by mail.yousmtpserver.com	
Moreno@yoursearchforthin.com	 [[SPAM]] - Stay slim this season - Message was fou	 69.4.81.56	 Received: from lauriesdiamonds.yoursearchforthin.com ([69.4.81.56]) by mail.yousmtpserver.com	
OnlineWork@beltafon.eu	 [[SPAM]] - Extra Holiday Cash. Facebook Revenue - 	 172.245.242.24	 Received: from tasty.beltafon.eu ([172.245.242.24]) by mail.yousmtpserver.com	
PlayOsmo@vj3i.eu	 [[SPAM]] - New IPad game improves Kids social Skil	 209.249.46.163	 Received: from 0086b80a.vj3i.eu ([209.249.46.163]) by mail.yousmtpserver.com	
Ross@dtposteohealing.com	 [[SPAM]] - Fractures from weak bones	 the truth...	 69.4.81.43	
Ross@dtposteohealing.com	 Fractures from weak bones	 the truth...	 69.4.81.43	
seat@siglacepe.com	 [[SPAM]] - Upgrade #89405 now available for your p	 64.34.255.214	 Received: from jump.siglacepe.com ([64.34.255.214]) by mail.yousmtpserver.com	
Shawn@stollarhealth.com	 [[SPAM]] - Sick of failure? - Message was found to	 69.4.81.59	 Received: from biwiron.stollarhealth.com ([69.4.81.59]) by mail.yousmtpserver.com	
Shawn@stollarhealth.com	 [[SPAM]] - Sick of failure? - Message was found to	 69.4.81.59	 Received: from biwiron.stollarhealth.com ([69.4.81.59]) by mail.yousmtpserver.com	
Toilet_Paper_Coupons@44matts.country	 [[SPAM]] - Toilet Paper Coupons - Message was foun	 138.128.0.13	 Received: from 02bd92ba.44matts.country ([138.128.0.13]) by mail.yousmtpserver.com	
UltimateCigarDeals@colone.eu	 [[SPAM]] - The Complete cigar--Lovers' package; Fo	 104.168.41.166	 Received: from 024e79e2.colone.eu ([104.168.41.166]) by mail.yousmtpserver.com	
UnbeatableCigarDeal@cihatem.eu	 [[SPAM]] - Holiday Sale. 10 Premium Cigars (includ	 23.227.97.114	 Received: from orange.cihatem.eu ([23.227.97.114]) by mail.yousmtpserver.com	
window@snickoaks.com	 Urgent updates to your policy	 198.2.203.4	 Received: from still.snickoaks.com ([198.2.203.4]) by mail.yousmtpserver.com	
yellow@courtupby.com	 [[SPAM]] - Bad eyesight corrected by Christmas - M	 195.154.32.139	 Received: from cores.courtupby.com ([195.154.32.139]) by mail.yousmtpserver.com	
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				 Received: from cyclesports.dtposteohealing.com ([69.4.81.43]) by mail.yousmtpserver.com
				
				
				
				
				
				 Received: from cyclesports.dtposteohealing.com ([69.4.81.43]) by mail.yousmtpserver.com
				
				 Received: from cyclesports.dtposteohealing.com ([69.4.81.43]) by mail.yousmtpserver.com

Open in new window

John

@crcsupport - Thank you and thanks for the update. I was happy to help.

crcsupport

ASKER

In order to use the script with your outlook,
1 Open macro editor from Outlook/Tools/macro/visual basic editor
2 click Insert Module icon
3 paste the script.
4 Modify the file path and regular expression to find your SMTP server on the functions to extract Received header info to your own...
5 click Save
6 Collect all junk emails from users and save under any sub folder
7. Select the emails and run the macro to export as a csv file. (*Since exchange server will limit the number of RCP connection, select 30-50 emails at once to export, create as many csv files as you have to).

You may wan to make a short cut to run the macro on your outlook;
1 right-click on Outlook toolbar space
2 select Customize
3 click Toolbars tab
4 click New
5 Add a new toolbar container 'Extract Header' or any you like.
6 Switch to Command tab
7 drag the macro to the newly created tool bar container.

ENJOY!!!!