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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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.
 
Outlook version
Outlook 2010 64 bit
Email Account
Office 365 Exchange
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
 
Outlook version
Outlook 2010 64 bit
Email Account
Office 365 Exchange
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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
 
Outlook version
Outlook 2010 64 bit
Email Account
Office 365 Exchange
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
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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.
Thread starter Similar threads Forum Replies Date
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
C Can't create Outlook data file Outlook Wishlist 0
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
L Create Custom Form Using Outlook 0
Diane Poremsky Create a Task when a Message is Flagged New Slipstick.com Articles 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
Diane Poremsky Create a custom field for Outlook messages New Slipstick.com Articles 0
Mark White vba to create a shared mailbox folder Outlook VBA and Custom Forms 3
Diane Poremsky Automatically create a task when sending a message New Slipstick.com Articles 0
Diane Poremsky Create a Custom Numbering Field for Outlook messages New Slipstick.com Articles 0
Diane Poremsky Create a Series of Tasks using VBA New Slipstick.com Articles 0
J create .pst data file for new gmail account Using Outlook.com accounts in Outlook 1
Diane Poremsky Create an Organizational Forms Library New Slipstick.com Articles 0
Diane Poremsky Create an Organizational Forms Library in Exchange 2013 or Office 365 New Slipstick.com Articles 0
Diane Poremsky Manually create a POP3 account in Outlook 2007 New Slipstick.com Articles 0
T Create Rule For Secondary E-Mail Address In Outlook 2016 Using Outlook 4
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
Diane Poremsky Create Appointments Using Spreadsheet Data New Slipstick.com Articles 0
A VBA to create meeting from template from a time slot selected in someone's calendar Outlook VBA and Custom Forms 5
G Create an Appointment at the Contact's Address From Email Outlook VBA and Custom Forms 0
Diane Poremsky Use VBA to create an Outlook Search Folder for Sender New Slipstick.com Articles 0
Diane Poremsky Create a Task from an Email using a Rule New Slipstick.com Articles 0
Diane Poremsky Create Task or Appointment and Insert Selected Text New Slipstick.com Articles 0
M Automatically create event in calendar when task is created Outlook VBA and Custom Forms 1
Diane Poremsky Create Appointment From Email Automatically New Slipstick.com Articles 0
Similar threads


















































Top