Trying to fill a form/table importing emails from outlook

[tbrown7777]

I have a database that i'm essentially moving my inbox into an access
database. I can email information into a table, but I need to be able to
categorize them. I have combo boxes on a form that I would like to use as I
bring over each email so I can catalog them. I either need to figure out how
to show this form during my loop or set the form values to the values i bring
over and wait for me to hit "next" to import the next email. I thought that
I could import the data into the table and then show the userform (the
for/next loop waits until i finish adding the categories) then continues
on.... Here is the code I'm trying to get working.
1. Is it better to add a record and fill the form with the data i bring
over or try to add it to the table first, then show the form?
2. I'm not sure of the syntax for the do.cmd showform so i can edit data.


Private Sub CmdGetEmails_Click()
On Error GoTo Err_CmdGetEmails_Click

Dim Olapp As Outlook.Application
Dim Olmapi As Outlook.NameSpace
Dim Olfolder As Outlook.MAPIFolder
Dim OlDelete As Outlook.MAPIFolder
Dim OlMail As Object 'Have to late bind as appointments e.t.c screw it up
Dim OlItems As Outlook.Items

Dim Rst As DAO.Recordset
Dim db As DAO.Database

Dim InboxCounter As Integer

Set db = CurrentDb
Set Rst = db.OpenRecordset("tblEmail", dbOpenDynaset) 'Open table Email

DoCmd.GoToRecord , , acLast

'Create a connection to outlook
Set Olapp = CreateObject("Outlook.Application")
Set Olmapi = Olapp.GetNamespace("MAPI")
'Open the inbox
Set Olfolder = Olmapi.GetDefaultFolder(olFolderInbox)
Set OlDelete = Olmapi.GetDefaultFolder(olFolderDeletedItems)
Set OlItems = Olfolder.Items
'Set up a loop to run till the inbox is empty (otherwise it skips some after
deleting)
InboxCounter = OlItems.Count

'For each mail in the collection check the subject line and process
accordingly
' Skip unread emails and ones with attachments
For i = InboxCounter To 1 Step -1
Set OlMail = Olfolder.Items(i)
If (OlMail.UnRead = False) And (OlMail.Attachments.Count = 0) Then
Rst.AddNew
Rst!FromName = OlMail.SenderName
Rst!ToName = OlMail.To
Rst!CCName = OlMail.CC
Rst!Subject = OlMail.Subject
Rst!SendDate = OlMail.ReceivedTime
Rst!Body = OlMail.Body

OlMail.Move OlDelete

Rst.Update
End If
InboxCounter = InboxCounter - 1
Next

Rst.Close
Set db = Nothing
Set Olapp = Nothing
Set OlItems = Nothing

Exit_CmdGetEmails_Click:
Exit Sub

Err_CmdGetEmails_Click:
MsgBox Err.Description
Resume Exit_CmdGetEmails_Click

End Sub

 

[David H]

1) What is the specific need to use Access to store your emails? Will the
Journal suffice?

2) When you say 'pull over' are you copying over the MailItems in a batch or
as their sent? -You can setup Outlook to automatically run code that opens
Access and saves the email. (ItemSend event). Also, it might be easier to
categorize the email when you create it and then have your code check the
Categories property of the MailItem.

http://msdn.microsoft.com/en-us/library/bb175161.aspx

 

[tbrown7777]

1. my company is deleting all pst files and wants us to save anything
important as msg files on our harddrive. You can imagine how hard it is to
find anything.

2. i wrote code to bring it over using outlook whenever I felt like cleaning
up my inbox (batch mode), but it doesn't have any 'category' info (I'm not
using the outlook category field) when I do it that way. When I had a pst
file, I could file emails under different folders to sort them for later.
Maybe the best way is to bring them over in a batch process and then go back
and loop through them using the form I created to add categories. I was
hoping I could do it an email at a time. I wrote this code in access and it
will move my inbox over, but I'm not smart enough to figure out how to bring
up my form as each email is brought over to add my 'filing info'. I created
a command button to "Get emails" on my form and put the code into the command
button, but it doesn't work.

 

[Pete D.]

