Export email from Outlook into Access...
 

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

Export email from Outlook into Access...
 

Before calling adoRS.AddNew you can use the Find function and search for
whatever will identify the recordset for sure, for instance look whether or
not the EntryID already exists.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: http://www.vboffice.net/product.html?pub=6&lang=en


Am Thu, 4 Jun 2009 15:02:07 -0700 schrieb 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

Export email from Outlook into Access...
 

This helps, but I think I need a little more guidance as I do not know vba at
all and I'm not even sure what to be looking for.

I've added this line
adoRS.Find (OutlookID) Like .EntryID

but what do I actually need to be adding in code-wise that will keep the
macro from importing the duplicates.

Perhaps there is another solution to this problem that I'm not seeing.

"Michael Bauer [MVP - Outlook]" wrote:


Before calling adoRS.AddNew you can use the Find function and search for
whatever will identify the recordset for sure, for instance look whether or
not the EntryID already exists.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: http://www.vboffice.net/product.html?pub=6&lang=en


Am Thu, 4 Jun 2009 15:02:07 -0700 schrieb 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

Export email from Outlook into Access...
 

Please use the ADO manual and see how the Find function works.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: http://www.vboffice.net/product.html?pub=6&lang=en


Am Mon, 15 Jun 2009 13:27:01 -0700 schrieb EdEarnshaw:

This helps, but I think I need a little more guidance as I do not know vba
at
all and I'm not even sure what to be looking for.

I've added this line
adoRS.Find (OutlookID) Like .EntryID

but what do I actually need to be adding in code-wise that will keep the
macro from importing the duplicates.

Perhaps there is another solution to this problem that I'm not seeing.

"Michael Bauer [MVP - Outlook]" wrote:


Before calling adoRS.AddNew you can use the Find function and search for
whatever will identify the recordset for sure, for instance look whether
or
not the EntryID already exists.

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: http://www.vboffice.net/product.html?pub=6&lang=en


Am Thu, 4 Jun 2009 15:02:07 -0700 schrieb 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