Transfering emails to Access database

[Sameer]

I am using following codes to tranfer emails from particular folder to access database:
Dim appAccess
Dim nms
Dim strFolder
Dim fld
Dim strAccessPath
Dim rst
Dim dbe
Dim wks
Dim dbs
Dim itms
Dim itm




Sub CommandButton1_Click()

Set nms = Application.GetNamespace("MAPI")
strFolder = "comtest"

Set fld = nms.Folders("Personal Folders").Folders(strFolder)

'Pick up path to Access database directory from Access SysCmd function

Set appAccess = CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)
'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version
'MsgBox "DBEngine version: " & strDBEngine
appAccess.Quit
If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "fbtestdb.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "fbtestdb.mdb"
Else
MsgBox "Unknown Office version; canceling"
Exit Sub
End If

'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase("C:\My Documents\comtest.mdb")

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("Table1")

'Set up reference to Outlook folder of items to export
Set itms = fld.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No communication requests to export"
Exit Sub
Else
MsgBox ItemCount & " Communication requests to export"
End If


'Set up reference to Outlook folder of items to export
Set itms = fld.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No Communication requests to export"
Exit Sub
Else
MsgBox ItemCount & " Communication requests to export"
End If

For Each itm In itms
rst.AddNew
'Custom Outlook properties

rst.Datecircular=itm.Sent
rst.subject =itm.Subject
rst.circular=itm.HTMLBody
rst.Update
Next

rst.Close
MsgBox "All communication requests exported!"
MsgBox "Quit Access"
appAccess.Quit


End Sub

Now, my problem is:
1.The date in access data base comes as 12/29/1899 for all the emails transferred.
2.Our corporate emails carry pictures from clip art and little formatting.When I transfer them to database, I loose all pictures and formatting.The body portion is kept as memo field in access.
Please help.
Thanks
Sameer

 

[Sameer]

Any thoughts on this or did I post it in wrong column??
Thanks
Sameer

 

[Fizzio]

I'll answer 2 first

Access cannot store a variety of information in 1 field and is not good at storing images anyway. Access only stores the information, not all the bells and whistles that go with it. You have a few options depending on how important the pictures and formatting is. If the text is more important then just accept how access has stored it.
If you really want the others, you will likely need to copy the HTMLBody into a file formay that will support the formatting and pics such as MSword then just store the path details to that file with the respective record.

OK, answer to 1

itm.sent
should read
itm.senton

the sent property is a true/false variable to flag whether the mail has been sent whereas SentOn is the date it was sent.

HTH

 

[Sameer]

Thanks, Fizzio
Item.Senton works perfectly alright now.
Thanks a lot for your help.I think we will go with the way access stores the value and give away formatting and images.
Also, one more quickie...please....the itemsent on brings in date and time it is sent on...like 8/1/2003 3:14:46 PM, now if I want to search by date, and enter in 8/1/2003 it returns no records because I am not entering in time...how do I deal with this in query so just by entering in date range I can get all the records in that range.
TIA...
S

 

[Fizzio]

You can do this a couple of ways.

If you only need the date and not the time from the SentOn property, try this line in your code

rst.Datecircular=Format(itm.SentOn,Short Date)

If you want to keep the time in the record but only want to search by date, It depends on how you perform the search - whether you use query by form or VBA code.

If you use QBF, format the date in the query ie

SentDate: Date([Datecircular])

 

[Sameer]

Superb!!!!!
You take away all my worries....Thank you very much for all the support....
S.

 

[wazza]

Hi,

I have tried running this code to see if I can something similar working.

I get the following error message:

"method or data member not found" (on the first line)

Set nms = Application.GetNamespace("MAPI")
strFolder = "inbox"



what does this mean?

 

[wazza]

Im using a POP connection - that anything to do with the problem ?

 

[fh2level]

I want to do the exact same thing. Im using Outlook, and a newbie at vb, but if you don't mind, is there much that I would have to change to get this code working on my comp?
Thank you so much