Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating database from Outlook Forms Templates

Posted on 2014-01-10
4
Medium Priority
?
425 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
[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
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

721 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