Solved

programatically export emails in outlook 2003

Posted on 2006-11-14
7
459 Views
Last Modified: 2009-07-29
I want to be able to create a macro or code within outlook so that I can select all the items in a folder and export them to an access file ?
Can you do this sort of programming in outlook?


0
Comment
Question by:andyhowse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 17942757
Greetings, andyhowse.

Yes.  It's simply a matter of making a connection to the database using ADO and then looping through the items in the folder and inserting their contents into the Access table.  What type of Outlook folder are you looking to export?  Do you need to see some sample code or were you just looking to see if it's possible?

Cheers!
0
 

Author Comment

by:andyhowse
ID: 17945060
Hi Bludevilfan and thanks for your reply.

I am trying to export a sub folder of inbobox on a regular basis - 4 times a day.

I would appreciate some sample code if possible.

Thanks in advance.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 17946065
andyhowse,

Here's a sample.  This snippet assumes that the table in the Access database is names Messages and is only exporting a subset of the fields in a message.  You can adjust that as needed.  Accessing the body of a message or any address field from code is going to trigger Outlook's built-in security.  The result will be a pop-up dialog-box warning that a program is accessing your mailbox and asking for your permission to allow it to continue.  There's no way to turn that off, but there are a couple of products that will help you work around it.  Here are links to both: http://www.contextmagic.com/express-clickyes/
http://www.dimastr.com/redemption

Sub ExportMessagesToAccess()
    Dim olkFolder As Outlook.Items, _
        olkMessage As Outlook.MailItem, _
        adoCon As Object, _
        strFields As String, _
        varValues As Variant
    'Add or remove fields as desired
    strFields = "Bcc,Body,BodyFormat,Categories,Cc,CreationTime,HTMLBody,Importance,SenderName,Sensitivity,Subject,SentTo"
    Set adoCon = CreateObject("ADODB.Connection")
    'Change the name and path of the database on the next line
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\eeTesting\Outlook.Mdb;Persist Security Info=False"
    Set olkFolder = Session.GetDefaultFolder(olFolderInbox).Items
    For Each olkMessage In olkFolder
        With olkMessage
            varValues = "'" & FixTextField(.BCC) & "'" _
                & ",'" & FixTextField(.Body) & "'" _
                & "," & .BodyFormat _
                & ",'" & FixTextField(.Categories) & "'" _
                & ",'" & FixTextField(.CC) & "'" _
                & ",'" & .CreationTime & "'" _
                & ",'" & FixTextField(.HTMLBody) & "'" _
                & "," & .Importance _
                & ",'" & FixTextField(.SenderName) & "'" _
                & "," & .Sensitivity _
                & ",'" & FixTextField(.Subject) & "'" _
                & ",'" & FixTextField(.To) & "'"
        End With
        'Change the table name "Messages" on the following line as needed
        adoCon.Execute "INSERT INTO Messages (" & strFields & ") VALUES(" & varValues & ")"
    Next
    adoCon.Close
    Set adoCon = Nothing
    Set olkMessage = Nothing
    Set olkFolder = Nothing
    MsgBox "All done!"
End Sub

Function FixTextField(varValue) As Variant
    FixTextField = Replace(Replace(varValue, Chr(34), Chr(34) & Chr(34)), "'", "''")
End Function
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:andyhowse
ID: 17951144
Thanks BlueDevilFan,
I was thinking of it the other way round - a script or macro to run in outlook to export the emails .... but this will be better - could you give me an idea of how I could then move the processed emails into a 'processed' folder?

I have accepted the answer as Grade A but I didn't get an option to give the points - or do you get the points automatically when I accept?

Many thanks

Andyhowse
0
 
LVL 76

Expert Comment

by:David Lee
ID: 17951967
You're welcome.  The points go to the person who posted the accepted answer.  Since you accepted my post I got the points.  

Moving the processed messages requires a bit of a change to the code.  We can't use a For Each ... Next loop to process the messages because moving the processed messages will cause the loop to skip messages.  When moving items out of the set of items being processed we have to start at the end of the list and work backwards.  

Sub ExportMessagesToAccess()
    Dim olkFolder As Outlook.Items, _
        olkMessage As Outlook.MailItem, _
        adoCon As Object, _
        strFields As String, _
        varValues As Variant, _
        intCounter As Integer
    'Add or remove fields as desired
    strFields = "Bcc,Body,BodyFormat,Categories,Cc,CreationTime,HTMLBody,Importance,SenderName,Sensitivity,Subject,SentTo"
    Set adoCon = CreateObject("ADODB.Connection")
    'Change the name and path of the database on the next line
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\eeTesting\Outlook.Mdb;Persist Security Info=False"
    Set olkFolder = Session.GetDefaultFolder(olFolderInbox).Items
    For intCounter = olkFolder.Count to 1 Step -1
        Set olkMessage = olkFolder.Item(intCounter)
        With olkMessage
            varValues = "'" & FixTextField(.BCC) & "'" _
                & ",'" & FixTextField(.Body) & "'" _
                & "," & .BodyFormat _
                & ",'" & FixTextField(.Categories) & "'" _
                & ",'" & FixTextField(.CC) & "'" _
                & ",'" & .CreationTime & "'" _
                & ",'" & FixTextField(.HTMLBody) & "'" _
                & "," & .Importance _
                & ",'" & FixTextField(.SenderName) & "'" _
                & "," & .Sensitivity _
                & ",'" & FixTextField(.Subject) & "'" _
                & ",'" & FixTextField(.To) & "'"
        End With
        'Change the table name "Messages" on the following line as needed
        adoCon.Execute "INSERT INTO Messages (" & strFields & ") VALUES(" & varValues & ")"
        'Replace "Some Folder Object" with a variable containing a folder object
        olkMessage.Move "Some Folder Object"
    Next
    adoCon.Close
    Set adoCon = Nothing
    Set olkMessage = Nothing
    Set olkFolder = Nothing
    MsgBox "All done!"
End Sub
0
 

Author Comment

by:andyhowse
ID: 17954020
Thanks you are a star!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question