Solved

Creating database from Outlook Forms Templates

Posted on 2014-01-10
4
409 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 250 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Set OWA language and time zone in Exchange for individuals, all users or per database.
Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now