Error when exporting Sent Mail to Excel

lneidorf

New Member
OS Version(s)
  1. Windows
Outlook version
Outlook 365 64 bit
Email Account
Exchange Server
Operating system::    Windows 11
Outlook version:     Classic O365 v2401
Email type or host:    O365

Hi there.

I have some code designed to loop through my Sent Mail folder and export certain fields to a spreadsheet. It errors out, with an "Object doesn't support this property or method" error. I can tell by the entries on the spreadsheet that it's erroring out when it encounters Calendar Invite acceptances.

My code appears below; I'd be grateful for an assist.

Many thanks!
Code:
Option Explicit

Sub List_Email_Info()


Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Dim arrHeader As Variant
Dim olNS As NameSpace
Dim olInboxFolder As MAPIFolder
Dim olItems As Items
Dim olMailItem As mailItem
Dim dtStart As Date
Dim oItem As Object
Dim oMail As mailItem
Dim txtInputDate As String
Dim dtEmailDate As String
Dim c As Range
Dim firstAddress As String
Dim strCopyToAddress As String
Dim strCopyFromAddress As String
Dim strFormula As String



txtInputDate = "1/01/2024"

arrHeader = Array("PM", "Date", "PID", "SONumber", "Project Name", "Hours", "Billable", "Description Category", "Standard Description", "Description Text")

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add


Set olNS = GetNamespace("MAPI")
''Set olInboxFolder = olNS.GetDefaultFolder(olFolderInbox)
Set olInboxFolder = olNS.GetDefaultFolder(olFolderSentMail)

Set olItems = olInboxFolder.Items


i = 1


On Error GoTo ERRHANDLER:


xlWB.Worksheets(1).Range("A1").Resize(1, UBound(arrHeader) + 1).Value = arrHeader


For Each oItem In Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Items
    If TypeName(oItem) = "MailItem" Then
        Set oMail = oItem
            
            olItems.Sort "[CreationTime]", True
            
            dtEmailDate = Format(olItems(i).CreationTime, "MM-DD-YYYY")
    
            
            If olItems(i).CreationTime >= CDate(txtInputDate) Then
            
          
            'xlWB.Worksheets(1).Cells(i + 1, "A").Value = olItems(i).CreationTime
            xlWB.Worksheets(1).Cells(i + 1, "A").Value = olItems(i).SenderName
            xlWB.Worksheets(1).Cells(i + 1, "B").Value = Format(olItems(i).CreationTime, "MM-DD-YYYY")
            xlWB.Worksheets(1).Cells(i + 1, "C").Value = ""
            xlWB.Worksheets(1).Cells(i + 1, "D").Value = ""
            xlWB.Worksheets(1).Cells(i + 1, "E").Value = olItems(i).TaskSubject
            xlWB.Worksheets(1).Cells(i + 1, "F").Value = ""
            xlWB.Worksheets(1).Cells(i + 1, "G").Value = ""
            xlWB.Worksheets(1).Cells(i + 1, "H").Value = "Review"
            xlWB.Worksheets(1).Cells(i + 1, "I").Value = "Other"
            xlWB.Worksheets(1).Cells(i + 1, "J").Value = olItems(i).TaskSubject
            '''xlWB.Worksheets(1).Cells(i + 1, "D").Value = olItems(i).ReceivedTime
            
            End If
            
            i = i + 1
        
    End If
    
Next oItem

''Find last number of column
Range("D" & Rows.Count).End(xlUp).Offset(1).Select

'''Range("D1:D2").Select
'''Selection.AutoFill Destination:=Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
'''Range(Selection, Selection.End(xlDown)).Select


''MsgBox ActiveCell.Address
strCopyToAddress = ActiveCell.Address

Range("D2").Value = "=MID(E2,FIND(75,E2,1),7)"
strFormula = Range("D2").Formula
''strCopyFromAddress = "=MID(E2,FIND(75,E2,1),7)"-+

Range("D2").Select

''MsgBox ActiveCell.Formula

Range("D2").Copy

''Range("D3").Select
Range("D3" & ":" & strCopyToAddress & "").Select
ActiveSheet.Paste

xlWB.Worksheets(1).Cells.EntireColumn.AutoFit

MsgBox "Export complete."

Set xlWB = Nothing
Set xlApp = Nothing

Set olItems = Nothing
Set olInboxFolder = Nothing
Set olNS = Nothing

ERRHANDLER:
If Err.Number <> 0 Then
    MsgBox Err.Description, Err.Number
    Debug.Print Err.Description, Err.Number
Else
    Exit Sub
End If   
Exit Sub
End Sub
 
>> If TypeName(oItem) = "MailItem" Then

That should kick it out. Try If TypeName(oItem) <> "MailItem" Then goto netxmsg to jump out of the loop.

Or use messageclass instead:

if oItem.messageclass ="ipm.note" then
 
>> If TypeName(oItem) = "MailItem" Then

That should kick it out. Try If TypeName(oItem) <> "MailItem" Then goto netxmsg to jump out of the loop.

Or use messageclass instead:

if oItem.messageclass ="ipm.note" then

Many thanks, Diane.

I'm a VBA novice, so I'm not sure where to put this. I assume I substitute it for:
Code:
If TypeName(oItem) = "MailItem" Then
I tried that and no dice. Assuming I need some sort of "Else IF" statement.

Thanks!
 
