Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-18-2017, 04:41 AM
Welshgasman Welshgasman is offline Folder path in outlook for moving files Windows 7 32bit Folder path in outlook for moving files Office 2003
Novice
Folder path in outlook for moving files
 
Join Date: Jun 2011
Posts: 26
Welshgasman is on a distinguished road
Default Folder path in outlook for moving files

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")
as I will not know how many levels there might be.

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
Reply With Quote
  #2  
Old 12-18-2017, 05:49 AM
gmayor's Avatar
gmayor gmayor is offline Folder path in outlook for moving files Windows 10 Folder path in outlook for moving files Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,103
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 12-18-2017, 07:08 AM
Welshgasman Welshgasman is offline Folder path in outlook for moving files Windows 10 Folder path in outlook for moving files Office 2007
Novice
Folder path in outlook for moving files
 
Join Date: Jun 2011
Posts: 26
Welshgasman is on a distinguished road
Default

Thank you Graham,

I'll add that to what I have and give it a go.
Reply With Quote
  #4  
Old 12-18-2017, 08:42 AM
Welshgasman Welshgasman is offline Folder path in outlook for moving files Windows 10 Folder path in outlook for moving files Office 2007
Novice
Folder path in outlook for moving files
 
Join Date: Jun 2011
Posts: 26
Welshgasman is on a distinguished road
Default

Hi Graham,
I have tried to adapt your code, and have the following
Code:
Dim objMsg As MailItem
......
Set objMsg = objSourceFolder.Items.Item(intCount)
and it appears to work for some mail messages but not all?
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
Reply With Quote
  #5  
Old 12-18-2017, 10:28 AM
Welshgasman Welshgasman is offline Folder path in outlook for moving files Windows 10 Folder path in outlook for moving files Office 2007
Novice
Folder path in outlook for moving files
 
Join Date: Jun 2011
Posts: 26
Welshgasman is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 12-18-2017, 10:21 PM
gmayor's Avatar
gmayor gmayor is offline Folder path in outlook for moving files Windows 10 Folder path in outlook for moving files Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,103
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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 suggested was for a single message.

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
Reply With Quote
  #7  
Old 12-19-2017, 02:25 AM
Welshgasman Welshgasman is offline Folder path in outlook for moving files Windows 10 Folder path in outlook for moving files Office 2007
Novice
Folder path in outlook for moving files
 
Join Date: Jun 2011
Posts: 26
Welshgasman is on a distinguished road
Default

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.
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Folder path in outlook for moving files Save to PDF with predetermined filename and specific folder path cutemich Word VBA 1 05-09-2017 06:27 PM
Folder path in outlook for moving files Moving body text from old files to new files (based on template) 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
Folder path in outlook for moving files Using user form fields as folder path and file name 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:46 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft