These two samples contains the basic code for working with all items in a selected folder or selected items in a folder. The code sample prints the item subject in the Immediate window (Ctrl+6).
Because the code uses generic objects instead of specific objects, it will work with all item types.
Work with all items in any folder
Option Explicit
Public Sub DoSomethingFolder()
Dim objOL As Outlook.Application
Dim objItems As Outlook.Items
Dim objFolder As Outlook.MAPIFolder
Dim obj As Object
Set objOL = Outlook.Application
Set objFolder = objOL.ActiveExplorer.CurrentFolder
Set objItems = objFolder.Items
For Each obj In objItems
With obj
' do whatever
Debug.Print .Subject
End With
Next
Set obj = Nothing
Set objItems = Nothing
Set objFolder = Nothing
Set objOL = Nothing
End SubTo work with all items in a specific folder, replace
Set objFolder = objOL.ActiveExplorer.CurrentFolder
with GetDefaultFolder and locate the folder (if it's not a default folder).
Set objFolder = Ns.GetDefaultFolder(olFolderCalendar)
Set objFolder = Ns.GetDefaultFolder(olFolderCalendar).Folders("Subfolder")
You'll also need to add these two lines to the macro - Dim Ns should be the first Dim statement, and Set Ns needs to be the first Set statement.
Dim Ns As Outlook.NameSpace
Set Ns = Application.GetNamespace("MAPI")
See VBA and non-default Outlook Folders for more information.
Work with Selected items in any folder
Option Explicit
Public Sub DoSomethingSelection()
Dim objOL As Outlook.Application
Dim currentExplorer As Explorer
Dim Selection As Selection
Dim obj As Object
Set objOL = Outlook.Application
Set currentExplorer = objOL.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection
With obj
' do whatever
Debug.Print .Subject
End With
Next
Set Selection = Nothing
Set currentExplorer = Nothing
Set obj = Nothing
Set Selection = Nothing
End Sub
John says
Is there a way to get all of the email items in an Outlook folder regardless if they're on the Exchange server or just in Outlook? The policy where I work won't allow me to change the caching option. I was hoping for a property that I could set or method to pull them all in.
Vidal Ccori says
Option Explicit Public Sub DoSomethingFolder() Dim objOL As Outlook.Application Dim objItems As Outlook.Items Dim objFolder As Outlook.MAPIFolder Dim obj As Object Set objOL = Outlook.Application Set objFolder = objOL.ActiveExplorer.CurrentFolder Set objItems = objFolder.Items For Each obj In objItems With obj ' do whatever Debug.Print .Subject End With Next Set obj = Nothing Set objItems = Nothing Set objFolder = Nothing Set objOL = Nothing End SubPlease, how can I make it go through the mail starting from the most recent mail to the oldest?
In Outlook 14 you loop from newest to oldest and Outlook 16 loops from oldest to newest.
Sathiya says
Hi Diane, hope you're doing fine, how do I specify certain links to be opened in an email, for example, if I have an email that has 6 links but I only want to open 3 links out of it, how do I define the pattern so that it only opens those 3 instead of all, let's say http://www.google.com, http://www.gmail.com, and http://www.ymail.com.
Diane Poremsky says
you need an if statement -
You use this if you want to skip the url -
If InStr(strURL, "google.com") Then GoTo NextURL
If you want to skip all but certain sites, this should work
If InStr(strURL,"google.com") = 0 Then GoTo NextURL
Sathiya says
Hi Diane,
It works only on one, is there a way I can add multiple? for example, when I say
If InStr(strURL,"google.com") = 0 Then GoTo NextURL it opens Google.com however I still have Ymail & Gmail in the email which I want to open may I know how do I add them?
If InStr(strURL,"google.com") = 0 Then GoTo NextURL
If InStr(strURL,"ymail.com") = 0 Then GoTo NextURL
If InStr(strURL,"gmail.com") = 0 Then GoTo NextURL
I tried the above still it only opens "Google" but doesn't open "ymail & gmail"
I'm sorry if this is a dumb question, I'm a novice, your help is highly appreciated.
Diane Poremsky says
Logic error. :) Goto NextURL skips the other two.
This should work -
If InStr(strURL,"google.com") = 0 Then
If InStr(strURL,"ymail.com") = 0 Then
If InStr(strURL,"gmail.com") = 0 Then GoTo NextURL
End if
End if
Sathiya says
Thanks a Million! You're a life saver!
Sathiya says
Hi Diane, When I used the code it only opens only the links on the first email even though the. Global value is set to True, there are 12 emails in the folder and it always opens the links on the first email, how do I get it to open all, your help is highly appreciated.
Diane Poremsky says
global opens all links in the selected message. If you want to open links in the selected message, you need to use code that loops the selection.
https://www.slipstick.com/developer/code-samples/open-hyperlinks-email-message/#selected
Sathiya says
Thanks a Million! one follow-up question, say I've ~10 hyperlinks in which I only want to open 3 specific ones, just for example say those are Ymail, Gmail & Google how do I modify the ".Pattern" code to let the script know that I only want to open the above 3, I tried different methods but no go, as always your help is highly appreciated!
Kashif says
Hi Diane,
Thanks for the tutorial, I hope you are doing well, I have one question to you, can I move the mails (only those mails that have category blank) from a folder to another folder at once like copy and paste not through loop, move bulk mails at once, is this possible? if yes? please help me.
Like if we do manually then will go to that folder then apply filter the mails then select those filtered mails and drage those mails to another folder, at once we moved all the mails to difference folder.
Please take good care of yourself and your family.
Thanks
Kashif
Joseph says
Diane - you amaze me. I'm relatively new and I always end up on your site for solutions.
I want to display collection of MailItems I've captured in my current Explorer window. Basically, I want to do the equivalent of an instant search but programmatically...
I can't figure out how to get teh collection to display ... :/
set olExplorer = ActiveExplorer
olExplorer.Display(itmEmails)
That's clearly not right.
Can you push me in the right direction?
Nidhi says
Hi! Can someone help me with a macro wherein I am trying to extract information from the emails, but not from all the emails. The macro needs to filter the emails based on the subject of the email and then extract information from the same?
Diane Poremsky says
This will get values from the message - as long as nothing in the other messages match, it could run on all messages in the folder.
Use RegEx to extract text from an Outlook email message (slipstick.com)
Otherwise you need to either use an if statement to check the subject -
If instr(1, item.subject, "phrase" > 0 then
' extract
end if
or use a find or restrict filter then run the macro on the filtered versions - this would be faster if there were "a ton" of messages and only a few needed to be processed.
DNDanello says
So if I wanted to select only the items visible in a filtered view of the folder, would it be:
Set currentExplorer = objOL.ActiveExplorer
Set Selection = currentExplorer.SelectAllItems
?
Thx, you're awesome.
Diane Poremsky says
It would still be the same as with a selection -
For Each obj In Selection
With obj
' do whatever
Debug.Print .Subject
End With
Next
Guy Edkins says
Hi,
I have a simple routine that parses HTML based mails and pops values into an Excel sheet. Oddly it is only parsing one email in the Inbox. There are five, if I remove the one it parses it then parses another, but only the one. I am having trouble finding the reason its passing over all the others in the inbox. Code below:
' Set Outlook application object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
Dim objNSpace As Object ' Create and Set a NameSpace OBJECT.
' The GetNameSpace() method will represent a specified Namespace.
Set objNSpace = objOutlook.GetNamespace("MAPI")
Dim myFolder As Object ' Create a folder object.
Set myFolder = objNSpace.GetDefaultFolder(olFolderInbox)
Dim objItem As Object
iRows = 2
' Loop through each item in the folder.
For Each objItem In myFolder.Items
If objItem.Class = olMail Then
Dim objMail As outlook.MailItem
Set objMail = objItem
html.Body.Innerhtml = objItem.HTMLBody ' set the body of the email equal to the html from outlook email
Set elements = html.getElementsByTagName("td")
For Each element In elements
'does all the extractions here
Next
End If
iRows = iRows + 1
Next
Diane Poremsky says
If you move or delete, that messes up the count and skips every other message - but if you are only getting 1 message, its not that.
iRows = iRows + 1 will add a row for every message - if there are messages that aren't processed, it will add a blank row.
Marek Czerski says
Dear Diane,
Thanks a lot for coding samples you provide to us. I have learnt from you a lot and you’ve saved a lot of my time.
Now, I have a problem with your DoSomethingSelection Sub. I apply the "For Each obj In Selection" loop to move each mail selected in my default inbox folder to other folders and it works great as long as the macro processes incoming "ordinary" mails. However, the whole procedure stucks when a message to be processed is a delivery confirmation item. The system displays error message that reads: "Runtime-error: 13. Type mismatch". I am using Outlook 2010 64-bit in Windows 2010 64-bit.
I have tried to find an "inspiration" in queries by other users, but to no effect. I do appologize I my problem has been already solved on this website.
I will greatly appreaciate your advice.
Best regards
Marek
Diane Poremsky says
The macros above should work on any item.
The macro sample at the end of this article - Use a Macro to Move Aged Email in Outlook (slipstick.com) - shows how to check for different item types.
You can use something like
If obj.Class = olReport Then
' skip it, use a different field etc
End if
Marek Czerski says
Thanks a lot for your reply. I will give it a try and if there is no further feedback from me, then assume, your advice has solved my problem.
Best regards
duck says
Hi
could i using restrict method for activeExplorer.selection.
thinks.
Ivan says
Diane, good morning
hopefully you can help me, i'm very new to vba outlook, and probably what i'm looking for is already published somewhere, but i cannot find.
I'm looking for a vba code that simply adds a follow up flag and reminder to an email i send if it contains in the body of message a specific phrase.
Your help would be highly appreciated.
Best regards
Ivan
Chris says
Hi Diane,
I have earlier designed a macro to send emails by attaching files and send to specific emails.
Is it possible to send another reminder email by using Outlook vba if the recipient never replies to my 1st email?
Jim says
Diane,
I have this code that runs when i close outlook, it moves all of my deleted messages to a file. It stopped working with a type mismatch error on this line "et objTrash = Outlook.Application.Session.GetDefaultFolder(olFolderDeletedItems).Items"
Can you please help?
Thank you,
Jim
Sub MoveDeletedItems() 'Move messages from "Deleted" to "Deleted Items Archive" personal folder. Dim objFolder As Outlook.MAPIFolder Set objFolder = Outlook.Application.GetNamespace("MAPI").folders("2020_Deleted_Folder") '.Folders("Deleted Items") Dim objTrash As Outlook.Items Set objTrash = Outlook.Application.Session.GetDefaultFolder(olFolderDeletedItems).Items Dim objDrafts As Outlook.Items Dim objItem As Object Dim objDraftItem As Object Dim varMsgID As String Dim varLoopCounter, x As Integer varLoopCounter = objTrash.count For x = 1 To varLoopCounter Set objTrash = Outlook.Application.Session.GetDefaultFolder(olFolderDeletedItems).Items For Each objItem In objTrash Debug.Print "Moving Item: " & objItem.Subject & " - " & objItem.Class objItem.Move objFolder 'varLoopCounter = varLoopCounter + 1 Exit For Next Next x 'Cleanup Set objItem = Nothing Set objDraftItem = Nothing Set objFolder = Nothing Set objDrafts = Nothing Set objTrash = Nothing 'Set objInbox = Nothing 'Set objNS = Nothing 'End Cleanup End SubDiane Poremsky says
it quit following an update? Try using
Application.Session.GetDefaultFolder(olFolderDeletedItems).Items
I've had issues with some code failing after updates and simple tweaks that really changed nothing fixed them.
Liviu says
Is there any documentation as to what type of items could exist in a mail folder? Or, at least, that any such items must share some common properties e.g. Categories. For example, MailItem, MeetingItem and ReportItem all have a Subject property, but if your For-Each hits some other type of item that doesn't have it then Debug.Print would throw an error.
Diane Poremsky says
I'm not aware of any specific documentation that lists it - there is documentation that lists the fields for each item type.
These links are for an older version but still apply to current versions.
for example, mailitem and reportitem is here:
https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa210946(v=office.11)
https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa211038(v=office.11)
Robert says
Hello, was linked here from: https://www.slipstick.com/developer/code-samples/macro-resend-message/?fbclid=IwAR29t9kfQrcG922CkbgQN9m77XVPUBsOc8yKsylxHwN7CTfgUel4cql08OY
I used the following code to resend selected messages and discard the original on Outlook 2010, but it doesn't work correctly on 2016:
Sub BatchResendEmails()
Dim objSelection As Outlook.Selection
Dim objMail As Outlook.MailItem
Dim objInspector As Outlook.Inspector
Dim objResendMail As Outlook.MailItem
Set objSelection = Application.ActiveExplorer.Selection
If Not (objSelection Is Nothing) Then
On Error Resume Next
For Each objMail In objSelection
objMail.Display
Set objInspector = myItem.GetInspector
'Resend message
objInspector.CommandBars.ExecuteMso ("ResendThisMessage")
Set objResendMail = Application.ActiveInspector.CurrentItem
'You can change the email details as per your needs
With objResendMail
.Subject = "" + objMail.Subject
.Send
End With
objMail.Close olDiscard
Next
End If
End Sub
I have a rule to add a category to emails sent with specific text in the subject.
I then filter my sent items for that category, and use the above macro to select emails from the day before and resend them.
As mentioned this above macro doesn't work as intended on outlook 2016, and hoping for help, as I'm not personally that will versed.
Diane Poremsky says
Do you receive any error messages? Do you have macro security set to low? The code should work in outlook 2016.
Robert says
Hello, sorry for the delay I've been off work.
I don't get any error messages, and my macro settings are set to check per session if I want to use it. When I select emails to batch resend that don't have a category, they seem to go fine, but only with a small group. I sometimes need to batch resend about 60 tagged emails, and I will get the sent messages in my inbox, but no new email is produced in my sent items.
Diane Poremsky says
The macro is working here, but I only tested it with 3 messages. Is it only failing with a large # of resends?
Robert says
Hello, it seems to fail with either large volume, or if I have a category set to the email.
Robert says
Hello, might add, that I am also on windows 10.
My colleague uses the same macro with outlook 2016 but on windows 7.
Robert says
Hello, sorry i thought I replied to this already. I do not get any error message and my security settings allow macro.
I have read receipts for my emails, they all come through to my inbox, but the emails are no where to be seen in the sent items.
Reuben Dayal says
Hi Diane,
Is there a way to assign the selected items or a selection any name or value, so Outlook remembers it and the same selection can be recalled if the outlook folder changes to another folder and back to the original one again?
I have tried using your above example of For Each obj In Selection and just a With Selection
commands, but neither seem to reselect the emails I had originally selected, once I go out of the folder and return to it again.
Thank you for your suggestions.
Reuben
Alan says
Hi Diane,
Do you have any suggestions around how to handle the scenario where you want to loop through each object in a selection if one or more items are selected but otherwise loop through each object in the folder if no items are selected? Is there any way to do something along the lines of this pseudocode:
Public Sub DoSomething()'Dim stuff
'Set stuff
If Selection.Count > 0 Then
objItems = Selection
Else
objItems = objFolder.Items
End If
For Each obj in objItems
'do whatever
Next
End Sub
Thanks!
Diane Poremsky says
That would be difficult, i'd think... because you'll always have at least 1 selected. You can loop the folder if 1 is selected, or do the selection if more than one is selected.
This little tweak will do the entire folder if 1 is selected, or only the selection if more than one is selected.
Set Selection = currentExplorer.Selection If Selection.Count = 1 Then ' run the other macro DoSomethingFolder Else For Each obj In Selection '--snip-- Next End IfSam says
Hi Diane,
I have an issue, I am hoping perhaps you or someone here can provide insight.
For a selected email, I assign user defined category from combobox and move the email to a folder. It works fine with emails in my Inbox.
When I do the same for emails in shared inbox, it simply moves it without assigning category. And there is no error message.
Any help appreciated.
Thank you
Diane Poremsky says
Does the category exist in the shared mailbox? Are you using code with the custom form? If so, it could be something with the code too.
Eric Li says
Hi Diane,
Thank you for this tutorial, we use it to send all drafts in draft folder using a specific email address (SentOnBehalfOfName).
However, the macro will only send half of the drafts in the folder, do you know if there's a fix for this?
thank you very much.
Public Sub DoSomethingFolder()
Dim objOL As Outlook.Application
Dim objItems As Outlook.Items
Dim objFolder As Outlook.MAPIFolder
Dim obj As Object
Set objOL = Outlook.Application
Set objFolder = objOL.ActiveExplorer.CurrentFolder
Set objItems = objFolder.Items
For Each obj In objItems
With obj
.SentOnBehalfOfName = "XXX@gmail.com"
.Send
End With
Next
Set obj = Nothing
Set objItems = Nothing
Set objFolder = Nothing
Set objOL = Nothing
End Sub
Diane Poremsky says
Sorry I missed this earlier. I wonder if it is losing the count as messages are sent, much like deleting items will get every other one because the index # changes as you delete. The fix is to count backwards - For i = 1 to objitems.count step -1
HermanT says
I changed it for a specific folder.
Then i get a error:
Fout -2147221233 (8004010f) tijdens uitvoering
De bewerking is mislukt. Kan een object niet vinden
Why? And how to solve it?
Diane Poremsky says
what code are you using to identify the folder? The error says 'Can not find an object' - and I'm guessing the object it can't find is the folder.
Ashish T says
i have already made code to save attachment from share mail box to sharepoint. but i am getting difficulties in saving attachment from specific date range kindly help
Diane Poremsky says
so you want to save only those in a specific range, not the file date?
Two methods: Select them then run a macro (second macro) or set the macro to check the date on each message in the folder.
if you are calculating the date using today's date as the base, you can do something like this:
intDateDiff = DateDiff("d", obj.SentOn, Now)
If intDateDiff > 7 Then ' older than 7 days
' do whatever
end if
ClintonA says
Dianne,
Great code. Do you know how to adapt this to modify the calendar folder to delete the subject and add the organizer? We have it set correctly moving forward but after migration all previous reservations show the subject instead of the organizer.
Any help appreciated.
Thank you,
Have a great one
Diane Poremsky says
I assume you mean different code than is what on this page... you'll use code similar to .subject = .organizer
if you are using exchange, the new server can be configured to show the organizers name on resource calendars.
Alex Chaplin says
Dianne thanks a lot for your code on "Work with all items in any folder". I was able to adapt it to create a list of sender names from 148 email responses saved in an Outlook folder saving me a lot of tedium and fat fingering to create a dg group. I also wanted to look at emails attached to some of the emails in the folder and get those sender names as well. I looked at different objects and attributes but couldn't find it. Is there a way to do this? Thanks in advance.
Diane Poremsky says
The emails were attachments on other emails?
Alex Chaplin says
Diane, I kept hitting an "Outlook cannot do this action on this type of attachment" error. This site http://www.outlookcode.com/threads.aspx?forumid=2&messageid=5551 provided the solution. The problem was embedded olOLE attachments which couldn't be accessed like other attachments. The code ran fine with the fix applied to check for objAttachments.Item(i).Type 6. See below.
Thanks a lot for your help. Much appreciated.
This code is in the WITH loop
Set objAttachments = obj.Attachments
lngCount = objAttachments.Count
If lngCount > 0 Then
For i = lngCount To 1 Step -1
' Added code fix here
If objAttachments.Item(i).Type 6 Then
strFile = objAttachments.Item(i).FileName
If Right(strFile, 4) = ".msg" Then
strFile = strFolderpath & i & strFile
objAttachments.Item(i).SaveAsFile strFile
' Debug.Print strFile
Set objItem = Application.CreateItemFromTemplate(strFile) 'Application.ActiveInspector.CurrentItem
' Debug.Print "attachment", objItem.SenderName, objItem.SenderEmailAddress
Debug.Print objItem.SenderName; ";"
objItem.Close olDiscard
End If
End If
Next
End If
Diane Poremsky says
you need to check for attachments, save & open them. open attached messages
Dimas says
Hi Diane!
I'm trying to get the size of each folder in the Inbox. That's not hard to do, just takes a lil time.
But, I don't know (and haven't been able to find) how to pull information from non-cached emails. I mean, those that only show after you click "Click here to view more on Microsoft Exchange".
Do you know how to do this, will you help me?
Thanks a lot!
Dimas
Diane Poremsky says
What version of Exchange? Do you need to use VBA or can you use powershell?
Russ B. says
Thank you Diane for answering my other questions I have posted in other sections... You are great and I have become a VBA demon. Now I am trying to take a specific day's appointments and then load them into an excel sheet that is published to our intranet daily. The above code seems to grab everything in the calendar folder. How would I restrict it to a specific day? For example if my calendar is currently viewing the appointments for December 5th, 2014 I would want to run a macro that would grab that information for each appt. on the currently viewed day. I think I can manage the rest once I have the appointment info for that day. Any help is always appreciated.
Diane Poremsky says
Steal the restrict code from https://www.slipstick.com/outlook/combine-outlook-calendars-print-one/ :
'create the filter - this copies appointments today to 3 days from now
sFilter = "[Start] >= '" & Date & "'" & " 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
Ainsley says
Hi Diane,
Yes there is a folder named "VBA" under my inbox. However, I had two email accounts under my outlook (one is a shared email account while another is a personal one). Will this affect the coding? I need the code to run only on one of the account.
Diane Poremsky says
Are they using two different data files? This line:
Set objFolder = Ns.GetDefaultFolder(olFolderInbox).Folders("VBA2")
is using the default data file's Inbox\VBA folder. If the folder you want to use is in the second account's data file, you need to either use current folder and select it before running the macro or use the getfolderpath function at https://www.slipstick.com/developer/working-vba-nondefault-outlook-folders/.
Ainsley says
Hi Diane,
Oh, got it. Thank you so much Diane!
Ainsley says
Hi Diane,
I tried to use this as sample to run a code on all items in a subfolder named "VBA Outlook Test" under the Inbox but however turned out to be error. My code has this structure:
Sub GetValueUsingRegEx()
Dim objOL As Outlook.Application
Dim objItems As Outlook.Items
Dim objFolder As Outlook.MAPIFolder
Dim olMail As Object
Dim Reg1 As RegExp
Dim M1 As MatchCollection
Dim M As Match
Set objOL = Outlook.Application
Set objFolder = objOL.GetDefaultFolder(olFolderInbox).Folders("VBA Outlook Test") 'Error located on the above line
Set objItems = objFolder.Items
For Each olMail In objItems
'All the working codes here
Next
Set olMail = Nothing
Set objOutlookItem = Nothing
Set objItems = Nothing
Set objFolder = Nothing
Set objOL = Nothing
End Sub
Could you please advise what is wrong? Error says "Object does not support this property or method"
Diane Poremsky says
My bad. :) When using currentfolder, it uses the application, when you call the folders by name, you need to use the namespace. I'll fix the notes I added earlier tonight without thinking about that. Sorry.
You need
Dim Ns As Outlook.NameSpace
Set Ns = Application.GetNamespace("MAPI")
instead of
Dim objOL As Outlook.Application
Set objOL = Outlook.Application
or change this: Set objFolder = objOL.[snip]
to Set objFolder = session.[snip]
Ainsley says
Hi Diane,
Sub GetValueUsingRegEx()
Dim Ns As Outlook.NameSpace
Dim objItems As Outlook.Items
Dim objFolder As Outlook.MAPIFolder
Dim olMail As Object
Dim Reg1 As RegExp
Dim M1 As MatchCollection
Dim M As Match
Set Ns = Application.GetNamespace("MAPI")
Set objFolder = Ns.GetDefaultFolder(olFolderInbox).Folders("VBA")
'Error in above line: The attempted operation failed. An object could 'not be found.
Set objItems = objFolder.Items
For Each olMail In objItems
'All the working codes here
Next
Set olMail = Nothing
Set objOutlookItem = Nothing
Set objItems = Nothing
Set objFolder = Nothing
Set Ns = Nothing
End Sub
Unfortunately, I tried using both of the corrections you mentioned but still getting error on the same line. Any idea why this is happening? :(
Diane Poremsky says
it works fine here. Is there a folder named VBA under the Inbox? You'll get that error if the folder isn't found.
Ainsley says
Hi Diane,
After revising the code to the correction you mentioned, I still had an error on the same line code saying "An object could not be found". Any idea why? :(
Diane Poremsky says
Without checking it first, I'll guess another oops on my part. :)