E
EdEarnshaw
I've come across some vba code allowing me to export emails from Outlook to
Access. It works wonderfully, except that repeating the operation adds
duplicate entries to the table in Access and forcing Access to not accept
duplicate entries stops the Outlook macro.
The reason I need to export out of Outlook stems from the fact that these
emails will be imported from several different Outlook accounts.
Is there any way to have the vba code check for duplicates before exporting
the emails, and if there are duplicates to export what is not a duplicate.
I'm using Outlook 2007 and Access 2007. The macro I'm using with Outlook is
as follows:
Sub ExportMailByFolder()
'Export specified fields from each mail
'item in selected folder.
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Set ns = GetNamespace("MAPI")
Set objFolder = ns.PickFolder
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim intCounter As Integer
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
'DSN and target file must exist.
adoConn.Open "DSN=OutlookData;"
adoRS.Open "SELECT * FROM email", adoConn, _
adOpenDynamic, adLockOptimistic
'Cycle through selected folder.
For intCounter = objFolder.Items.Count To 1 Step -1
With objFolder.Items(intCounter)
'Copy property value to corresponding fields
'in target file.
If .Class = olMail Then
adoRS.AddNew
adoRS("OutlookID") = .EntryID
adoRS("Subject") = .Subject
adoRS("Body") = .Body
adoRS("FromName") = .SenderName
adoRS("ToName") = .To
adoRS("FromAddress") = .SenderEmailAddress
adoRS("CCName") = .CC
adoRS("BCCName") = .BCC
adoRS("DateRecieved") = .ReceivedTime
adoRS("DateSent") = .SentOn
adoRS.Update
End If
End With
Next
adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing
Set ns = Nothing
Set objFolder = Nothing
End Sub
Access. It works wonderfully, except that repeating the operation adds
duplicate entries to the table in Access and forcing Access to not accept
duplicate entries stops the Outlook macro.
The reason I need to export out of Outlook stems from the fact that these
emails will be imported from several different Outlook accounts.
Is there any way to have the vba code check for duplicates before exporting
the emails, and if there are duplicates to export what is not a duplicate.
I'm using Outlook 2007 and Access 2007. The macro I'm using with Outlook is
as follows:
Sub ExportMailByFolder()
'Export specified fields from each mail
'item in selected folder.
Dim ns As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Set ns = GetNamespace("MAPI")
Set objFolder = ns.PickFolder
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim intCounter As Integer
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
'DSN and target file must exist.
adoConn.Open "DSN=OutlookData;"
adoRS.Open "SELECT * FROM email", adoConn, _
adOpenDynamic, adLockOptimistic
'Cycle through selected folder.
For intCounter = objFolder.Items.Count To 1 Step -1
With objFolder.Items(intCounter)
'Copy property value to corresponding fields
'in target file.
If .Class = olMail Then
adoRS.AddNew
adoRS("OutlookID") = .EntryID
adoRS("Subject") = .Subject
adoRS("Body") = .Body
adoRS("FromName") = .SenderName
adoRS("ToName") = .To
adoRS("FromAddress") = .SenderEmailAddress
adoRS("CCName") = .CC
adoRS("BCCName") = .BCC
adoRS("DateRecieved") = .ReceivedTime
adoRS("DateSent") = .SentOn
adoRS.Update
End If
End With
Next
adoRS.Close
Set adoRS = Nothing
Set adoConn = Nothing
Set ns = Nothing
Set objFolder = Nothing
End Sub