Create Printout from Multiple Shared Calendars

Status
Not open for further replies.
Outlook version
Outlook 2010 64 bit
Email Account
Office 365 Exchange
Hi Diane,

I've been reading your articles for the past few days and I find you to be brilliant.

I have a problem in my company...

We have about 30 shared conference room calendars.
I need to be able to create a print out of all of the events on a particular day filtered by location.

From your articles it looks as though my best bet might be to copy all of the entries for the day from each calendar to a calendar designed for exporting to a CSV file. Then I can use a mail merge to print the results.

We use Office 365 Exchange Online as our server.

I can't seem to figure out how to specify the folder that I'm copying the events from especially since the "owner" of each of the shared calendars is the same.


Dim NS As Outlook.NameSpace
Dim objOwner As Outlook.Recipient
Set NS = Application.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("maryc")
objOwner.Resolve
If objOwner.Resolved Then
'MsgBox objOwner.Name
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If

Can you advise?

Jeanne (I am not worthy) Goodman
 
Are the calendars all in one mailbox? The "owner" refers to the mailbox alias, not other users who are listed as owners. You need to get the mailbox name then it's calendar.

Depending on how they are in the profile, this might work as is - Combine and Print Multiple Outlook Calendars - i don't have code handy that uses the shared folder code.
 
You amaze me!

This is the line that triggers the OnError for shared folders.

calName = CalFolder.Parent.Name

How can I determine the parent when it's a shared folder?

Best,

Jeanne (Humbled by your help) Goodman
 
Hi again,

I figured it out! Our mailbox names are Replace(objNavFolder," ","-") so using that it just WORKS!

Onto figuring out the macro to export. I'm so close I can taste it.

Jeanne
 
This is the line that triggers the OnError for shared folders.

calName = CalFolder.Parent.Name

How can I determine the parent when it's a shared folder?

That is in the CopyAppttoPrint macro? That sets the category name - you don't need to use it.
 
This is my version that works with the default calendar in mailboxes, shared mailboxes or shared calendars (but not secondary calendars in a mailbox) . It uses the folder name as the category (and in the case of mailboxes, adds the parent display name).

Code:
Dim CalFolder As Outlook.Folder
Dim printCal As Outlook.Folder
Dim nameFolder

' Run this macro
Sub PrintCalendarsAsOne()
Dim objPane As Outlook.NavigationPane
Dim objModule As Outlook.CalendarModule
Dim objGroup As Outlook.NavigationGroup
Dim objNavFolder As Outlook.NavigationFolder
Dim objCalendar As Folder
Dim objFolder As Folder

Dim i As Integer
Dim g As Integer

On Error Resume Next

Set objCalendar = Session.GetDefaultFolder(olFolderCalendar)
Set printCal = objCalendar.Folders("Print")
printCal.Delete
Set printCal = objCalendar.Folders.Add("Print")

Set Application.ActiveExplorer.CurrentFolder = objCalendar
DoEvents

Set objPane = Application.ActiveExplorer.NavigationPane
Set objModule = objPane.Modules.GetNavigationModule(olModuleCalendar)

With objModule.NavigationGroups

For g = 1 To .Count

Set objGroup = .Item(g)

For i = 1 To objGroup.NavigationFolders.Count
Set objNavFolder = objGroup.NavigationFolders.Item(i)

If objNavFolder.IsSelected = True Then

'run macro to copy appt
Set CalFolder = objNavFolder.Folder
Set nameFolder = objNavFolder

Dim NS As Outlook.NameSpace
Dim objOwner As Outlook.Recipient
Set NS = Application.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient(nameFolder)
objOwner.Resolve
If objOwner.Resolved Then
Set CalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If

CopyAppttoPrint

End If
Next i
Next g
End With


Set objPane = Nothing
Set objModule = Nothing
Set objGroup = Nothing
Set objNavFolder = Nothing
Set objCalendar = Nothing
Set objFolder = Nothing
End Sub


Private Sub CopyAppttoPrint()

Dim calItems As Outlook.Items
Dim ResItems As Outlook.Items
Dim sFilter As String
Dim iNumRestricted As Integer
Dim itm, newAppt As Object

Set calItems = CalFolder.Items

If CalFolder = printCal Then
Exit Sub
End If

' Sort all of the appointments based on the start time
calItems.Sort "[Start]"
calItems.IncludeRecurrences = True

Debug.Print "3: " & nameFolder

On Error Resume Next
StrName = " - " & CalFolder.Parent.Name

calName = nameFolder & StrName
' to use category named for account & calendar name
' calName = CalFolder.Parent.Name & "-" & CalFolder.Name

'create the filter - this copies appointments today to 3 days from now
sFilter = "[Start] >= '" & Date - 2 & "'" & " And [Start] < '" & Date + 3 & "'"

' Apply the filter
Set ResItems = calItems.Restrict(sFilter)

iNumRestricted = 0

'Loop through the items in the collection.
For Each itm In ResItems
iNumRestricted = iNumRestricted + 1

Set newAppt = printCal.Items.Add(olAppointmentItem)
With newAppt
.Subject = itm.Subject
.Start = itm.Start
.End = itm.End
.ReminderSet = False
.Categories = calName
.Save
End With

Next
' Display the actual number of appointments created
Debug.Print calName & " " & (iNumRestricted & " appointments were created")

