crcsupport
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??
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Anyone knows answer or tips to the original question, please reply
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...
the csv file will look like this;
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
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
@crcsupport - Thank you and thanks for the update. I was happy to help.
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!!!!
1 Open macro editor from Outlook/Tools/macro/visual
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!!!!
ASKER