asked on

Change subject line to [table lookup]

I want to create a script that is called by a rule that takes the string of the subject line and finds the record in a (table?) somewhere and returns a value corresponding to the record that was found.

To be more specific, I have faxes that are being converted into e-mail, which in the subject line contains the fax #. I would like to do a lookup of some kind to a table that stores the fax #'s and corresponding store location. The result being the subject line gets changed from the fax # to the store location.

I am experienced with VBA in Excel and Access, but have not used it in Outlook before.

Thank you

Chris Bottomley

We can certainly help ... it depends on where you want to master it from ... is the table in access and do you want to trigger off an incoming email or will you want to pick up a specific email for processing.

Chris

wipnav

ASKER

The table could be stored anywhere (access, excel, txt). I would like to trigger this off an incoming e-mail.

Chris Bottomley

Keying to my strengths then excel would be the easiest for me to integrate so can you provide a sample of an excel file structure you would use ... i.e. is it only Fax number and store location in the 'table'.

An example if one exists is best but if not it's easy enough to create the initial file for the purpose.

Chris

wipnav

ASKER

Hey Chris,

I don't have the list of all of the fax numbers and store locations populated yet. The table structure would simply be Fax # and store location columns.

Thanks for your help,

Steve

Chris Bottomley

That's fine i'll test an outline and supply the file.

In regard to a script to run from a rule ... can you define what a fax number will look like ... prefix or anything else and what punctuation needs to be considered. Basically I need to recognise a fax number for example as different from (for example) a telephone or order number.

Chris

wipnav

ASKER

The fax numbers come in as "1-123-456-7890". The fax # e-mails will be the only type of e-mail being sent to this e-mail address so we don't have to restrict the rule to execute only under certain conditions.

ASKER CERTIFIED SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wipnav

ASKER

This works great! I am just calling this procedure as a script from the advanced rules settings and it changes the subject line to the correct location. Thanks for your help, A++.

Steve

Chris Bottomley

For purposes of general usage ... the library routines used are as below ... they were used from a library module called regexp hence the regexp prefix but if used in the same code module as the Q_26849002 sub then no prefix is necessary.

Steve

My apologies for the assumption ... I should have been more explicit.

Chris

Function getDatabyRegEx(strFindin As String, strPattern As String, Optional strReplacement As String = "", Optional bolGlobalReplace As Boolean = True, Optional bolMatchCase As Boolean = False) As Variant
Dim colmatch As Object
Dim itm As Variant
Dim retArray() As String
Dim intBounds As Integer

    intBounds = -1
    If valDatabyRegEx(strFindin, strPattern, bolMatchCase) Then
        With CreateObject("vbscript.regexp")
            .IgnoreCase = Not bolMatchCase
            .Global = bolGlobalReplace
            .Pattern = strPattern
            Set colmatch = .Execute(strFindin)
            If bolGlobalReplace Then
                For Each itm In colmatch
                    intBounds = intBounds + 1
                    ReDim Preserve retArray(0 To intBounds)
                    retArray(intBounds) = itm
                Next
            Else
                ReDim retArray(0)
                retArray(0) = colmatch(0)
            End If
        End With
        getDatabyRegEx = retArray
    Else
        getDatabyRegEx = Array("")
    End If
    
End Function

Function valDatabyRegEx(strFindin As String, strPattern As String, Optional bolMatchCase As Boolean = False) As Boolean
    
    With CreateObject("vbscript.regexp")
        .IgnoreCase = Not bolMatchCase
        .Pattern = strPattern
        valDatabyRegEx = .test(strFindin) = True
    End With
    
End Function

Open in new window