Solved

Export Outlook 2000 folder to Excel file with Access VBA: how to include more than 3 fields.

Posted on 2009-05-07
13
326 Views
Last Modified: 2013-11-27
I have developed a code that exports an Outlook subfolder, with 4 fields/properties for each message.
Unfortunately it creates the first 10 rows in the .xls spreadsheet and then it hangs on "myXl.Activesheet.Cells(j + 1, 3) = olkItem.SenderName" line, saying that properties ".SenderName" and ".SenderEmailAddress" are not supported.
Can you help please? Thanx.
Public Function EsportaRicevuteOutlook()
 
    Dim olkApp As Object, olkNS As Object, olkFolder As Object, myXl As Object
    Dim olkItems As Object, olkItem As Object
    Dim i As Integer, j As Integer
    Dim olkProp As Outlook.ItemProperty
    Set olkApp = CreateObject("Outlook.application")
    Set myXl = CreateObject("Excel.application")
    Set olkNS = olkApp.GetNamespace("MAPI")
    Set olkFolder = olkNS.GetDefaultFolder(6).Folders("Ricevute messaggi")
    
    Set olkItems = olkFolder.Items
    i = olkItems.Count
    myXl.Visible = True
    myXl.Workbooks.Add
    myXl.Sheets("Foglio2").Select
    myXl.Activesheet.Delete
    myXl.Sheets("Foglio3").Select
    myXl.Activesheet.Delete
    myXl.Sheets("Foglio1").Select
    myXl.Sheets("Foglio1").Name = "Ricevute"
 
    myXl.Activesheet.Cells.Value = "Oggetto"
    myXl.Activesheet.Cells.Value = "Testo"
    myXl.Activesheet.Cells.Value = "Mittente"
    myXl.Activesheet.Cells.Value = "Email"
    
    j = 0
    For Each olkItem In olkItems
        myXl.Activesheet.Cells(j + 1, 1) = olkItem.Subject
        myXl.Activesheet.Cells(j + 1, 2) = olkItem.Body
        myXl.Activesheet.Cells(j + 1, 3) = olkItem.SenderName
        myXl.Activesheet.Cells(j + 1, 4) = olkItem.SenderEmailAddress
        j = j + 1
    Next olkItem
    
    myXl.Activesheet.Columns("A:A").EntireColumn.AutoFit
    myXl.Activesheet.Columns("B:B").EntireColumn.AutoFit
    myXl.Activesheet.Columns("C:C").EntireColumn.AutoFit
    myXl.Activesheet.Columns("D:D").EntireColumn.AutoFit
    
    myXl.ActiveWorkbook.SaveAs "C:\Ricevute.xls"
    myXl.ActiveWorkbook.Close
    myXl.Quit
 
End Function

Open in new window

