![]() |
#1
|
|||
|
|||
![]()
Hi all,
Outlook 2007 and Windows 10 Home. I have updated my profile, but still shows as 2003. I am trying to put together some code to move emails easily in VBA. I have cobbled together the code below and have managed to get the Outlook folder path eg Inbox\Folder1\Folder2 I want to move the emails in Folder2 to the pst file with the same structure. For now we will assume the structure is there? What I cannot find out how to do at present is set the destination folder path using the above syntax and not Code:
Set objDestFolder = objNamespace.Folders("Inbox").Folders(""Folder1").Folders("Folder2") This is mainly for a manager who has levels you would not believe. I've had it working (not as it is now) with just a folder in the Inbox, but now want to expand it to cover all folders and then eventually create a folder if not present. However one thing at a time. Anyone able to help please.? TIA Code:
Sub MoveOldEmails() ' Declare all variables. Dim objOutlook As Outlook.Application Dim objNamespace As Outlook.NameSpace Dim objSourceFolder As Outlook.MAPIFolder Dim objDestFolder As Outlook.MAPIFolder Dim objFolders As Outlook.Folders Dim objFolder As Outlook.Folder, objParentFolder As Outlook.Folder Dim objVariant As Variant Dim lngMovedMailItems As Long Dim intCount As Integer, intDays As Integer Dim intDateDiff As Integer Dim strDestFolder As String, strFolderName As String, strFolder As String, strpath As String ' Create an object for the Outlook application. Set objOutlook = Application ' Retrieve an object for the MAPI namespace. Set objNamespace = objOutlook.GetNamespace("MAPI") ' Retrieve a folder object for the source folder. 'Set objSourceFolder = objNamespace.GetDefaultFolder(olFolderInbox) strFolderName = Application.ActiveExplorer.CurrentFolder.Name Set objSourceFolder = Application.ActiveExplorer.CurrentFolder ' ' Get full path strpath = strFolderName Set objParentFolder = objSourceFolder.Parent Do Until strFolder = "Personal Folders" strpath = objParentFolder.Name & "\" & strpath Set objParentFolder = objParentFolder.Parent strFolder = objParentFolder.Name Loop ' Loop through the items in the folder. NOTE: This has to ' be done backwards; if you process forwards you have to ' re-run the macro an inverse exponential number of times. For intCount = objSourceFolder.Items.Count To 1 Step -1 ' Retrieve an object from the folder. Set objVariant = objSourceFolder.Items.Item(intCount) ' Allow the system to process. (Helps you to cancel the ' macro, or continue to use Outlook in the background.) DoEvents ' Filter objects for emails or meeting requests. If objVariant.Class = olMail Or objVariant.Class = olMeetingRequest Then ' This is optional, but it helps me to see in the ' debug window where the macro is currently at. 'Debug.Print objVariant.SentOn & " - " & objVariant.Subject & " - " & DateDiff("d", objVariant.SentOn, Now) ' Calculate the difference in years between ' this year and the year of the mail object. 'intDateDiff = DateDiff("yyyy", objVariant.SentOn, Now) intDays = DateDiff("d", objVariant.SentOn, Now) ' Only process the object if it older than 60 days If intDays > 60 Then ' Calculate the name of the personal folder. strDestFolder = Year(objVariant.SentOn) ' Retrieve a folder object for the destination folder. 'Set objDestFolder = objNamespace.Folders(strDestFolder).Folders("Inbox").Folders(strFolderName) Set objDestFolder = objNamespace.(strDestFolder & "\" & strpath) ' Move the object to the destination folder. objVariant.Move objDestFolder ' Just for curiousity, I like to see the number ' of items that were moved when the macro completes. lngMovedMailItems = lngMovedMailItems + 1 ' Destroy the destination folder object. Set objDestFolder = Nothing End If End If Next ' Display the number of items that were moved. MsgBox "Moved " & lngMovedMailItems & " messages(s)." End Sub Last edited by Welshgasman; 12-18-2017 at 04:44 AM. Reason: Added new office version |
#2
|
||||
|
||||
![]()
You can loop through the folders collection below any start folder and move the message to that folder. The following puts the selected message in the named folder ("Folder Name"), creating it as a subfolder of the start folder if not present. The macro prompts for the top level start folder.
Code:
Option Explicit Sub MoveMessage() Dim olMsg As MailItem Dim olFolder As Folder On Error Resume Next Set olMsg = ActiveExplorer.Selection.Item(1) GetFolder olMsg, "Folder Name" lbl_Exit: Set olMsg = Nothing Exit Sub End Sub Sub GetFolder(olItem As MailItem, strFolderName As String) Dim olNS As NameSpace Dim cFolders As Collection Dim olFolder As Outlook.Folder Dim StartFolder As Outlook.Folder Dim SubFolder As Outlook.Folder Dim bExists As Boolean Set cFolders = New Collection Set olNS = GetNamespace("MAPI") Set StartFolder = olNS.PickFolder cFolders.Add StartFolder Do While cFolders.Count > 0 Set olFolder = cFolders(1) cFolders.Remove 1 If UCase(olFolder.Name) = UCase(strFolderName) Then bExists = True Exit Do End If For Each SubFolder In olFolder.folders cFolders.Add SubFolder Next SubFolder Loop If Not bExists Then Set olFolder = StartFolder.folders.Add(strFolderName) End If olItem.Move olFolder lbl_Exit: Set olNS = Nothing Set StartFolder = Nothing Set cFolders = Nothing Set olFolder = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
![]()
Thank you Graham,
I'll add that to what I have and give it a go. |
#4
|
|||
|
|||
![]()
Hi Graham,
I have tried to adapt your code, and have the following Code:
Dim objMsg As MailItem ...... Set objMsg = objSourceFolder.Items.Item(intCount) It fails with a Type Mismatch on the above line. The original code had the variable type as variant? It appears to fail in the inbox, but only after going through about 60 emails that will not be processed.? Edit: I changed the variable objMsg to a variant in my code and your Getfolder proc and it works all the way through the folder? Last edited by Welshgasman; 12-18-2017 at 08:52 AM. Reason: Added change for variant |
#5
|
|||
|
|||
![]()
Hi Graham,
I am trying to tweak the code. I only need to know if the folder exists on the first move of a message, after that we know the folder will exist. At present I am going through the code for every message.? I have my sub getting a relevant message, then calling your code, which I have named to MoveMailToFolder(), so my code replaces your MoveMessage() sub.? What do I need to change so as to only run once and ensure the folder exists or is created? TIA |
#6
|
||||
|
||||
![]()
To answer both your questions, 'Dim objMsg As MailItem' requires that all the items checked are actually mail items. If there are mixed item types in the folder use
Code:
Dim objMsg As Object The code checks that the appropriate folder exists for each message and creates it only if missing; and as you wanted to avoid hard coding the path, it also locates the named folder in the folder tree in order to move the message to it. If however you want to process a folder full of messages into the same sub folder , then lose the function and process the messages in a loop in the main macro, testing for the folder with the first message only e.g. as follows. Process the messages in reverse order so that the movement of the messages doesn't upset the count. The process moves all the messages from the selected startfolder to the named sub folder of that startfolder. Code:
Option Explicit Sub MoveMessages() Dim olNS As NameSpace Dim cFolders As Collection Dim strFolderName As String Dim olFolder As Outlook.Folder Dim StartFolder As Outlook.Folder Dim SubFolder As Outlook.Folder Dim olItem As Object Dim bExists As Boolean Dim iMsg As Integer Set cFolders = New Collection Set olNS = GetNamespace("MAPI") strFolderName = InputBox("Enter the name of the folder to move the messages to") 'The target folder Set StartFolder = olNS.PickFolder For iMsg = StartFolder.Items.Count To 1 Step -1 Set olItem = StartFolder.Items(iMsg) If iMsg = StartFolder.Items.Count Then cFolders.Add StartFolder Do While cFolders.Count > 0 Set olFolder = cFolders(1) cFolders.Remove 1 If UCase(olFolder.Name) = UCase(strFolderName) Then bExists = True Exit Do End If For Each SubFolder In olFolder.folders cFolders.Add SubFolder Next SubFolder Loop If Not bExists Then Set olFolder = StartFolder.folders.Add(strFolderName) End If End If olItem.Move olFolder Next iMsg lbl_Exit: Set olNS = Nothing Set StartFolder = Nothing Set cFolders = Nothing Set olFolder = Nothing Set olItem = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
![]()
Thank you Graham,
I went home last night thinking about this and was planning on turning your original code into a function to create the folder if it did not exist and pass back the folder to the calling program to process it, in this case a move.? All the items in the Inbox are mail items AFAIK, so will have to investigate further if I find the time. Many thanks for your help. |
![]() |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
cutemich | Word VBA | 1 | 05-09-2017 06:27 PM |
![]() |
vagabond | Word | 7 | 05-02-2017 03:24 PM |
Linking to a spreadsheet in the same folder without folder path | Jacky837 | Excel | 0 | 07-30-2016 05:07 AM |
![]() |
carlandtina02 | Excel Programming | 3 | 04-15-2016 01:47 PM |
How to find the path to the downloads folder when it is different for each user | CatMan | Excel Programming | 2 | 12-07-2012 09:59 PM |