excelvbaticket-system

How do I add in my current code the condition where in if the email subject already found in a column, skip that email


My code is as follows. I just want a function to skip the email subject if it's already in the worksheet. I have already tried couple of things but didnt work. If you have follow up question please comment here. :(

If filteredItems.Count = 0 Then
    Debug.Print "No emails found"
    Found = False
Else
    Found = True
    For Each itm In filteredItems
    '''
    If Range("B" & Rows.Count).Value <> itm.ReceivedTime Then
    Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Format(itm.ReceivedTime, "yyyymmdd")
    Range("C" & Rows.Count).End(xlUp).Offset(1).Value = itm.Subject
    Range("B" & Rows.Count).End(xlUp).Offset(1).Value = itm.ReceivedTime
    Range("D" & Rows.Count).End(xlUp).Offset(1).Value = itm.SenderName
    Range("H" & Rows.Count).End(xlUp).Offset(1).Value = itm.Body
    Range("H:H").WrapText = False
    Range("E" & Rows.Count).End(xlUp).Offset(1).Value = "Not Started"
    
    
    '''
     Debug.Print itm.Subject
    End If
    Next
End If


'If the subject isn't found:
If Not Found Then
    MsgBox "No new ticket as of" & " " & Now() & "." & " " & "Please try again later."
Else
End If

Solution

  • Use Worksheetfunction.Countif(Range("C:C"), "*" & itm.Subject & "*") > 0 as your check.

    Also it would be best practice to reference a worksheet variable e.g.

    Dim Wksht as Worksheet
    Set Wksht = Activeworkbook.Sheets("Sheet1")
    
    If Wksht.Range(...
    

    -- this will stop your code being affected if you select another worksheet part way through.