Export email from Outlook into Access...

Status
Not open for further replies.
R

RWRFYXJuc2hhdw

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
 
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

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
 
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 " 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

> > >

> 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
>
 
Please use the ADO manual and see how the Find function works.

Best regards

Michael Bauer

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 " 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
>

>> >> >>

>

>
>> 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
> >
 
Status
Not open for further replies.
< Check sent email and reply if have specific words | Harvest Data from Outlook Form >
Similar threads
Thread starter Title Forum Replies Date
R Outlook 2010 How do you export 2 email Accounts' 2010 Inbox Files to new computer (2019)? Using Outlook 0
S How to export urls from email to excel and check the status of the url ? Using Outlook 5
A How to export one email data to excel Using Outlook 1
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
J How can I EXPORT my email address bok? Using Outlook 1
J Export an email profile to PST Outlook VBA and Custom Forms 3
U Export of Message Header / Unique ID to Excel Sheet When email is Outlook VBA and Custom Forms 1
T How to Export & Import GMAIL Contacts into Outlook 2021 ? Using Outlook 4
Owl Export Outlook PDF Attachment as JPG? Outlook VBA and Custom Forms 4
K Disabling import/export button to restrict PST creation Using Outlook 3
K Export Folder to PST Outlook VBA and Custom Forms 2
O Export Outlook calendar appointments by filters and date range Outlook VBA and Custom Forms 1
M Export-Import .pst file problems Using Outlook 2
M How to export Voting Results with user names and their responses Outlook VBA and Custom Forms 13
J Export Zimbra to Outlook PST Using Outlook.com accounts in Outlook 1
C Export NSF Lotus Notes files in Outlook PST format? Using Outlook 1
C How to export Outlook data to csv? Using Outlook 0
T How to Export mailbox from Outlook 2019 to MBOX format? Using Outlook 1
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
P How to export voting results using VBA? Outlook VBA and Custom Forms 2
R How To Repair OST File & Export OST File to Outlook PST ? Using Outlook 3
JohnViuf Export task list view settings to other pc Outlook VBA and Custom Forms 16
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
N Export details to a excel spreadsheet using macros Using Outlook 0
avant-guvnor Export Calendar, Birthdays and Holidays Outlook VBA and Custom Forms 0
Diane Poremsky Export (Save) Outlook Contact photos Using Outlook 0
K Macro to search emails between two time slots on public folders with excel export Outlook VBA and Custom Forms 12
W macro to export outlook emails to excel Outlook VBA and Custom Forms 6
E Exchanger service export detection Exchange Server Administration 1
R Correct a wrong csv export file from WLM 2012 Using Outlook 0
Q Outlook 2016\365 export specific rules to import in another system Exchange Server Administration 1
H How to export *all* fields from Outlook Using Outlook 2
K Export Organizational Forms Library Exchange Server Administration 1
N Export Outlook custom forms fields to excel Outlook VBA and Custom Forms 1
E RSS export as OPML problem Using Outlook 4
A export free / busy from exchange Exchange Server Administration 0
C Export to excel missing items, but a larger total? BCM (Business Contact Manager) 6
M Outlook 2013/365 won't export 99% of my contacts to csv Using Outlook 2
T Outlook 2007 Export tasks to Excel Using Outlook 2
J Opening/Archiving/Import/Export PST files Using Outlook 4
H Select one of Contact-Mailadesses to Export > Excel or Winword Outlook VBA and Custom Forms 2
Sarge USMC Cannot import/export Outlook 13 Using Outlook 6
L Outlook 2007 Codes Export and Import Using Outlook 9
L Outlook 2007 Toolbar Export and Import Using Outlook 7
S Requried a VBA Code to export Calander details to excel... Outlook VBA and Custom Forms 4
Jennifer Murphy Can I export individual emails? Using Outlook 1
R Export Address Book VBA Using Outlook 0
W How to export additional fields in contacts Using Outlook 1
R Recover/export rules from a backup .PST Using Outlook 2
J Formatting Contacts to Export for iPhone Using Outlook 0

Similar threads

Back
Top