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 Session = Nothing
Set currentExplorer = Nothing
Set obj = Nothing
Set Selection = Nothing
End Sub
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?
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.
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.
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
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
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.
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
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
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
Hi
could i using restrict method for activeExplorer.selection.
thinks.
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
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?
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 Subit 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.