0
Comment
Question by:Sar1973
[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
  • 7
  • 6
13 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 24332574
What version of Outlook is the code using?

Those properties were new to 2003.
0
 
LVL 9

Author Comment

by:Sar1973
ID: 24333771
I am using Access 2000 and Outlook 2003. Do you think that this is the cause of my trouble?
Notice that the code hangs after 10 lines, which I get correctly in my worksheet.
0
 
LVL 34

Expert Comment

by:Norie
ID: 24334275
Does all the code work without those 2 lines?

What's version of Microsoft Outlook... is listed when you goto Tools>References...?

Also what references are checked?

Is this code being used on more than 1 computer/setup? If it is does it fail on some and not others?

Where is the code being run from?
Public Function EsportaRicevuteOutlook()
 
Dim olkApp As Object
Dim olkNS As Object
Dim olkFolder As Object
Dim olkItems As Object
Dim olkItem As Object
Dim olkProp As Outlook.ItemProperty
 
Dim myXl As Object
Dim myWB As Object
Dim myWS As Object
Dim I As Long
Dim J As Long
    
    
    Set olkApp = CreateObject("Outlook.application")
    Set myXl = CreateObject("Excel.application")
    Set olkNS = olkApp.GetNamespace("MAPI")
    Set olkFolder = olkNS.GetDefaultFolder(6).Folders("Ricevute messaggi")
    
    Set olkItems = olkFolder.Items
    
    I = olkItems.Count
    
    myXl.Visible = True
    
    Set myWB = myXl.Workbooks.Add(-4167)
    
    Set myWS = myWB.Worksheets(1)
    
    myWS.Name = "Ricevute"
    
    ' this is strange - why are you filling all the cells in the worksheet
    ' with one value, then repeating for another value and so on?
 
    myWS.Cells.Value = "Oggetto"
    myWS.Cells.Value = "Testo"
    myWS.Cells.Value = "Mittente"
    myWS.Cells.Value = "Email"
    
    For Each olkItem In olkItems
        myWS.Cells(J + 1, 1) = olkItem.Subject
        myWS.Cells(J + 1, 2) = olkItem.Body
        myWS.Cells(J + 1, 3) = olkItem.SenderName
        myWS.Cells(J + 1, 4) = olkItem.SenderEmailAddress
        J = J + 1
    Next olkItem
    
    myWS.Range("A:D").EntireColumn.AutoFit
    
    myXl.ActiveWorkbook.SaveAs "C:\Ricevute.xls"
    myXl.ActiveWorkbook.Close
    
    myXl.Quit
 
End Function

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 9

Author Comment

by:Sar1973
ID: 24334600
1. "Does all the code work without those 2 lines?" Sorry, there was a mistake: the code below writes correctly the header line
2. "What's version of Microsoft Outlook... is listed when you goto Tools>References...?" Outlook 2003 but, as I told you, Access 2000
3. "Also what references are checked?" What do you mean by checked references?
4. "Is this code being used on more than 1 computer/setup? If it is does it fail on some and not others?" No, no.
5. "Where is the code being run from?" An Access 2000 form, which launches the function with a button. With the new code below it hangs exactly on row 10 (9th item), column 3 (sender name) saying that "variable With block not set", as if the code would need a With method.
Public Function EsportaRicevuteOutlook()
    
    Dim olkApp As Object, olkNS As Object, olkFolder As Object, myXl As Object
    Dim olkItems As Object, olkItem As Object
    Dim i As Integer, j As Integer
    Dim olkProp As Outlook.ItemProperty
    Set olkApp = CreateObject("Outlook.application")
    Set myXl = CreateObject("Excel.application")
    Set olkNS = olkApp.GetNamespace("MAPI")
    Set olkFolder = olkNS.GetDefaultFolder(6).Folders("Ricevute messaggi")
    
    Set olkItems = olkFolder.Items
    i = olkItems.Count
    myXl.Visible = True
    myXl.Workbooks.Add
    myXl.Sheets("Foglio2").Select
    myXl.Activesheet.Delete
    myXl.Sheets("Foglio3").Select
    myXl.Activesheet.Delete
    myXl.Sheets("Foglio1").Select
    myXl.Sheets("Foglio1").Name = "Ricevute"
    
    myXl.Activesheet.Cells(1, 1).Value = "Oggetto"
    myXl.Activesheet.Cells(1, 2).Value = "Testo"
    myXl.Activesheet.Cells(1, 3).Value = "Mittente"
    myXl.Activesheet.Cells(1, 4).Value = "Email"
    
    j = 2
    For Each olkItem In olkItems
        myXl.Activesheet.Cells(j, 1) = olkItem.Subject
        myXl.Activesheet.Cells(j, 2) = olkItem.Body
        myXl.Activesheet.Cells(j, 3) = olkItem.SenderName
        myXl.Activesheet.Cells(j, 4) = olkItem.SenderEmailAddress
        j = j + 1
    Next olkItem
    
    myXl.Activesheet.Columns("A:A").EntireColumn.AutoFit
    myXl.Activesheet.Columns("B:B").EntireColumn.AutoFit
    myXl.Activesheet.Columns("C:C").EntireColumn.AutoFit
    myXl.Activesheet.Columns("D:D").EntireColumn.AutoFit
    
    myXl.ActiveWorkbook.SaveAs "C:\Ricevute MKT.xls"
    myXl.ActiveWorkbook.Close
    myXl.Quit
 
End Function

Open in new window

0
 
LVL 34

Expert Comment

by:Norie
ID: 24334969
1 I was really asking if the code worked without the 2 lines of code that appear to be causing the problem

2 The important thing is the version of Outlook, Access doesn't actually have any relevance here. The code could have been written in Excel VBA, Outlook VBA etc.

3 References that are checked when you goto Tools>References..., ie those with a tick next to them.

5 What do you mean exactly? Do you mean the code works for the first 9 Outlook items but fails on the 10th?

If that's the case the problem is most likely because that item doesn't have SenderName or SenderEmailAddress.

By the way do you actually have a reference for Outlook checked?

The reason I ask is because of this.

Dim olkApp As Object, olkNS As Object, olkFolder As Object, myXl As Object
Dim olkItems As Object, olkItem As Object
Dim olkProp As Outlook.ItemProperty

Why are you declaring most of these as 'Object' but olkProp as 'ItemProperty'?
0
 
LVL 9

Author Comment

by:Sar1973
ID: 24335406
I don't know, really: if I must say I have took codes around and arranged them together.
That's also why I have posted the question: to have a proper code.
I confirm my previous answers  and tell you:
3. I haven't any check on "mail format": the messages are sent in HTML, Word is not the predefined editor
5. exactly
0
 
LVL 34

Expert Comment

by:Norie
ID: 24336330
3 I have no idea what you mean - I never mentioned 'mail format' anywhere. I'm referring to the references in the VBE when you goto Tools>References...

5 This means that the problem most likely lies with the items not the code. I'm afraid I can't test this out as I don't use Outlook any more.

    What happens when you either omit or comment out the 2 lines causing the problem?
0
 
LVL 9

Author Comment

by:Sar1973
ID: 24393548
If I set only 2 properties to be exported, it works. That's the reason of my question.
0
 
LVL 34

Expert Comment

by:Norie
ID: 24394093
Then the problem probably lies with the data, ie the Outlook items, not the code.

Is there anything 'different' about the item(s) the code fails on?
0
 
LVL 9

Author Comment

by:Sar1973
ID: 24394608
No, that's also why I posted the question: the items are all the same, without empty fields or other.
I have noticed instead that the code extracts first items which are in the middle of the folder, sorted by date.
0
 
LVL 34

Accepted Solution

by:
Norie earned 250 total points
ID: 24394697
Sorry I can't make any further suggestions.

You insist there is no problems with the items, are you 100% sure about that?

How are you actually checking that?
0
 
LVL 9

Author Comment

by:Sar1973
ID: 24394765
I have scrolled the whole folder and I have also took a look to the .xls file exported directly from Outlook: each item has a precise value/content in every field.
0
 
LVL 9

Author Closing Comment

by:Sar1973
ID: 31579239
-
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

734 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