Excel With Outlook

A

alihussain19

Hello,
I know it's not just an Excel issue but I am writing a Macro to get m
all the appointements I have in Outlook and import them to an Exce
Sheet.

Sub ListAllItemsInInbox()
Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
Application.ScreenUpdating = False
Workbooks.Add ' create a new workbook
' add headings
Cells(1, 1).Formula = "Subject"
Cells(1, 2).Formula = "Date"
Cells(1, 3).Formula = "With Who"
Cells(1, 4).Formula = "Where"
With Range("A1:D1").Font
.Bold = True
.Size = 14
End With
Application.Calculation = xlCalculationManual
Set OLF = GetObject("", _

"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)
EmailItemCount = OLF.Items.Count
i = 0: EmailCount = 0
' read e-mail information
While i < EmailItemCount
i = i + 1
If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mai
messages " & _
Format(i / EmailItemCount, "0%") & "..."
With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.StartTime
"dd.mm.yyyy hh:mm")
Cells(EmailCount + 1, 3).Formula = .EndTime
Cells(EmailCount + 1, 4).Formula = .Attendees
End With
Wend
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
Columns("A:D").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False
End Sub

I get Debug error in the following lines
Cells(EmailCount + 1, 2).Formula = Format(.StartTime, "dd.mm.yyy
hh:mm")
Cells(EmailCount + 1, 3).Formula = .EndTime
Cells(EmailCount + 1, 4).Formula = .Attendees

Any Idea how to Fix it!!!. Trying to get the Start and End time i
Addition to the Attendees (I need a loop but how can I loop and wha
count I need to use)
Any Help will be appreciated...
Thanks;
 
D

Dick Kusleika

Try this

Dim sRecips As String
Dim oRecip As Recipient

With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.Start, "dd.mm.yyyy
hh:mm")
Cells(EmailCount + 1, 3).Formula = .End
For Each oRecip In OLF.Items(i).Recipients
sRecips = sRecips & oRecip.Name & ","
Next oRecip
Cells(EmailCount + 1, 4).Formula = sRecips
sRecips = ""
End With

StartTime and EndTime are not properties of AppointmentItem, they should be
Start and End. You can loop through the Recipients collection to get the
Attendees. There is no Attendees property.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top