Welcome to Outlook Exchange


	Become a Columnist \| Microsoft Exchange Site \| Microsoft Support Site \| MSDN Exchange Site

How did you like this article? Please vote and let us know.

Subscribe to OutlookExchange

What's New

Columnist

Development

More Resources

Software

Exporting Outlook Journal Entries to a Microsoft Access Table

This section shows how you can export information stored in an Outlook Journal folder into a Microsoft Access table. This can be useful if you are using the Journal folder in Outlook to keep a history of a project. You can store this information in Microsoft Access where you can sort and view it in several ways.

The following code sample assumes that you have two tables, Projects and ProjectHistory, in a Microsoft Access database. The first contains general information about a project and the second contains details on the progress of a project. For the purposes of the code, the Project table has at least three fields: ProjectName, ProjectId, and LastUpdated. It also has an index called Subject on the ProjectName field. The ProjectName corresponds to the Subject field of an Outlook JournalItem. The ProjectHistory table requires the fields ProjectId (the foreign key), DetailDateTimeStamp, and DetailDuration.

The UpdateProjectHistory procedure is passed the name of a project. (Note that the name of a project must correspond to a subject in an Outlook JournalItem.) It opens the Journal folder and retrieves just the items for that project using the Restrict method of the JournalItems collection. It then checks to see if the name passed to it is in the Projects table. If the project is new, the project name won't be found, so it adds the project to the table.

Next it retrieves the date from the projects LastUpdated field and stores it in dteUpdated. If it does not find a date, it assigns 1/1/95 to dteUpdated, which insures that all the JournalItems will be seen as new (assuming there are no JournalItems older than 1/1/95). Next it iterates through these items. If a JournalItem has a CreationTime greater than the projects LastUpdated date, then the information in this item is added as a new record in the ProjectHistory table.

AccSamp.mdb contains the sample code as well as the two tables needed to run this solution. The code is found in the modProjectHistory module. To run the code, click Update Project in the Samples form. When prompted for project, use a name from the Subject field of your Journal.

Note The use this code from a Microsoft Access module, you must reference the Microsoft Outlook 8.0 Object Library.

Sub UpdateProjectHistory(strSubject As String)

	Dim tblProjects As Recordset
	Dim tblDetails As Recordset
	Dim dbs As Database
	Dim lngProjectId As Long
	Dim oOutlook As New Outlook.Application
	Dim colItems As Items
	Dim intCtr As Integer
	Dim dteUpdated As Date
	Dim strMessage As String

	Const MESSAGE_CAPTION = "Updating Project History"

	On Error GoTo Err_UpdateProjectHistory

	'Get all JournalItems for project.
	Set colItems = oOutlook.GetNamespace("MAPI"). _
			GetDefaultFolder(olFolderJournal). _
			Items.Restrict("[Subject] = '" & strSubject & "'")

	'Check to see if there are any Journal items. If there
	'are not any Journal items then display a message and
	'exit.
	If colItems.Count > 0 Then

		Set dbs = CurrentDb

		'Open tables and search for subject in Projects.
		Set tblProjects = dbs.OpenRecordset("Projects")
		Set tblDetails = dbs.OpenRecordset("ProjectHistory")

		'Seek is used because it is fast. It has the disadvantage
		'that it will not work on an attached table.
		tblProjects.Index = "Subject"
		tblProjects.Seek "=", strSubject

		'If there is no match, add new record and set the
		'recordset's Bookmark property to LastModified to move to
		'that record.
		With tblProjects
			If .NoMatch Then
				.AddNew
				tblProjects!Subject = strSubject
				.Update
				.Bookmark = .LastModified
			End If
		End With
		
		'Get the ProjectId from the current record.
		lngProjectId = tblProjects!SubjectId
		'If the LastUpdated field is null, assign dteUpdated an early
		'date to use all records. Otherwise assign LastUpdated to
		'dteUpdated.
		If IsNull(tblProjects!LastUpdated) Then
			dteUpdated = #1/1/95#
		Else
			dteUpdated = tblProjects!LastUpdated
		End If
	
		'Add details if there are any that are dated later than dteUpdated.
		If colItems.Count > 0 Then
			With tblDetails
				For intCtr = 1 To colItems.Count
					If CDate(colItems(intCtr).CreationTime) > dteUpdated Then
						.AddNew
						tblDetails!SubjectId = lngProjectId
						tblDetails!DateTimeStamp = colItems(intCtr). _
											CreationTime
						tblDetails!Duration = colItems(intCtr).Duration
						.Update
					End If
				Next intCtr
				.Close
			End With
			'Update LastUpdated field.
			With tblProjects
				.Edit
				tblProjects!LastUpdated = Now
				.Update
				.Close
			End With
		End If

		strMessage = "Project successfully updated."
		MsgBox strMessage, vbOKOnly, MESSAGE_CAPTION

	Else
		strMessage = "Project not found!"
		MsgBox strMessage, vbCritical, MESSAGE_CAPTION
	End If

Exit_UpdateProjectHistory:
	On Error Resume Next

	Set oOutlook = Nothing
	Set tblProjects = Nothing
	Set tblDetails = Nothing
	Set dbs = Nothing

	Exit Sub 

Err_UpdateProjectHistory:
	strMessage = "An unexpected error, #" & Err & " : " & _
								Error & " has occured."

	MsgBox strMessage, vbCritical, MESSAGE_CAPTION
	Resume Exit_UpdateProjectHistory

End Sub

Disclaimer: Your use of the information contained in these pages is at your sole risk. All information on these pages is provided "as is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Pro Exchange. OutlookExchange.Com and Pro Exchange shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.