Solved

Change subject line to [table lookup]

Posted on 2011-02-25
9
244 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:wipnav
  • 5
  • 4
9 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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
0
 
LVL 1

Author Comment

by:wipnav
Comment Utility
The table could be stored anywhere (access, excel, txt). I would like to trigger this off an incoming e-mail.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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
0
 
LVL 1

Author Comment

by:wipnav
Comment Utility
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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
0
 
LVL 1

Author Comment

by:wipnav
Comment Utility
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.
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
As a starter ... the following script should be called from your rule for incoming messages.  It uses a file of the form as supplied which needs to be saved to your PC and the correct path and filename used to replace:

Const strWBName As String = "C:\deleteme\Fax2Store.xls"

That said the incoming mail subjects should then be edited with the appropriate value from column 2

Chris
Sub Q_26849002(mai As MailItem)
Dim strFaxNumber As String
Dim xlApp As Object
Dim xlwb As Object
Dim xlws As Object
Dim rng As Object
Dim var As Variant
Const strWBName As String = "C:\deleteme\Fax2Store.xls"
Const xlwhole As Integer = 1

    With mai
        If RegExp.valDatabyRegEx(mai.Subject, "[0-9]-[0-9]{3}-[0-9]{3}-[0-9]{4}") Then
            ' It's a FAX number so ...
            strFaxNumber = RegExp.getDatabyRegEx(Application.ActiveInspector.CurrentItem.Subject, "[0-9]-[0-9]{3}-[0-9]{3}-[0-9]{4}")(0)
            'Look it up in the excel data
            If xlApp Is Nothing Then
                Set xlApp = CreateObject("excel.application")
            End If
            If xlwb Is Nothing Then
                For Each var In xlApp.Workbooks
                    If var.FullName = strWBName Then
                        Set xlwb = var
                        Exit For
                    End If
                Next
            End If
            If xlwb Is Nothing Then Set xlwb = xlApp.Workbooks.Open(strWBName)
            Set xlws = xlwb.Sheets("Sheet1")
            Set rng = xlws.Range("A:A").Find(What:=strFaxNumber, LookAt:=xlwhole, MatchCase:=False, SearchFormat:=False)
            mai.Subject = Replace(mai.Subject, strFaxNumber, rng.Offset(0, 1))
            mai.Save
            xlwb.Close
            xlApp.Quit
        Else
            'Do Nothing
        End If
    End With
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:wipnav
Comment Utility
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
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Outlook Free & Paid Tools
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now