Moving Outlook Emails from MS Access

[Phantek]

I would like to be able to automate the process of moving a specific email from one sub-folder to another sub-folder in Outlook, via an Access database that I am using.

I am using an Exchange account, and the emails are within folders that are located on a secondary inbox in that Exchange account.

Any ideas how I could go about doing this? Thanks in advance.

 

[darbid]

I would like to be able to automate the process of moving a specific email from one sub-folder to another sub-folder in Outlook, via an Access database that I am using.

Any ideas how I could go about doing this? Thanks in advance.

This is not my code I have copied it to show you how to move emails from a folder -

Thus you will learn how to get a folder and then how to get an email within that folder and how to move.

source http://www.experts-exchange.com/Sof...eryTermInfo=1+10+30+email+folder+move+outlook

Sub MoveAllMessages()
    Dim olkSource As Outlook.MAPIFolder, _
        olkDestination As Outlook.MAPIFolder, _
        olkMsg As Outlook.MailItem, _
        intIndex As Integer
    'Change the folder path on the following line
    Set olkSource = OpenOutlookFolder("Testing\Folder1")
    'Change the folder path on the following line
    Set olkDestination = OpenOutlookFolder("Testing\Folder2")
    For intIndex = olkSource.items.Count To 1 Step -1
        olkSource.items.Item(intIndex).Move olkDestination
    Next
    Set olkSource = Nothing
    Set olkDestination = Nothing
    Set olkMsg = Nothing
    MsgBox "Finished!", vbInformation + vbOKOnly, "Move All Messages"
End Sub
 
Function IsNothing(obj)
  If TypeName(obj) = "Nothing" Then
    IsNothing = True
  Else
    IsNothing = False
  End If
End Function
 
Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        olkFolder As Outlook.MAPIFolder
    On Error GoTo ehOpenOutlookFolder
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        If Left(strFolderPath, 1) = "\" Then
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        End If
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            If IsNothing(olkFolder) Then
                Set olkFolder = Session.Folders(varFolder)
            Else
                Set olkFolder = olkFolder.Folders(varFolder)
            End If
        Next
        Set OpenOutlookFolder = olkFolder
    End If
    On Error GoTo 0
    Exit Function
ehOpenOutlookFolder:
    Set OpenOutlookFolder = Nothing
    On Error GoTo 0
End Function

I am using an Exchange account, and the emails are within folders that are located on a secondary inbox in that Exchange account.

Tell me more about this bit. Is the account your main profile account or secondary.

 

[Phantek]

darbid, thank you very much. The code works great.

The origin and destination folders are both in my secondary email account (though it is on the same exchange server as my primary).

The only thing now, is how may I move the emails one at a time as they get processed? I can see the section of code that loops through all of the emails, but how am I able to identify one specific email to move? As they were submitted by a form, they all have the same subject line.

Thanks again!

 

[darbid]

The only thing now, is how may I move the emails one at a time as they get processed? I can see the section of code that loops through all of the emails, but how am I able to identify one specific email to move? As they were submitted by a form, they all have the same subject line.

Thanks again!

You will move them because of an event that you will watch for - or if you initiate the code then you will have to get the email and maybe check the subject or the TO or FROM and decide whether to more or not.

 

[Phantek]

Okay, I should be more specific. The emails are orders. Currently Access imports the email information, parses the body of the email, and places the information in the appropriate field of an Access form. After the information is reviewed, a button is clicked to process the information - which adds it to a table, as well as some other steps.

At this point, I wish for this one specific email with the information to be moved between folders in Outlook - from the inbound folder to the processed folder.

How can I do that? Is there a way to look for the email based on time of receipt? Or does it need to be done by subject?

 

[darbid]

At this point, I wish for this one specific email with the information to be moved between folders in Outlook - from the inbound folder to the processed folder.

Ok to read the contents of you email you would have your email as an object eg myEmail. Thus you key is this below which is above in my original example

olkSource.items.Item(intIndex).Move olkDestination

I am guessing this must be pretty damm close

myEmail.Move olkDestination

How can I do that? Is there a way to look for the email based on time of receipt? Or does it need to be done by subject?

As for this I have no idea what you could use. I am not sure which version of Outlook you are on but look here at the properties of an outlook mail object.
http://msdn.microsoft.com/en-gb/library/aa210946(office.11).aspx
Go down to all the properties and see if something there helps you.

 

[Phantek]

