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

Question

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

*"I have already tried couple of things but didnt work."* Please tell **what** you have tried (show that code) and tell what went wrong, which errors you got or where you got stuck. Note that *"didn't work"* is a completely useless error description. • Please read [ask] and improve your question. Finally you need to ask a question so we can write an answer to it (you didn't ask one yet). — Pᴇʜ, Aug 03 '21 at 06:03
I pretty new in VBA. So far what I did was add for each or if statements in between the current loop in my code. As for the question, maybe someone can give me some advice on how to add a function where in if 'itm.subject' or subject name already exist in the worksheet, it should skip that one then proceed to the next. Apologies if this still doesn't help someone. — COCO, Aug 03 '21 at 06:17
Have a look at the [Range.Find method](https://learn.microsoft.com/en-us/office/vba/api/excel.range.find) or the [WorksheetFunction.Match method](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match) to check if the subject exists in the sheet (there are many tutorials here and on google how to use both of them). — Pᴇʜ, Aug 03 '21 at 06:37
I tried adding the range.find but apparently it returns error please see code below ```If Range("C:C").Find("Request for adding something (07/15/2021)") <> itm.Subject``` Then when I change to this one, it runs perfectly. ```If Range("C:C").Find("a") <> itm.Subject``` "a" is just to test if it works on other string — COCO, Aug 03 '21 at 06:54
The error is (Run time error '91': Object variable or With block variable not set) — COCO, Aug 03 '21 at 07:09
Find errors if you don't find anything, please read the documentation link I gave you. You need to specify more parameters then only `What`. At minimum `LookIn`, `LookAt`, `SearchOrder` and `MatchByte` needs to be specified. Also see [Range.Find method error handling](https://stackoverflow.com/a/50398015) for how to do it properly. — Pᴇʜ, Aug 03 '21 at 08:04

score 0 · Accepted Answer · answered Aug 03 '21 at 07:42

0

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.

answered Aug 03 '21 at 07:42

Spencer Barnes

2,809
1
7
26

Actually `ActiveWorkbook` is still no good idea (bad practice) as now you are safe against selecting another worksheet but not safe against selecting another workbook. Make sure to use `ThisWorkbook` whenever you can and only use `ActiveWorkbook` when you know why this is necessary and why you cannot use `ThisWorkbook`. There are only rare cases where `ActiveWorkbook` is really needed. – Pᴇʜ Aug 03 '21 at 08:06
1

Thank you so much @Spencer. This is really helpful. You dont know how much I needed the solution for this asap! Thank you – COCO Aug 03 '21 at 08:08
@Peh you are correct, but I find that once a sheet variable is set, changing the workbook usually isn't a vulnerability - certainly reusing `activeworkbook` throughout the code is bad, but not just for once. – Spencer Barnes Aug 03 '21 at 08:34
@SpencerBarnes True, that minimizes the issue with `ActiveWorkbook`, I still recommend `ThisWorkbook` wherever you can and `ActiveWorkbook` wherever `ThisWorkbook` is not suitable. – Pᴇʜ Aug 03 '21 at 13:37

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

1 Answers1