Import Outlook form data into Access

[yetstar]

Hi all

I've searched everywhere for a solution to this problem....and every site I come to has numerous examples of how to save Outlook Contact data into a database, but nothing on how to automatically store info from forms into Access table.

So, I assume the code will be in Access. When you run the macro it will look in a certain email folder, return all of the data within the forms in that folder and store it into an Access table.

Does ANYBODY know how to do this? I thought it'd be a much more common problem than it appears to be??

Or am I just being stupid

Thanks for your help!

 

[Banana]

To access any Outlook objects, make a linked table.

When you're at the database window, go to:

File -> Get External Data -> Linked Table

In the dialog that opens, change the file extension to "Outlook", then you can access your own Outlook folder, whether inbox, calendar or contacts.

HTH.

 

[yetstar]

No it was for forms - linking tables wont include the info on the Outlook form, just whats in the email

I've worked it out (nearly...!)

Use this Macro in Access:


Option Compare Database

Private Sub cmdImport_Click()

Dim appOut As Outlook.Application
Dim appInspect As Outlook.Inspector
Dim appItem As Outlook.MailItem

Set appOut = New Outlook.Application
Set appInspect = appOut.ActiveInspector
Set appItem = appInspect.CurrentItem

Me.Sent = appItem.UserProperties.Item("Sent").Value

(Where Me.Sent is the form field and "Sent" is the table field etc)

 

[dsfcom]

Public Folder Tasks & Form Data to Access?

I have several public task folders from which I would like to import the task information into Access including some form data. I've tried the table linking deal but it doesn't include the start date and other form data. I know the EntryIDs and StoreIDs but can't figure out how to make it import. Any ideas? Here's my current code:

' Set up DAO objects (uses existing "tblTasks" table)
   Dim rst As DAO.Recordset
   Set rst = CurrentDb.OpenRecordset("tblTasks")


   ' Set up Outlook objects.
   Dim ol As New Outlook.Application
   Dim olns As Outlook.NameSpace
   Dim cf As Outlook.MAPIFolder
   Dim c As Outlook.TaskItem
   Dim objItems As Outlook.Items
   Dim Prop As Outlook.UserProperty
   Dim txtEntryID, txtStoreID

   txtEntryID = Screen.ActiveForm.ENTRYID
   txtStoreID = Screen.ActiveForm.STOREID
   
   Set olns = ol.GetNamespace("MAPI")
   Set cf = olns.GetFolderFromID(txtEntryID, txtStoreID)
   Set objItems = cf.Items
   iNumTasks = objItems.Count
   If iNumTasks <> 0 Then
      For i = 1 To iNumTasks
         If TypeName(objItems(i)) = "TaskItem" Then
            Set c = objItems(i)
            rst.AddNew
            rst!TASK = c.Subject
            rst!DueDate = c.DueDate
            rst!STARTDATE = c.STARTDATE
            rst!Status = c.Status
            rst!Rec = c.IsRecurring
            rst!SECTION = Screen.ActiveForm.DutySection
            rst!Remarks = c.Body
            rst.Update
         End If
      Next i
      rst.Close
   End If

After the last rst! I would like to add a reference to a field on a custom tab:

rst!EQUIPID = c.MyCustomTab.EQUIPID (or whatever will work)

Any ideas?