Is your company really deleting them or just deleting off the servers to
save space. Our company requires all PST files to be kept on local hard
drives but to delete them completely sounds like someone doesn't understand
the value of email history. I sure would kick it upstairs to someone's
boss. Reinventing the wheel sure doesn't make much sense.

 

[tbrown7777]

actually, our corporate attorneys are pushing it. It's almost impossible to
find old project files without a 'preview'....so much for a paperless
environment. tasks and contacts are the only think not being restricted.

 

[tbrown7777]

I am also struggling with trying to save attachments in a seperate table
related to each email. I think I need to determine the type of file (.doc or
..xls) to tell access what to do with it. I can attach an ole object into the
table manually, but this code generates an error. "Object doesn't support
this property or method". what i wouldn't do for a macro record button!


For Each olAttachment In OlMail.Attachments
Set Rst = db.OpenRecordset("tblAttachments", dbOpenDynaset) '
open attachment table
Rst.AddNew
Rst!Attachment = OlMail.Attachment
Rst.Update
Rst.Close
Next olAttachment

 

[Pete D.]

Your MS Access file is going to blot with a bunch of attachements. Might I
suggest a slightly different way. Access makes a great document management
tool with a little work. What about storing/exporting your email in Msg
documents including the attachment in the msg documents and use Access to
index those documents. Make folders on the hard drive similar to folders in
outlook and email subjects as document names. Then you could use one of the
many sample vba codes to do a recursive scan of the hard drive starting with
your email/msg root folder to build the index complete with hyperlinks to
open those documents with the click of a button. Msg files can hold the
attachments and when opened will open up back in outlook allowing you to
edit, forward or whatever. Just a thought. Pete

 

[David H]

You're corporate attorneys are IDIOTS. What they're trying to do is to create
a certain degree of protection in the event that the organization is sued. By
stating that the official policy is not to keep emails, they limit the
documentation that might have to be turned over in the event of a subpoena.
However, what they don't realize is that if they've publicly stated via Memo
that any messages that you want to save should be saved as a .msg file, they
have just created a situation where if a subpeona is served, they'd have to
search through all the PC's anyways. I'm not certain, but I want to say that
a *.PST file is required to use the full Outlook client. Of course, if you
accessing it via OWA then no PST file is needed.

 

[David H]

I concur. Not to mention that if something happens to the .mdb file you loose
all of it. I still think that you should investigate using the Item_Send
event in Outlook to automatically categorize the emails as you go and/or save
them as .msg file on your hard drive. (Its a lot easier to categorize as you
send them as opposed to afterward when you have 200 that you have to go
through.) To help with categorizing them, you should be able to create code
wherein you select a category and then based on the category automatically
save the .msg file in the correct folder. Assuming that you create a folder
that's the same name as the Category in Outlook and that all folders have the
same basic path as in

Base Path:
C:\Documents and Settings\dch3\Outlook
Folder Paths:
C:\Documents and Settings\dch3\Outlook\Project Runway
C:\Documents and Settings\dch3\Outlook\The Manhattan Project
C:\Documents and Settings\dch3\Outlook\Weapons X

Using Item_Send to Set a Category
http://www.outlookcode.com/codedetail.aspx?id=87

Using Redemption to save a message as a .msg and delete the original message
(requires use of Outlook Redemption available here
http://www.dimastr.com/redemption/home.htm)
http://www.outlookcode.com/codedetail.aspx?id=1364

Save attachments automatically
http://www.outlookcode.com/codedetail.aspx?id=989

 

[tbrown7777]

many thanks for your suggestions.

I think I am going to go with a hybrid solution, meaning, any emails with
attachments will be saved as msg files and referenced, but ones without will
get ported to access. I didn't realize the sentto and cc fields are limited
to 255 characters as a text string, so i have to truncate those strings when
someone sends out a broadcast email to a zillion addresses. It's about 200k
per 100 emails so I'm still getting a feeling for whether or not it's going
to be too unweidly. I'm kinda enjoying being able to query emails many
different ways. I will probably add the functionality of bringing up the
email form in outlook so i can forward/reply to old emails.

 

[Pete D.]

If outlook is your default msg file handler (odds are as it is an outlook
format) a simple follow hyperlink will bring them up in outlook ready to do
whatever with. See msdn link below.
http://msdn.microsoft.com/en-us/library/aa204494(office.10).aspx