Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Change subject line to [table lookup]

Posted on 2011-02-25
9
Medium Priority
?
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34986866
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
ID: 34988059
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
ID: 34988339
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Author Comment

by:wipnav
ID: 34988571
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34988644
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
ID: 34988747
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 2000 total points
ID: 34989180
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
ID: 34989297
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
ID: 34991010
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

604 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