CODEWITHSUNDEEP
vbaoutlook
Matthew Lutz
Matthew Lutz

Reputation: 1

How can I count the instances of a certain string in an email and store that number?

So this has come up before, but the only answers given were to use Regular Expressions, but it's not making sense to me.

I already have a module that is copying text out of selected emails and dumping it into a CSV, I need it to also count instances of a certain string of text and also dump that count. What I have I bastardized from code I found here:

Sub CopyToExcel()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim olItem As Outlook.MailItem
Dim vText As Variant
Dim sText As String
Dim vItem As Variant
Dim i As Long
Dim rCount As Long
Dim bXStarted As Boolean
Const strPath As String =     
"F:\Scripting\Export\AEX_JUNIPER_LOGGING\Input\orders.csv" 'the path of the     workbook

If Application.ActiveExplorer.Selection.Count = 0 Then
MsgBox "No Items selected!", vbCritical, "Error"
Exit Sub
End If
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
    Application.StatusBar = "Please wait while Excel source is opened ... "
    Set xlApp = CreateObject("Excel.Application")
    bXStarted = True
    End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("orders")
xlSheet.Rows(2 & ":" & xlSheet.Rows.Count).ClearContents

'Process each selected record
 rCount = xlSheet.UsedRange.Rows.Count
  For Each olItem In Application.ActiveExplorer.Selection
    sText = olItem.Body
    vText = Split(sText, Chr(13))
    'Find the next empty line of the worksheet
     rCount = rCount + 1
    'Check each line of text in the message body
    For i = UBound(vText) To 0 Step -1

      If InStr(1, vText(i), "JOBID:") > 0 Then
            vItem = Split(vText(i), Chr(58))
            xlSheet.Range("A" & rCount) = Trim(vItem(1))
        End If

        If InStr(1, vText(i), "RMA Number   : ") > 0 Then
            vItem = Split(vText(i), Chr(58))
            xlSheet.Range("B" & rCount) = Trim(vItem(1))
        End If

        If InStr(1, vText(i), "DESTINATION WAREHOUSE : ") > 0 Then
            vItem = Split(vText(i), Chr(58))
            xlSheet.Range("C" & rCount) = Trim(vItem(1))
        End If

        If InStr(1, vText(i), "TRACKING NUMBER  : ") > 0 Then
            vItem = Split(vText(i), Chr(58))
            xlSheet.Range("D" & rCount) = Trim(vItem(1))
        End If

    Next i
    xlWB.Save
Next olItem
xlWB.Close SaveChanges:=True
If bXStarted Then
    xlApp.Quit
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
Set olItem = Nothing
End Sub

How can I amend this to include the new code I need?

Upvotes: 0

Views: 300

Answers (1)

Kris B
Kris B

Reputation: 446

Without using regex, you can find the number of occurrences of a substring (subStr) within a string (str) using a similar algorithm as follows:

'Replaces all instances of substring with nothing, effectively removing all instances of it
newStr = Replace(str, subStr, "")

'Determine how many instances were removed
instancesOfSubStr = (len(str) - len(newStr)) / len(subStr)

At this point I imagine you can just accumulate it in a global variable after processing each cell.

Upvotes: 3

Related Questions