CODEWITHSUNDEEP
excelvbaoutlook
z0rd
z0rd

Reputation: 49

Apply VBA code to e-mails containing "string" in the subject

I am using VBA code to send info from Outlook to Excel so I can filter it and automate the work.

The code is executing for all e-mails received.

I want to execute it on e-mails with subject starting with "EK".

I tried the InStr as below:

If InStr(xMailItem.Subject, "EK") = 0 Then
   Exit Sub
End If

Where should I put this line of code?

Private Sub GMailItems_ItemAdd(ByVal Item As Object)

    Dim xMailItem As Outlook.MailItem
    Dim xExcelFile As String
    Dim xExcelApp As Excel.Application
    Dim xWb As Excel.Workbook
    Dim xWs As Excel.Worksheet
    Dim xNextEmptyRow As Integer
    Dim linhas As Variant, i As Integer
    Dim linhaInicial As Long
    Dim numeroCaracteresAssunto As Integer
    Dim assuntoEmail As String
    Dim k As Integer
           
    On Error Resume Next
    If (Item.Class <> olMail) Then Exit Sub
    Set xMailItem = Item
    
    xExcelFile = "EXCELFILEPATH.xlsx"
    If IsWorkBookOpen(xExcelFile) = True Then
        Set xExcelApp = GetObject(, "Excel.Application")
        Set xWb = GetObject(xExcelFile)
        If Not xWb Is Nothing Then xWb.Close True
    Else
        Set xExcelApp = New Excel.Application
    End If
                              
    Set xWb = xExcelApp.Workbooks.Open(xExcelFile)
    Set xWs = Sheets.Add
    numeroCaracteresAssunto = Len(xMailItem.Subject)
    assuntoEmail = Right(xMailItem.Subject, numeroCaracteresAssunto - 16)
    xWs.Name = UCase(assuntoEmail)
    xNextEmptyRow = xWs.Range("B" & xWs.Rows.Count).End(xlUp).Row + 1
    linhaInicial = 1
    
    With xWs
        linhas = Split(xMailItem.Body, vbNewLine)
        
        For i = 0 To UBound(linhas)
            Cells(linhaInicial + i, 1).Value = linhas(i)
            linhaInicial = linhaInicial + 1
        Next
        
        For k = 1 To i

            xWs.Range("B" & k).FormulaLocal = "=SEERRO(ÍNDICE($A$1:$A$999;MENOR(SE(ÉNÚM(LOCALIZAR(""PC"";$A$1:$A$999));CORRESP(LIN($A$1:$A$999);LIN($A$1:$A$999)));" & k & "));"""")"
            xWs.Range("B" & k).FormulaArray = xWs.Range("B" & k).Formula
        
        Next k
    End With
End Sub

Upvotes: 0

Views: 230

Answers (1)

niton
niton

Reputation: 9179

Instr is case sensitive.

If InStr(UCase(xMailItem.Subject), UCase("EK")) = 0 Then

Either UCase or LCase.

On both parts, or you may run into an "eK" typo.

Upvotes: 1

Related Questions