Solved

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

Posted on 2014-12-16
9
247 Views
1 Endorsement
Last Modified: 2014-12-17
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??
1
Comment
Question by:crcsupport
  • 5
  • 4
9 Comments
 
LVL 90

Accepted Solution

by:
John Hurst earned 500 total points
Comment Utility
Open an email in Outlook and use File, Properties. That brings up headers. You can copy these and paste into a text editor. For the very few times I need to do this, the Properties approach suffices.
0
 
LVL 1

Author Comment

by:crcsupport
Comment Utility
I asked the question after doing that for 300 emails and feel like I got carpal turnel syndrom
0
 
LVL 90

Assisted Solution

by:John Hurst
John Hurst earned 500 total points
Comment Utility
I think you should filter your spam out and delete it first. Responding to spam or trying to find headers to get to the source of the spam is almost impossible today. Spammers appear to have their own ISP for sending out crap.

So filter out spam and then don't be concerned about it.

I look for headers maybe once per year (and I get 100 - 200 emails per day every day.
0
 
LVL 1

Author Comment

by:crcsupport
Comment Utility
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
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 90

Assisted Solution

by:John Hurst
John Hurst earned 500 total points
Comment Utility
As I noted, get rid of spam first. It is not worth any effort to look for spam headers. Save your breath. Once done, you should have next to no headers to look for and then you should be able to look through as you need (that is, manually).

I just opened an arbitrary email, and you can copy properties (headers) and paste into Notepad (or something).
0
 
LVL 1

Author Comment

by:crcsupport
Comment Utility
Anyone knows answer or tips to the original question, please reply
0
 
LVL 1

Author Comment

by:crcsupport
Comment Utility
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

0
 
LVL 90

Expert Comment

by:John Hurst
Comment Utility
@crcsupport  - Thank you and thanks for the update. I was happy to help.
0
 
LVL 1

Author Comment

by:crcsupport
Comment Utility
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!!!!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Set OWA language and time zone in Exchange for individuals, all users or per database.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now