• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Outlook Customized Rules

I have a vba rule that runs in outlook each time an email is received. I would like to have it quickly access a list (maybe in excel or a text file) and make a choice where a copy of the email is stored based on the information in the list.

Is there an easy / fast way to have outlook open and filter a list like this.

I thought of loading the list one time upon opening as an array but I'm not familiar with using arrays.

Thanks
0
rogerdjr
Asked:
rogerdjr
  • 5
  • 3
1 Solution
 
Rgonzo1971Commented:
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
0
 
rogerdjrAuthor Commented:
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
0
 
Rgonzo1971Commented:
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

Open in new window

Regards
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
rogerdjrAuthor Commented:
Thanks

I'll work on it later today
0
 
rogerdjrAuthor Commented:
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
0
 
rogerdjrAuthor Commented:
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
0
 
Rgonzo1971Commented:
Hi,

Is your security setting medium?

is your code in the ThisOutlook module

pls test startup event with this little code

Private Sub Application_Startup() 
 MsgBox "Welcome, " & Application.GetNamespace("MAPI").CurrentUser 
End Sub

Open in new window

then Close Outlook and reopen it

Regards
0
 
rogerdjrAuthor Commented:
Amazing - I moved the code to the ThisOutlookSession Module and it worked like a dream.

This is why I subscribe to Experts Exchange!!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now