Solved

Create an Outlook Rule to generate a SQL Server action

Posted on 2008-06-23
5
280 Views
Last Modified: 2011-06-22
I receive standardized messages in Outlook. When I receive the message I go into SQL Server, go to the record referenced in the Outlook message and then go to a webpage stored as a URL in the referenced record. How would I set up a rule in Outlook that would upon receipt of the message, find the keyword, open SQL Server, match the keyword to the record and open the URL?
0
Comment
Question by:bpfsr
[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
  • 3
  • 2
5 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 21853926
Hi, bpfsr.

The short answer is to write a script that handles connecting to SQL server, doing the search, and launching the URL if a matching record is found.  You'd then create a rule that calls this script.  I can give you the pseudo-code for the process, but I don't have access to SQL server, so I can't provide a complete solution.
0
 

Author Comment

by:bpfsr
ID: 21855033
pseudo code would work, thank you.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 21855246
Ok.  You'll want something like this.  Here's how this will work.

1.  The rule fires and calls the script below, passing the message that triggered the rule as a parameter.
2.  The code parses the body and finds the reference to the SQL record.
3.  Create a connection to the SQL database
4.  Select the record matching the value from the message
5.  Retrieve the URL from the record
6.  Launch Internet Explorer
7.  Navigate to the URL
8.  Make IE visible
9.  Clean-up the objects to avoid memory leaks
Sub RunSQL(Item As Outlook.MailItem)
    Dim adoCon As Object, adoRS As Object, strKey As String, strURL As String, objIE As Object
    'Replace x on the next line with code to get the record reference from the message body.  I don't know what the messages look like, so I can't advise on the exact code.
    strKey = x
    Set adoCon = CreateObject("ADODB.Connection")
    'Replace Connection String on the next line with a conenction string for SQL Server
    adoCon.Open "Connection String"
    'Replace Fields, Table, and KeyField on the next line with the appropriate values
    Set adoRS = adoCon.Execute("SELECT Fields FROM Table WHERE KeyField = '" & strKey & "'")
    If (Not adoRS.bof) & (Not adoRS.EOF) Then
        'Replace URL_Field_Name on the next line with the name of the field in the database that holds the URL
        strURL = adoRS.Fields("URL_Field_Name").Value
        Set objIE = CreateObject("IntenetExplorer.Application")
        objIE.Navigate2 strURL
        objIE.Visible = True
    End If
    Set objIE = Nothing
    adoRS.Close
    Set adoRS = Nothing
    adoCon.Close
    Set adoCon = Nothing
End Sub

Open in new window

0
 

Author Comment

by:bpfsr
ID: 21975485
Hello,
Sorry it took so long to come back but I moved last week which put a crimp in - well - everything! Anyhow, I looked at your code and it does make sense to me. I have two questions:
1. When I create the rule, I see the option to run a script, but it looks like Outlook wants me to select the script from a list. Where do I save the script so it gets added to the list.
2. In the beginning of the code where 'x' equals the searched for phrase, assuming "find this text" was the key phrase, would you be able to tell me how to conduct that search?

Thank you
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 21977109
Hi, bpfsr.

No problem.  I know how moving can be.

1.  Press ALT+F11 to open Outlook's VB editor.  Expand the folders on the left under Project1.  The code can go in any module except ThisOutlookSession.  It'll then appear automatically in the list of scripts available to rules.

2.  I can't answer the question.  I've no idea what the message body will look like, so there's no way for me to know what it'll take to find the key text.  
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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