Solved

Creating database from Outlook Forms Templates

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

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.
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

867 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

23 Experts available now in Live!

Get 1:1 Help Now