Outlook Customized Rules

HI,

Could you be more precise

Which OL version you have?

What do you want to do with the array data, if not in the array what should the rule do?

Regards

I'm using Outlook 2013

What I am trying to do, as a start, is create a list with two columns:

Column 1 - Job # / Key Word
Column 2 - Folder

The rule would search the list for a matching (Job # / Key Word) in the subject or body of the email and move a copy to the appropriate (Folder)

I would populate the array with information from a spreadsheet list when Outlook opens and use the array for all emails received.

The reason I'm considering the array is that when I tried this code to open excel with each email it was way too slow:

Thanks

----------------------------------------------------------------------------------------------------------
Sub MyRule(Item As Outlook.MailItem)
    Dim objOL As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objContact As Outlook.ContactItem
    Dim objItems As Outlook.Items
    Dim objContactsFolder As Outlook.MAPIFolder
    Dim obj As Object
   
    Dim NameFilter As String, strFile As String, i As Integer
   
    Dim RowNo As Integer, MaxRows As Integer
   
    Set xlApp = CreateObject("Excel.Application")

    If xlApp = "Microsoft Excel" Then
   
    Else
        With xlApp
            .Visible = True
            .EnableEvents = False
        End With
    End If
   
    strFile = "C:\Personal\OutlookData\OutlookRuleFileEmails.xlsm"
On Error GoTo openit
    If sourceWB.Name <> "OutlookRuleFileEmails.xlsm" Then
openit:
        Set sourceWB = xlApp.Workbooks.Open(strFile) ', , False, , , , , , , True)
        Set sourceWS = sourceWB.Worksheets("EmailRule")
        sourceWB.Activate
    End If
   
    Set objOL = CreateObject("Outlook.Application")
    Set objNS = objOL.GetNamespace("MAPI")
    Set objContactsFolder = objNS.GetDefaultFolder(olFolderContacts)
    Set objItems = objContactsFolder.Items

    NameFilter = "[Email1Address] = " & """" & Item.SenderEmailAddress & """"
   
    Set objContact = objItems.Find(NameFilter)
   
On Error GoTo NoContactFound
   
        If objContact.Class = olContact Then
            RowNo = 1
            MaxRows = sourceWS.Cells(1, 1)
       
            While RowNo <= MaxRows
                    MsgBox "Second " & vbNewLine & Item.Subject & vbNewLine & Item.SenderEmailAddress & vbNewLine & NameFilter & vbNewLine & objContactsFolder.Name & vbNewLine & objContact.Email1Address & vbNewLine & Format(Item.ReceivedTime, "mm/dd/yyyy") & vbNewLine & sourceWS.Cells(i, 2) & vbNewLine & sourceWS.Cells(i, 3) & vbNewLine & i
               
                If Item.SenderEmailAddress = sourceWS.Cells(i, 2) Then
                    MsgBox "Third " & vbNewLine & Item.Subject & vbNewLine & Item.SenderEmailAddress & vbNewLine & NameFilter & vbNewLine & objContactsFolder.Name & vbNewLine & objContact.Email1Address & vbNewLine & Format(Item.ReceivedTime, "mm/dd/yyyy") & vbNewLine & sourceWS.Cells(i, 2) & vbNewLine & sourceWS.Cells(i, 3) & vbNewLine & i
                    objContact.Body = objContact.Body & vbNewLine & "--------------------" & vbNewLine & Format(Item.ReceivedTime, "mm/dd/yyyy") & " " & Item.Subject & vbNewLine & sourceWS.Cells(i, 3)
                    objContact.Save
                End If
               
                RowNo = RowNo + 1
            Wend
        End If
NoContactFound:

End Sub

Hi,

First you have to place your code in the this outlook module

the Variable
Private arrDataForRules As Variant before any code

the startup event will fire Private Sub Application_Startup()

then the arrDataForRules data will be available to the other macro in your module

Private arrDataForRules As Variant
Private Sub Application_Startup()


    Set xlApp = CreateObject("Excel.Application")

    If xlApp = "Microsoft Excel" Then

    Else
        With xlApp
            .Visible = True
            .EnableEvents = False
        End With
    End If

    strFile = "C:\Personal\OutlookData\OutlookRuleFileEmails.xlsm"
On Error GoTo openit
    If sourceWB.Name <> "OutlookRuleFileEmails.xlsm" Then
openit:
        Set sourceWB = xlApp.Workbooks.Open(strFile) ', , False, , , , , , , True)
        Set sourceWS = sourceWB.Worksheets("EmailRule")
        sourceWB.Activate
        sourceWS.Activate
        Set myRange = Range(Range("B1"), Range("C" & Cells.Rows.Count).End(xlUp))
        arrDataForRules = myRange
    End If

End Sub

Sub Macro()
arrD = arrDataForRules ' so you can see the values in the Locals Window
For Idx = LBound(arrDataForRules) To UBound(arrDataForRules)
    MsgBox arrDataForRules(Idx, 1)
    MsgBox arrDataForRules(Idx, 2)
Next
End Sub

Select allOpen in new window

Regards

Thanks

I'll work on it later today

Hmm

Can't seem to get it to run

First it required me to define variables so I edited as follows:

Sub Macro()
Dim arrD As Variant, arrDataForRules As String, idx As Variant
arrD = arrDataForRules ' so you can see the values in the Locals Window
For idx = LBound(arrDataForRules) To UBound(arrDataForRules)
    MsgBox arrDataForRules(idx, 1)
    MsgBox arrDataForRules(idx, 2)
Next
End Sub

Then when I try to run the macro I get an error message with lbound highlighted that says "Compile error: Expected array"

Sorry I'm a novice at using arrays - I hope you can help.

Thanks

Further trouble shooting?? - the start-up macro doesn't seem to run - I modified it as follows to test for a value when I run the macro under a different name it works fine but as the start-up macro it doesn't run - do I need to change a setting?

Using Outlook 2013?

Private Sub Application_Startup()

    Dim strFile As String
       
    Set xlApp = CreateObject("Excel.Application")

    If xlApp = "Microsoft Excel" Then

    Else
        With xlApp
            .Visible = True
            .EnableEvents = False
        End With
    End If

    strFile = "C:\Personal\OutlookData\OutlookRuleFileEmails.xlsm"
On Error GoTo openit
    If sourceWB.Name <> "OutlookRuleFileEmails.xlsm" Then
openit:
        Set sourceWB = xlApp.Workbooks.Open(strFile) ', , False, , , , , , , True)
        Set sourceWS = sourceWB.Worksheets("EmailRule")
        sourceWB.Activate
        sourceWS.Activate
'        Set myRange = Range(Range("B1"), Range("C" & Cells.Rows.Count).End(xlUp))
'        arrDataForRules = myRange
    End If

msgbox = sourceWS.Cells(3, 2)


End Sub

Amazing - I moved the code to the ThisOutlookSession Module and it worked like a dream.

This is why I subscribe to Experts Exchange!!!!!!!