Using
Outlook or Exchange with a database
Everyone
is familiar with the possibility of using Outlook or Exchange with the
help of a database, for example, with Access. However, although there is
lot of information about this subject, the reproduction of that
information is not much.
In
this page it will be fully explained how to do it.
Users
that already use the Outlook forms might find some problems, in
particularly with prints and also in the work’s development with the
forms
This
means that:
If
the programmer creates an application with Outlook in way that all
information can be
stored
in a Public Folder of an Exchange Server, there are few simple ways of
running the results.
If
you try to connect Outlook to Access in this simple way:
-
In Access click in New
Table to add a new table.
-
In the dialog box New
Table, please choose Link Table and than OK.
-
In the dialog box Link,
in Files of Types, choose Outlook () or Exchange().
-
If you request an Outlook
perfil, show which one you will use.
-
In Link
Exchange/Outlook Wizard, choose the folder that you wil be
working and than click Next.
-
Save the Linked Table and
click Finnish.
You
will make it, but the custom fields created by the programmer will not
be recognized.
To
make possible the recognition of this kind of fields, please follow
these steps:
(I
created this database based in the Microsoft Application, Help Desk -
http://office.microsoft.com/downloads/9798/helpdesk.aspx)
Create
a database and save it with this name “importoutlook.mdb” then in
the database, create a table “tblHdrs” with these fields:
From
Subject
Received
Close
Software
Os
Technician
Tipoprob
Historical
After
creating the table, create a module and add the following code:
Option Compare Database
Dim ol As New
Outlook.Application
Dim PublicFolder As
MAPIFolder
Dim OldTaskItems As Items
Dim itm As Outlook.TaskItem
Dim AppPath As String
Sub ImportItems()
Set PublicFolder =
ol.GetNamespace("MAPI").Folders("Public
Folders").Folders("All Public
Folders").Folders("PT").Folders("Help Desk
Application").Folders("Tarefas Antigas")
Set OldTaskItems =
PublicFolder.Items.Restrict("[Subject] > ''")
Dim nmritens As Integer
nmritens =
OldTaskItems.Count
'
' If nmritens = 0 Then
' MsgBox
"Não há itens"
' Else
' MsgBox
" Existem " & nmritens & " itens para importar"
' End If
For Each itm In OldTaskItems
If nmritens > 1 Then
Set appAccess =
CreateObject("Access.Application")
strAccessPath =
appAccess.SysCmd(9)
strDBName =
"c:/documents and Settings/ricardosilva/my Documents/mdb/"
& "importoutlook.mdb" 'strAccessPath & "importoutlook.mdb"
Set dbe = CreateObject("DAO.DBEngine.36")
Set wks =
dbe.Workspaces(0)
Set dbs =
wks.OpenDatabase(strDBName)
Set rst =
dbs.OpenRecordset("tblHdrs")
rst.AddNew
rst.remetente =
itm.UserProperties("Behalf")
rst.assunto =
itm.UserProperties("Subject")
rst.recebido =
itm.UserProperties("Received Date")
rst.fechado =
itm.UserProperties("Close Date")
rst.software =
itm.UserProperties("Computer Software")
rst.os =
itm.UserProperties("Computer OS")
rst.tecnico
= itm.UserProperties("Technician Name")
rst.tipoprob =
itm.UserProperties("Problem Type")
rst.historial
= itm.UserProperties("History Text")
rst.Update
rst.Close
dbs.Close
End If
Next
End Sub
In
the Public Folder reference, please change to yours.
In
my case, I have the Application Desktop, above PT, that in his turn is
in the All
Public Folders structure.
In
the database’s location do the necessary modifications to set the path
to your database.
My
database is in c:\documents
and Settings\ricardosilva\my Documents\mdb\
Then
you just need to run the module and wait for the results.
Two
final notes about some problems that might happen:
If
the forms have not been published, will appear a message to do “enable
or disable macros”. You have to click in “enable macros” to work
correctly.
It
may occur errors in the module and the code will stop. This may happen
if you have the Access table’s fields with small size (normally a text
field size is 50), you just need to increase the field size. After the
fields’ increase, run the code again and it will work correctly. |
