Export Outlook to Access

[shavenlunatic]

Office 2003
---

Hi,

Tried searching around the forums but too many results and couldn't find anything relating to Outlook > Access.

I want to manually run a marco which will scan a given folder in Outlook which will contain several standard emails. To give more of an idea, the format looks like this:

Payroll number     : [1234]
Surname            : [doe]
Forename           : [john]
Department         : [ThisDept]
Room/Floor         : [9th floor]
Absence Start      : [2006-11-16]
Shift Start        : [9:00]
Absence Type       : [Sickness]
Absence Reason     : [Plague]
Return Date        : [18.11.06]
Contact Phone      : [012345678910]
Line manager email : [you@me.com]
Site               : [A]

I want each line exporting into an access database, and each mail constituting a new record. This easy enough to do?

Thanks in advance,

Shaven

________
clueless

 

[CautionMP]

shavenlunatic,
You left out a couple of details, off the top of my head.[ul]
[li]Will the macro run in Outlook and push the data to Access?[/li]
[li]Will the macto run in Access and pull the data from Outlook?[/li][/ul]
This makes a difference in what method you use to actually create the record in your database.

Without knowing how you will create the record here are two routines that will interate an Outlook folder and parse the data out of an email body a place it in an array. The last piece would be moving the data from the array to your table.

[navy]Sub[/navy] IterateOutlookFolderItems()
[navy]On Error GoTo[/navy] IterateOutlookFolderItems_[navy]Error[/navy]
[navy]Dim[/navy] objOutlook [navy]As Object[/navy]
[navy]Dim[/navy] objFolder [navy]As Object[/navy]
[navy]Dim[/navy] objTarget [navy]As Object[/navy]
[navy]Dim[/navy] objItem [navy]As Object[/navy]
[navy]Set[/navy] objOutlook = GetObject(, "Outlook.Application")
[green]'This would be a public folder[/green]
[navy]Set[/navy] objFolder = objOutlook.Session.Folders("Finance")
[green]'This is a sub-folder in the public folder[/green]
[navy]Set[/navy] objTarget = objFolder.Folders("Test")
[navy]For Each[/navy] objItem [navy]In[/navy] objTarget.Items
 [navy]If[/navy] objItem.Class = 43 [navy]Then[/navy]  [green]'olMail[/green]
    [navy]With[/navy] objItem
      ParseMailBody .Body
    [navy]End With[/navy]
  [navy]End If[/navy]
[navy]Next[/navy] objItem

IterateOutlookFolderItems_Exit:
[navy]Set[/navy] objItem = [navy]Nothing[/navy]
[navy]Set[/navy] objTarget = [navy]Nothing[/navy]
[navy]Set[/navy] objFolder = [navy]Nothing[/navy]
[navy]Set[/navy] objOutlook = [navy]Nothing[/navy]
[navy]Exit Sub[/navy]

IterateOutlookFolderItems_Error:
Debug.Print Now, "IterateOutlookFolderItems", Err.Number, Err.Description
[navy]Stop[/navy]
[navy]Resume[/navy] IterateOutlookFolderItems_Exit
[navy]End Sub[/navy]

[navy]Sub[/navy] ParseMailBody(BodyText [navy]As[/navy] String)
[navy]On Error GoTo[/navy] ParseMailBody_[navy]Error[/navy]
[navy]Dim[/navy] intLine [navy]As Integer[/navy]
[navy]Dim[/navy] strLines() [navy]As[/navy] String, strParts() [navy]As String[/navy]
[navy]Dim[/navy] strOutput(12, 1) [navy]As String[/navy]
[navy]If[/navy] Len(BodyText) = 0 [navy]Then GoTo[/navy] ParseMailBody_Exit
strLines = Split(BodyText, vbCrLf)
[navy]For[/navy] intLine = 0 [navy]To[/navy] 12
  strParts = Split(strLines(intLine), ":")
  strOutput(intLine, 0) = Trim(strParts(0))
  strOutput(intLine, 1) = Trim(strParts(1))
  strOutput(intLine, 1) = Replace(strOutput(intLine, 1), "[", "")
  strOutput(intLine, 1) = Replace(strOutput(intLine, 1), "]", "")
[navy]Next[/navy] intLine

[green]'The text from the email should be fully deconstructed so[/green]
[green]'write the data to your table here[/green]
[navy]Stop[/navy]

ParseMailBody_Exit:
[navy]Exit Sub[/navy]

ParseMailBody_Error:
Debug.Print Now, "ParseMailBody", Err.Number, Err.Description
[navy]Stop[/navy]
[navy]Resume[/navy] ParseMailBody_Exit
[navy]End Sub[/navy]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)