?
Solved

Creating database from Outlook Forms Templates

Posted on 2014-01-10
4
Medium Priority
?
433 Views
Last Modified: 2014-01-17
This is a follow on question from an earlier question where BlueDevilFan explained to me how to create an email template form in outlook through which employees would send their data via this email template to a central mailbox.

My follow on question is:  Now that I have these templates ready to go and in a publicly accessible Forms Library, how do I extract the answers given in these emails, from their central mailbox location - and into a database?

BlueDevilFan it would be great if you could address this as a follow on to your earlier response.
Thank you
Lomed_Geoff
0
Comment
Question by:Lomed_Geoff
  • 3
4 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 39771883
Hi, Geoff.

I got the link to the question.  I'll post a solution as soon as I can, hopefully within the next 24-48 hours.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 1000 total points
ID: 39778950
Geoff,

Sorry, it's clearly taken me longer than 24-48 hours to get to this.  

In general, you'll need something like this.  

Sub ExportFormData()
    'On the next line edit the message class to match the message class fo the form you want to target
    Const TARGET_CLASS = "IPM.Note.LomedGeoff1"
    'On the next line edit the connection string used to connect to your database.  See this site for details on connection strings: http://www.connectionstrings.com/
    Const MY_CONNECTION_STRING = ""
    'On the next line edit the insert statement to match you table and field names
    Const SQL_INSERT_STATEMENT = "INSERT INTO [Table] (ProjectNumber,StartDate,EndDate,TaskCode,Reason,F1Title,F1Hours,F2Title,F2Hours,F3Title,F3Hours) VALUES('%S1','%S2','%S3','%S4','%S5','%S6',%I2,'%S7',%I2,'%S8',%I3)"
    Dim olkMsg As Object, _
        adoCon As Object, _
        strSQL As String, _
        strStartDate As String, _
        strEndDate As String, _
        strTaskCode As String, _
        strReason As String, _
        strProjectNumber As String, _
        strF1Title As String, _
        strF2Title As String, _
        strF3Title As String, _
        intF1Hours As Integer, _
        intF2Hours As Integer, _
        intF3Hours As Integer
    Set adoCon = CreateObject("ADODB.Connection")
    adoCon.Open MY_CONNECTION_STRING
    For Each olkMsg In Application.ActiveExplorer.Selection
        If olkMsg.Class = olMail Then
            If olkMsg.MessageClass = TARGET_CLASS Then
                strStartDate = olkMsg.UserProperties("StartDate").Value
                strEndDate = olkMsg.UserProperties.Item("EndDate").Value
                strF1Title = olkMsg.UserProperties.Item("Function1Title").Value
                intF1Hours = olkMsg.UserProperties.Item("Function1Hours").Value
                strF2Title = olkMsg.UserProperties.Item("Function2Title").Value
                intF2Hours = olkMsg.UserProperties.Item("Function2Hours").Value
                strF3Title = olkMsg.UserProperties.Item("Function3Title").Value
                intF3Hours = olkMsg.UserProperties.Item("Function3Hours").Value
                strTaskCode = olkMsg.UserProperties.Item("TaskCode").Value
                strReason = olkMsg.UserProperties.Item("Reason").Value
                strProjectNumber = olkMsg.UserProperties.Item("ProjectNumber").Value
                strSQL = SQL_INSERT_STATEMENT
                strSQL = Replace(strSQL, "%S1", strProjectNumber)
                strSQL = Replace(strSQL, "%S2", strStartDate)
                strSQL = Replace(strSQL, "%S3", strEndDate)
                strSQL = Replace(strSQL, "%S4", strTaskCode)
                strSQL = Replace(strSQL, "%S5", strReason)
                strSQL = Replace(strSQL, "%S6", strF1Title)
                strSQL = Replace(strSQL, "%S7", strF2Title)
                strSQL = Replace(strSQL, "%S8", strF3Title)
                strSQL = Replace(strSQL, "%I1", intF1Hours)
                strSQL = Replace(strSQL, "%I2", intF2Hours)
                strSQL = Replace(strSQL, "%I3", intF3Hours)
                adoCon.Execute strSQL
            End If
        End If
    Next
    adoCon.Close
    Set adoCon = Nothing
    Set olkMsg = Nothing
End Sub

Open in new window


I can't be more specific without knowing the name of your form, what type of database you're using, and the names of the fields in the database.

You'd use this by selecting the messages you want to import into the database.  The macro would then read those messages, extract the custom fields, and add a record to the database (one record per message).
0
 

Author Closing Comment

by:Lomed_Geoff
ID: 39788191
Thanks to BlueDevilFan for his patience, professionalism and dedication to getting me the solutions.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39788323
You're welcome, Geoff.
0

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.

Question has a verified solution.

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

With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
I came across an unsolved Outlook issue and here is my solution.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

599 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