Set itm = Nothing
Set newAppt = Nothing
Set ResItems = Nothing
Set calItems = Nothing
Set CalFolder = Nothing

End Sub
 
Hi Diane,

I'm so close. I really tried to figure it out on my own, but I'm having problems with one final part... I'm having trouble figuring out how to tell Outlook to only copy values from the "Print" folder.

' get the values from outlook
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection

Set olItem = obj​

I saw your article: Working with VBA and non-default Outlook Folders but when I tried:

Set oNamespace = Application.GetNamespace("MAPI")
Set Items = Session.GetDefaultFolder(olFolderCalendar).Folders("Print").Items
Set Selection = Items
...​

Everything comes up empty.

Jeanne (Feeling Incompetent) Goodman
 
this works with the selected items -
Code:
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
  For Each obj In Selection

    Set olItem = obj

to change it to work with all items in the folder we'll swipe code from Working with All Items in a Folder or Selected Items

Code:
    Set objFolder = Session.GetDefaultFolder(olFolderCalendar).Folders("Print")
    Set objItems = objFolder.Items
    For Each olItem In objItems
 
' code to copy to excel
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
S Create Outlook Task from Template and append Body with Email Body Outlook VBA and Custom Forms 4
B Modify VBA to create a RULE to block multiple messages Outlook VBA and Custom Forms 0
J Want to create a button on the nav bar (module add-in) to run code Outlook VBA and Custom Forms 2
B How to create a button that sorts and selects the most recent message with ONE click Using Outlook 2
J PSA: How to create custom keyboard shortcut for "Paste Unformatted Text" in Outlook on Windows Outlook VBA and Custom Forms 1
W Create a Quick Step or VBA to SAVE AS PDF in G:|Data|Client File Outlook VBA and Custom Forms 1
Wotme create email only data file Using Outlook 1
S Outlook 365 Help me create a Macro to make some received emails into tasks? Outlook VBA and Custom Forms 1
J How to create a drop down user defined field that will appear on an inbox view Outlook VBA and Custom Forms 8
Commodore Any way to create "from-only" account on Outlook 2021? Using Outlook 1
L Capture email addresses and create a comma separated list Outlook VBA and Custom Forms 5
N Can't create NEW GROUP and add/remove a member from existing Group in Outlook Using Outlook 1
NVDon Create new Move To Folder list Outlook VBA and Custom Forms 0
C Create Meeting With Custom Form Outlook VBA and Custom Forms 2
D Create advanced search (email) via VBA with LONG QUERY (>1024 char) Outlook VBA and Custom Forms 2
G Create ordinal numbers for birthday Outlook VBA and Custom Forms 2
O Outlook 365 - How to create / copy a new contact from an existing one? Using Outlook 5
D Create new email from the received Email Body with attachment Outlook VBA and Custom Forms 10
A How to create fixed signatures for aliases that process through GMAIL? Outlook VBA and Custom Forms 0
P Can I create a Rule that sends me an email when I get a Task? Using Outlook 2
M How create a Rule to filter sender's email with more that one @ sign Using Outlook 1
B Can I create a local PST file for SPAM on a drive that is usually disconnected? Using Outlook 3
Chiba Create an appointment for all the members Outlook VBA and Custom Forms 1
S Create a clickable custom column field Outlook VBA and Custom Forms 0
O Create a custom contact form - questions before messing things up... Outlook VBA and Custom Forms 4
L automaticaly create a teams meeting with a sync Using Outlook 0
D Can Exchange Admin Center create a pst for users email/contacts/calendar? Exchange Server Administration 0
S Create A Search Folder That Looks For Message Class? Outlook VBA and Custom Forms 0
F How to create phone number as links in notes of Contacts Using Outlook 2
Nessa Can't create new appointment Using Outlook 1
A Create date folder and move messages daily Outlook VBA and Custom Forms 1
C Create new Message with shared contacts & BCC'ing recipients Outlook VBA and Custom Forms 0
O Multiple email accounts - hesitate to create a new profile Using Outlook 3
G Can't create Folder Groups in Outlook 2013 Using Outlook 0
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
F Delete/create/reset Exchange mailbox on Outlook.com Using Outlook.com accounts in Outlook 3
R Can not create folder to store specific emails in in Outlook for Mac Using Outlook 1
W Create Search Folder excluding Specific Email Addresses Using Outlook 5
A Outlook macro to create search folder with mail categories as criteria Outlook VBA and Custom Forms 3
K VBA BeforeItemMove event create rule to always move to its folder. Outlook VBA and Custom Forms 4
JackBlack What tools do you use to create the signature for email? Using Outlook 3
Rupert Dragwater How to create a new email with @outlook.com Using Outlook.com accounts in Outlook 32
F Should a new email account also create new contacts Using Outlook 2
D create an html table in outlook custom form 2010 using vba in MsAccess Outlook VBA and Custom Forms 7
R Outlook add-in to create new contact from an email. Using Outlook 0
Tanja Östrand Outlook 2016 - Create Macro button to add text in Subject Outlook VBA and Custom Forms 1
Q Script to create a pst file for Archiving Using Outlook 1
Jennifer Murphy Can I create a Rule with Or'd conditions? Using Outlook 1
D Outlook macros to create meeting on shared calendar Outlook VBA and Custom Forms 10
G How do I create a custom pick list in VB for an outlook automated email? Outlook VBA and Custom Forms 1

Similar threads

Back
Top