This code sample uses a default Outlook folder:
Sub Whatever()
Dim Ns As Outlook.NameSpace
Set Ns = Application.GetNamespace("MAPI")
'use the default folder
Set Items = Ns.GetDefaultFolder(olFolderCalendar).Items
'do whatever
End Sub
To use a folder at the same level as the Default folders (such as Calendar, Inbox etc), use this in place of Set Items = Ns.GetDefaultFolder(olFolderCalendar).Items, where SharedCal is the folder name:
Set Items = Session.GetDefaultFolder(olFolderCalendar).Parent._
Folders("SharedCal").Items
When the folder is a subfolder under the default Calendar folder, use this instead:
Set Items = Session.GetDefaultFolder(olFolderCalendar)._
Folders("SharedCal").Items
To use the currently selected folder, you'll need to use:
Set Items = Application.ActiveExplorer.CurrentFolder.Items
Use a folder in another pst or mailbox
To use a specific folder in another data file in the profile, you need to use a function. Call the function in your macro in this manner, where GetFolderPath is the function name:
Set Items = GetFolderPath("New PST\Test Cal").Items
After adding the function to ThisOutlookSession:
Function GetFolderPath(ByVal FolderPath As String) As Outlook.Folder
Dim oFolder As Outlook.Folder
Dim FoldersArray As Variant
Dim i As Integer
On Error GoTo GetFolderPath_Error
If Left(FolderPath, 2) = "\\" Then
FolderPath = Right(FolderPath, Len(FolderPath) - 2)
End If
'Convert folderpath to array
FoldersArray = Split(FolderPath, "\")
Set oFolder = Application.Session.Folders.item(FoldersArray(0))
If Not oFolder Is Nothing Then
For i = 1 To UBound(FoldersArray, 1)
Dim SubFolders As Outlook.Folders
Set SubFolders = oFolder.Folders
Set oFolder = SubFolders.item(FoldersArray(i))
If oFolder Is Nothing Then
Set GetFolderPath = Nothing
End If
Next
End If
'Return the oFolder
Set GetFolderPath = oFolder
Exit Function
GetFolderPath_Error:
Set GetFolderPath = Nothing
Exit Function
End Function
Use a shared folder (Exchange mailbox)
To access a shared folder in another user's Exchange server mailbox, you need to use GetSharedDefaultFolder to reference the mailbox, after resolving the address to the folder. If the shared folder was opened from a sharing invitation (or Open Other User's folder command), you may need to use the method in the next section.
You can use the mailbox owner's display name, alias, or email address when resolving the recipient.
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
To add an item to a folder in a shared mailbox, use Items.add:
Set objAppt = newCalFolder.Items.Add(olAppointmentItem)
Default Folder Names
The following are the default folder names to use when referencing Outlook's default folders.
| Name | Value | Description |
|---|---|---|
| olFolderCalendar | 9 | Calendar folder |
| olFolderContacts | 10 | Contacts folder |
| olFolderDeletedItems | 3 | Deleted Items folder |
| olFolderDrafts | 16 | Drafts folder |
| olFolderInbox | 6 | Inbox folder |
| olFolderJournal | 11 | Journal folder |
| olFolderJunk | 23 | Junk E-Mail folder |
| olFolderNotes | 12 | Notes folder |
| olFolderOutbox | 4 | Outbox folder |
| olFolderSentMail | 5 | Sent Mail folder |
| olFolderSuggestedContacts | 30 | Suggested Contacts folder |
| olFolderTasks | 13 | Tasks folder |
| olFolderToDo | 28 | To Do folder |
| olPublicFoldersAllPublicFolders | 18 | All Public Folders folder in Exchange Public Folders store (Exchange only) |
| olFolderRssFeeds | 25 | RSS Feeds folder |
Access a Folder Opened from a Sharing Invitation
In order to access a folder received from a folder Invitation (or using Open Other User's folder command), you need find the folder on the navigation pane. This will work with default folders opened using Open Other User's Folder command or any folder shared from a sharing invitation.
Dim objPane As Outlook.NavigationPane
Dim objModule As Outlook.CalendarModule
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)
' need to use the calendar name as displayed in calendar list
If objNavFolder = "Philip Poremsky - Test" Then
Set CalFolder = objNavFolder.Folder
End If
Next
Next
End With
Hello Diane,
I am new to scripting & VBA and so far I have perused the innerweb's and have come across your website. Great article and resources. This is greatly appreciated by us noobs!
No to the nitty-gritty. I am looking for a basic VBA for Outlook that will allow me to view the current sent received items of selected folders in Outlook & export to excel.
Currently I have a script that will give me a total count on selected folder, but I have to run it one folder at a time and with a screen pop that tells me the count. However, I would like to Run the VBA script that will allow users to select a folder or set of folders with possible date and time selections to get the count of sent a received emails to export to Excel.
Is this possible and would you be willing to lend a helping hand and modify my existing code?
Thank you,
DoM
It is possible. Do you want them to select random folders or a parent and it's subfolders? Parent/subs is easy - getting a list of random folders is more difficult.
Sending to excel (or a text file) is fairly easy - this is one example of how to find the next empty row in Excel. (You'll want to remove the block that writes the emails to Excel.)
Hello Diane,
I would like for them to select parent/subs. If I shared the code I currently run in Outlook, could you help modify to work with parent/subs and export to csv or excel?
Thank you,
DoM
You need to use the GetFolder function to walk the subfolders. Yes, if you share the code i can edit it to work with the function.
Diane,
Thank for your willingness to assist me with this. I have attached the code that I currently have running as a Macro in outlook.
Modify accordingly.
Thank you,
DoM
This writes just the count to a text file.
I have a similar macro at - https://www.slipstick.com/developer/print-list-of-outlook-folders/ - it splits the counts by folder.
Hi Diane. Thank you for this helpful introduction to Microsoft Outlook VBA. I am currently trying to figure out a way to have an Outlook meeting 'close' after getting the meeting accepted by required roles. The meeting invite is sent out to the whole department and can only occur once the necessary people are able to attend. Would you happen to have something like this already written or have an idea of how to execute this? Thanks.
You'd need to check the acceptances and see if all accepted - its certainly doable using VBA.
I have a sample that closes a room when the room is full - that can be the basis for your macro or you can watch for declines.
Sharing this code as it took me ages to work out how to create a meeting request from a shared calendar
Sub SendEmailFromSharedMailbox()
Dim olApp As Outlook.Application
Set olApp = Outlook.Application
Dim olNS As Outlook.Namespace
Dim objOwner As Outlook.Recipient
Set olNS = olApp.GetNamespace("MAPI")
Set objOwner = olNS.CreateRecipient("Shared Mailbox Name")
objOwner.Resolve
If objOwner.Resolved Then
MsgBox objOwner.Name
Set newCalFolder = olNS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
'Now create the email
Set olAppt = newCalFolder.Items.Add(olAppointmentItem)
With olAppt
'Define calendar item properties
.Start = "19/9/2019 2:00 PM"
.End = "19/9/2019 2:30 PM"
.Subject = "Appointment Item Here"
.Recipients.Add ("someone@email.com")
.Display
End With
End If
End Sub
My office uses OUTLOOK Office 365.I have my personal account and secondary shared account "DSC Ethernet Design" that I want to write a VBA macro that runs a rule on the shared account's inbox to move to another folder and forward the email to another email address.
I have no idea what I'm doing, or how to do it, but there has to be a simpler way to automate it instead of click, forward, move manually.
Hi,
I am trying to use your code as it is relevant to what I want to do with outlook. Would you mind taking a look at my stackoverflow question which references your code : https://stackoverflow.com/questions/55526145/excel-vba-create-meeting-in-non-default-calendar
Is the account an exchange account? Use the shared mailbox sample - and after setting the accounts, you set the sub folder:
Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar).Folders("calendar name")
It's actually recommended to use fewer dots, so this is better:
Set CalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
set newCal = calfolder.Folders("calendar name")
using a folder at the same level as the calendar using parent.folders("calendar name")
You can also use the GetFolderPath macro (for exchange accounts, or pst) and would put the calendar path when you call it.
Hi Diana,
I am trying to move items from my default sent folder to my default inbox and mark them as unread (without getting the stupid notification for emails that I sent)
I tried to use this with no luck. I am very new to VBA but i know how to pull information from the web i need together to make things happen the way i want it to. That isn't the case today :
Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
Set Items = Session.GetDefaultFolder(olFolderSentMail).Items
End Sub
Private Sub Items_ItemAdd(ByVal Item As Object)
Set MovePst = GetFolderPath(olFolderInbox)
Item.UnRead = False
Item.Move MovePst
End Sub
I am getting the error "compile error: sub or function not defined" at the 6th line at "GetFolderPath" which VBA highlights.
Could you help me out with this?
You need the GetFolderPath function in the Use a folder in another pst or mailbox section above.
Hi Diane, thank you for putting all these materials together! I'm quite new to this, and am trying to combine the "Send Meeting Requests" code with the "Use a shared folder (Exchange mailbox)" code. Everything works great when using my default email, but I'm having difficulties with the shared folder code. Below is the combination of the two. It's resulting in a compile error: Variable not defined from "newCalFolder" listed in the "shared folder" code set. Am I missing something? Option Explicit Public Sub CreateOutlookAppointments() Sheets("Sheet1").Select On Error GoTo Err_Execute Dim olApp As Outlook.Application Dim olAppt As Outlook.AppointmentItem Dim blnCreated As Boolean Dim olNs As Outlook.Namespace Dim CalFolder As Outlook.MAPIFolder Dim i As Long On Error Resume Next Set olApp = Outlook.Application If olApp Is Nothing Then Set olApp = Outlook.Application blnCreated = True Err.Clear Else blnCreated = False End If On Error GoTo 0 'Beginning of "Use a shared folder (Exchange mailbox)" code Dim NS As Outlook.Namespace Dim objOwner As Outlook.Recipient Set NS = Application.GetNamespace("MAPI") Set objOwner = NS.CreateRecipient("maryc") 'Is "maryc" the Shared folder name? objOwner.Resolve If objOwner.Resolved Then 'MsgBox objOwner.Name Set newCalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar) End If 'End of "Use a shared folder (Exchange mailbox)" code i =… Read more »
This is the mailbox name or alias -
Set objOwner = NS.CreateRecipient("maryc") 'Is "maryc" the Shared folder name?
The problem is here -
Set olAppt = CalFolder.Items.Add(olAppointmentItem)
it should be newCalFolder, not calfolder.
I want to apply this to a subfolder that is on the exchange, how do I get the name of this folder to put into the line " Set Items = NS.GetSharedDefaultFolder(objOwner, olFolder4. SiSTER Emails - Sent).Items" in this case the folder name is "4.SiSTER Emails - Sent" but this doesn't work. Can somebody please help me get the right folder name?