Okay, what if I try a different tactic? What if I look for the email by its subject, and then move that specific email between folders?

I am using Office 2003.

 

[darbid]

Okay, what if I try a different tactic? What if I look for the email by its subject, and then move that specific email between folders?

I am using Office 2003.

Look at this post which shows how to find something in a folder. This is an appointment item but the same principle applies. Change to mail item and change it to the folder you are wanting to search in.
http://www.access-programmers.co.uk/forums/showpost.php?p=1000549&postcount=6

 

[Phantek]

I have this code so far. It works fine to move mail items from the inbox to a folder that is subordinate to the inbox... but I want it to move from a folder that is NOT the inbox, to another folder that is NOT subordinate. How can I go about changing the code to do that? I have tried changing the Destination folder (as an example) to "Account1\Test1" but it is unable to do so, as only the Inbox is selected.

    Dim myOlApp As New Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim myInbox As Outlook.MAPIFolder
    Dim myDestFolder As Outlook.MAPIFolder
    Dim myItems As Outlook.Items
    Dim myItem As Outlook.MailItem
 
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
 
    Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
 
    Set myItems = myInbox.Items
 
    Set myItem = myItems.Find("[Subject] = 'Test Subject'")
 
    Set myDestFolder = myInbox.Folders("Test")
 
    myItem.Move myDestFolder

 

[darbid]

There are a couple of ways to get folder, none of them easy.

First you can make the Folder Picker dialog come up and then the user can choose a folder. see here http://www.outlookcode.com/d/code/getfolder.htm

Secondly you can also work it out like this

Public Function GetFolder(strFolderPath As String) As MAPIFolder
  ' strFolderPath needs to be something like
  '   "Public Folders\All Public Folders\Company\Sales" or
  '   "Personal Folders\Inbox\My Folder"

  Dim objApp As Outlook.Application
  Dim objNS As Outlook.NameSpace
  Dim colFolders As Outlook.folders
  Dim objFolder As Outlook.MAPIFolder
  Dim arrFolders() As String
  Dim I As Long
  On Error Resume Next

  strFolderPath = Replace(strFolderPath, "/", "\")
  arrFolders() = Split(strFolderPath, "\")
  Set objApp = Application
  Set objNS = objApp.GetNamespace("MAPI")
  Set objFolder = objNS.folders.Item(arrFolders(0))
  If Not objFolder Is Nothing Then
    For I = 1 To UBound(arrFolders)
      Set colFolders = objFolder.folders
      Set objFolder = Nothing
      Set objFolder = colFolders.Item(arrFolders(I))
      If objFolder Is Nothing Then
        Exit For
      End If
    Next
  End If

  Set GetFolder = objFolder
  Set colFolders = Nothing
  Set objNS = Nothing
  Set objApp = Nothing
End Function

And then you need to call this function with the text of your folder structure. You put a \ between each level of your folders.

Set olFolder = GetFolder("Mailbox - Second Name\Sent Items")

 

[Phantek]

Thank you, once again!

I have tried what you said, but clearly I do not correctly understand how to use it. I now have:

Dim myOlApp As New Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim myInbox As Outlook.MAPIFolder
    Dim myDestFolder As Outlook.MAPIFolder
    Dim myItems As Outlook.Items
    Dim myItem As Outlook.MailItem
 
    Set myNameSpace = myOlApp.GetNamespace("MAPI")
 
    Set myInbox = GetFolder("Mailbox - Alternate Account\Submissions") 
 
    Set myItems = myInbox.Items
 
    Set myItem = myItems.Find("[Subject] = 'Test Subject'")
 
    Set myDestFolder = GetFolder("Mailbox - Alternate Account\Processed")
 
    myItem.Move myDestFolder

Unfortunately, I keep getting a "Run-time error 91: Object variable or With variable not set" on:

Set myItems = myInbox.Items

Any idea what I am doing wrong?

 

[darbid]

The getfolder function is not working. You need to step through it to see where the fault is. The fault will be because you have not put the folder structure like it is expected.

 

[Phantek]

I have stepped through it, and it seems as though no matter what I set the folder to, it will not work.

In the GetFolder function, objFolder always equals nothing - no matter what I try and set the folder value to... primary email account, secondary, main Inbox without a sub-folder, a sub-folder to the Inbox, a folder within the main email account... everything.

 

[darbid]

use the link i gave you and use that function there in case there is something different. That code definitely works, so if you cannot get it to work the problem is with your folder structure.