Post a Contest

Outlook/Excel routine to extract a regex from every email in a folder

Status: Closed
Prize: €20
Entries Received: 7
Winner: tushchak0716

Winner

Contest Brief

I need an Outlook vba routine that checks every incomming email in a folder and copy paste every unique instance of an ISIN (an international security identifier number) into an excel workbook

in MS excel vba regex that would be the following pattern "\b[A-Z]{2}\w{10}\b"
(2 capital letters Alphabetic characters followed by 10 alphanumeric characters)
for example: XS2294AA5739

The workbook should update such that newer ISINs pop at the top and push the olderones further down (I want to see the newes first, ie at the top)

I managed to create a routine that copy paste 1 ISIN but have not had the time to enhance it further to check every ISIN in an email or to check every email in a given folder (see the attachmen to get an idea of the code) There might be some redundant lines.

I use competitions to get to know freelancers that I can work further with. I expect more similar task to come up afterwards. The first solution that I find good enough wins, I will end the competition as soon as I find a good solution.

Recommended Skills

Winner

Post a Contest Like This

Employer Feedback

“He was fast, kind and delivered as requested ”

gabrieldileo, Germany.

Public Clarification Board

Contest Holder
- 3 years ago
Hi, I have the regex already, what I need is for the routine to check every email in an outlook folder, and copy paste the regex I programmed. Take a look at my example code. The first to submit a working solution wins
- 3 years ago
1. rupamkumarmandal
  - 3 years ago
  Hi,
  I’ve the VBA code (with routine check) ready and tested in my email box with multiple scenarios and sample RegExp. I’ve submitted the demonstration (VBA OutPut) as a screenshot in my submission #7 . Let me know your emailID so that I can send the code and the VB File for further review.
  As per your requirement few parameter values to be updated when you run it in your computer (Like Email Folder Name, The RegExp, routine check timings). Please also let me know for any modification require or any query.
  Please let me know your feedback to proceed further. Looking for your valuable feedback.
  - 3 years ago
rupamkumarmandal
- 3 years ago
Hi,
I’ve the VBA code (with routine check) ready and tested in my email box with multiple scenarios and sample RegExp. I’ve submitted the demonstration (VBA OutPut) as a screenshot in my submission #7 . Let me know your emailID so that I can send the code and the VB File for further review.
As per your requirement few parameter values to be updated when you run it in your computer (Like Email Folder Name, The RegExp, routine check timings). Please also let me know for any modification require or any query.
Please let me know your feedback to proceed further. Looking for your valuable feedback.
- 3 years ago
uali0012
- 3 years ago
I am waiting for your offer. Please give me the offer.
- 3 years ago
Krish148
- 3 years ago
Option Explicit
Public Sub SaveMessageAsMsg()
Dim oMail As Outlook.MailItem
Dim objItem As Object
Dim sPath As String
Dim dtDate As Date
Dim sName As String
Dim enviro As String
enviro = CStr(Environ("USERPROFILE"))
For Each objItem In ActiveExplorer.Selection
Set oMail = objItem
sName = oMail.Subject
ReplaceCharsForFileName sName, "_"
dtDate = oMail.ReceivedTime
sName = Format(dtDate, "yyyymmdd", vbUseSystemDayOfWeek, _
vbUseSystem) & Format(dtDate, "-hhnnss", _
vbUseSystemDayOfWeek, vbUseSystem) & "-" & sName & ".msg"
sPath = enviro & "\Documents\"
Debug.Print sPath & sName
oMail.SaveAs sPath & sName, olMSG
Next
End Sub
Private Sub ReplaceCharsForFileName(sName As String, _
sChr As String _
)
sName = Replace(sName, "/", sChr)
sName = Replace(sName, "\", sChr)
sName = Replace(sName, ":", sChr)
sName = Replace(sName, "?", sChr)
sName = Replace(sName, Chr(34), sChr)
sName = Replace(sName, "", sChr)
sName = Replace(sName, "|", sChr)
End Sub
- 3 years ago