I am trying to extract information from outlook, was able to do that with the following
Sub ListAppointments()
Dim olApp As Object
Dim olNS As Object
Dim olFolder As Object
Dim olApt As Object
Dim NextRow As Long
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNamespace("MAPI")
Set olFolder = olNS.GetDefaultFolder(9) 'olFolderCalendar
Range("A1:b1").Value = Array("Start", "Subject", "Location")
NextRow = 2
For Each olApt In olFolder.Items
Cells(NextRow, "a").Value = olApt.Start
With Selection
.NumberFormat = "MMMM dd"
End With
Cells(NextRow, "b").Value = olApt.Subject
'Cells(NextRow, "C").Value = olApt.End
NextRow = NextRow + 1
Next olApt
I am also able to create a calendar using the following
Sub CreateCalendar()
Dim lMonth As Long
Dim strMonth As String
Dim rStart As Range
Dim strAddress As String
Dim rCell As Range
Dim lDays As Long
Dim dDate As Date
'Add new sheet and format
Worksheets.Add
ActiveWindow.DisplayGridlines = False
With Cells
.ColumnWidth = 6#
.Font.Size = 12
End With
'Create the Month headings
For lMonth = 1 To 4
Select Case lMonth
Case 1
strMonth = "January"
Set rStart = Range("A1")
Case 2
strMonth = "April"
Set rStart = Range("A8")
Case 3
strMonth = "July"
Set rStart = Range("A15")
Case 4
strMonth = "October"
Set rStart = Range("A22")
End Select
'Merge, AutoFill and align months
With rStart
.Value = strMonth
.HorizontalAlignment = xlCenter
.Interior.ColorIndex = 6
.Font.Bold = True
With .Range("A1:G1")
.Merge
.BorderAround LineStyle:=xlContinuous
End With
.Range("A1:G1").AutoFill Destination:=.Range("A1:U1")
End With
Next lMonth
'Pass ranges for months
For lMonth = 1 To 12
strAddress = Choose(lMonth, "A2:G7", "H2:N7", "O2:U7", _
"A9:G14", "H9:N14", "O9:U14", _
"A16:G21", "H16:N21", "O16:U21", _
"A23:G28", "H23:N28", "O23:U28")
lDays = 0
Range(strAddress).BorderAround LineStyle:=xlContinuous
'Add dates to month range and format
For Each rCell In Range(strAddress)
lDays = lDays + 1
dDate = DateSerial(Year(Date), lMonth, lDays)
If Month(dDate) = lMonth Then ' It's a valid date
With rCell
.Value = dDate
.NumberFormat = "ddd dd"
if (exists InStr(1, "Sat", "Sun") = 1) then rCell.Value = ""
GoTo 20
End With
End If
20
Next rCell
Next lMonth
'add con formatting
With Range("A1:U28")
.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=TODAY()"
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions(1).Interior.ColorIndex = 1
End With
Here is what I am looking to accomplish for a rolling 60 day period.
| July |
| 01 |
02 |
03 |
04 |
05 |
| 08 sample appt |
09 |
10 another appt |
11 |
12 |
| 15 |
16 appt made |
17 |
18 |
19 |
| 22 |
23 |
24 |
25 |
26 |
| 29 |
30 |
31 |
|
|
Can anyone please help