This
if oItem.messageclass ="ipm.note" then

would replace

If TypeName(oItem) = "MailItem" Then

Did it error or just not work?


Or... change the dim 's from mailitem to object - that should work with any items as long as it has the fields you need.
Dim olMailItem As object
Dim oMail As object
 
Alas no luck either way.
First approach errors out almost immediately.
Second errors out when it encounters that first Cal Invite acceptance. Same as before.
 
This
if oItem.messageclass ="ipm.note" then

would replace

If TypeName(oItem) = "MailItem" Then

Did it error or just not work?


Or... change the dim 's from mailitem to object - that should work with any items as long as it has the fields you need.
Dim olMailItem As object
Dim oMail As object
CORRECTION:
First approach technically completes, with no data populated except the headers and a couple lines of the formula {Range("D2").Value = "=MID(E2,FIND(75,E2,1),7)"}.
Second errors out when it encounters that first Cal Invite acceptance. Same as before.
 
I will test it.
 
Similar threads
Thread starter Title Forum Replies Date
J OLADD.FAE Error When Exporting Contacts Using Outlook 6
M Error Messages when exporting Outlook 2010 Contacts to a CSV (DOS) or Excel Using Outlook 2
H Error in values when exporting report to Excel BCM (Business Contact Manager) 1
G Error - "Customized functionality in this application will not work because the certificate" Outlook VBA and Custom Forms 0
G Outlook 365 My iCloud Outlook doesn’t work after reinstalling Microsoft365 on Windows 10 PC – now I get error message on contacts and calendar Using Outlook 1
V Outlook Error The Attempted operation Failed. An Object Could Not be found Outlook VBA and Custom Forms 0
S macro error 4605 Outlook VBA and Custom Forms 0
humility36 Cannot move emails to archive - 440 error Outlook VBA and Custom Forms 1
D.Moore Strange VBA error Outlook VBA and Custom Forms 4
T Event Error on non existent Event. Using Outlook 2
P now on office 365 but getting error messages about missing Outlook 2013 cache folders Using Outlook 2
W Outlook 365 I am getting the "Either there is no default mail client" error when I try to send an email on excel Office 365 Using Outlook 1
A Links in email getting error message about group policy Using Outlook 4
Aussie Outlook 365 Rule runs manually but returns the error code "an unexpected error has occurred" when incoming mail arrives Using Outlook 1
Cathy Rhone Mail merge error message Using Outlook 1
U Outlook 2019 VBA run-time error 424 Outlook VBA and Custom Forms 2
V Outlook error 500 Using Outlook 2
O Comma Separated Values.ADR and A file error has occurred in the translator Using Outlook 6
D We're sorry but outlook has run into an error Using Outlook 6
D Outlook 2016 Outlook Error Msg "The operation cannot be performed ..." How to Stop it Using Outlook 4
P Outlook 2013 All imported Mail Rules in error when imported into new profile Using Outlook 5
H Outlook 2019 Certificate error Using Outlook 2
V Date and/or time error in Outlook Form Outlook VBA and Custom Forms 0
A Run-time error '430' on certain emails when trying to set "Outlook.mailitem" as "ActiveExplorer.Selection.Item" Outlook VBA and Custom Forms 2
E Complite error on SaveAsFile method Outlook VBA and Custom Forms 2
I Error saving screenshots in a custom form in outlook 2016, outlook 365 - ok in outlook 2013, outlook 2010 Outlook VBA and Custom Forms 5
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
A Unable to save recurring Meeting to Documents folder due to error Using Outlook 2
M Compile error: User-defined type not defined Outlook VBA and Custom Forms 0
R Error when trying to forward current email item Outlook VBA and Custom Forms 7
M ERROR: None of your email accounts could send to this recipient Using Outlook 2
C Send/receive error 80040119 Using Outlook 2
W error with the permission for the file Outlook VBA and Custom Forms 0
L Outlook 2019 MAC sync error after working for 4 hours Using Outlook 1
A Run time error 424. object required in outlook 2013 Outlook VBA and Custom Forms 10
M error code 0x8DE00006 Using Outlook 1
M Desktop Version Of Outlook Generating Error Using Outlook 4
M Send/Receive error 0x800CCC0F Using Outlook 0
T Outlook 2016 CSV Translator Import Error Using Outlook 6
ManaarZakaria I'm afraid of this issue, cause of strange error Exchange Server Administration 2
P Suppress dialog box on email check error? Using Outlook 5
vodkasoda Object could not be found Error in Outlook 2007 Outlook VBA and Custom Forms 5
S VBA Macro - Run-time error '424': object required - Help Please Outlook VBA and Custom Forms 3
avant-guvnor Outlook.Application now produces error Outlook VBA and Custom Forms 5
P Run Time Error 91 when linking contact to task in VBA Outlook VBA and Custom Forms 1
N Error 0x80090326 when trying to setup IMAP account on Outlook.com Using Outlook.com accounts in Outlook 1
N Saving And Deleting Outlook Attachments with Unknown Error Message Outlook VBA and Custom Forms 1
Dennis Gaudenzi Your setup couldn't be started because of an unexpected error (mapi 0x80040604) Using Outlook 14
M Outlook 2013 fails to start -- missing WindowsCodecs.dll error Using Outlook 3
Steshelter ICloud Unexpected Error Using Outlook 5

Similar threads

Back
Top