ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Excel With Outlook (https://www.excelbanter.com/excel-programming/300760-excel-outlook.html)

alihussain19[_6_]

Excel With Outlook
 
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").GetDef aultFolder(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;

--
Message posted from http://www.ExcelForum.com


Dick Kusleika[_3_]

Excel With Outlook
 
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.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"alihussain19 " wrote in
message ...
Hello,
I know it's not just an Excel issue but I am writing a Macro to get me
all the appointements I have in Outlook and import them to an Excel
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").GetDef aultFolder(olFolderCalenda
r)
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-mail
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.yyyy
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 in
Addition to the Attendees (I need a loop but how can I loop and what
count I need to use)
Any Help will be appreciated...
Thanks;)


---
Message posted from http://www.ExcelForum.com/





All times are GMT +1. The time now is 09:09 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com