Creating database from Outlook Forms Templates

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
Lomed_GeoffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
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
David LeeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lomed_GeoffAuthor Commented:
Thanks to BlueDevilFan for his patience, professionalism and dedication to getting me the solutions.
0
David LeeCommented:
You're welcome, Geoff.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.