This code sample shows how to load the contents of a text file into an array then use the array to do something, in this example, to move messages to a new folder.
An Outlook user asked a question our forum at Move email items based on a list of email addresses:
I have a huge number of emails in sent items and I am trying to move email items based on email addresses to a new folder. I have a huge list of addresses. Can this be done through a macro?
This example uses a text file containing one entry per line, checks the addresses against the recipients of the message and moves the messages if there is a match. For best results, the text file should not have blank lines at the end of the list, however the macro will remove the last blank new line, if one exists.
Public Sub MoveMessagesSenderFile() Dim objOutlook As Outlook.Application Dim objNamespace As Outlook.NameSpace Dim objDestFolder As Outlook.MAPIFolder Dim objSourceFolder As Outlook.Folder Dim obj As Object Dim lngMovedItems As Long Dim intCount As Integer Dim strAddress As String Dim totalCount As Integer Set objOutlook = Application Set objNamespace = objOutlook.GetNamespace("MAPI") Set objSourceFolder = objOutlook.ActiveExplorer.CurrentFolder Set objDestFolder = objNamespace.GetDefaultFolder(olFolderInbox).Parent.Folders("Movetosent") ' array from list Dim fn As String, ff As Integer, txt As String fn = "D:\Documents\addresses-to-move.txt" '< --- .txt file path txt = Space(FileLen(fn)) ff = FreeFile Open fn For Binary As #ff Get #ff, , txt Close #ff ' remove ending line break, if exists If Len(txt) <> 0 Then If Right$(txt, 2) = vbCrLf Or Right$(txt, 2) = vbNewLine Then txt = Left$(txt, Len(txt) - 2) End If End If Dim arrAddress() As String 'Use Split function to return a zero based one dimensional array. arrAddress = Split(txt, vbCrLf) ' end arrray totalCount = objSourceFolder.Items.count For intCount = totalCount To 1 Step -1 Set obj = objSourceFolder.Items.Item(intCount) ' only move mail If obj.Class = olMail Then ' clear the string for the next message strAddress = "" Dim Recipients As Recipients Set Recipients = obj.Recipients For i = Recipients.count To 1 Step -1 recip$ = Recipients.Item(i).Address ' To use only the alias from the x.500 address ' If InStr(1, LCase(recip), "/ou=") Then recip = Right(recip, Len(recip) - InStr(1, LCase(recip), "recipients") - 13) ' Use semicolon separator if there is more than 1 address If i = 1 Then strAddress = recip Else strAddress = strAddress & recip & "; " End If Next i ' Go through the array and look for a match, then do something For i = LBound(arrAddress) To UBound(arrAddress) If InStr(LCase(strAddress), arrAddress(i)) > 0 Then On Error Resume Next obj.Move objDestFolder 'count the # of items moved lngMovedItems = lngMovedItems + 1 GoTo NextMsg End If Next i NextMsg: End If Next ' Display the number of items that were moved. MsgBox "Moved " & lngMovedItems & " messages(s)." Set obj = Nothing Set objOutlook = Nothing Set objNamespace = Nothing Set objSourceFolder = Nothing End Sub
How to use the macros on this page
First: You need to have macro security set to the lowest setting, Enable all macros during testing. The macros will not work with the top two options that disable all macros or unsigned macros. You could choose the option Notification for all macros, then accept it each time you restart Outlook, however, because it's somewhat hard to sneak macros into Outlook (unlike in Word and Excel), allowing all macros is safe, especially during the testing phase. You can sign the macro when it is finished and change the macro security to notify.
To check your macro security in Outlook 2010 and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, look at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Macros that run when Outlook starts or automatically need to be in ThisOutlookSession, all other macros should be put in a module, but most will also work if placed in ThisOutlookSession. (It's generally recommended to keep only the automatic macros in ThisOutlookSession and use modules for all other macros.) The instructions are below.
The macros on this page should be placed in a module.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor