Solved

Outlook VBA Question about saving to second Mailbox

Posted on 2013-12-21
27
613 Views
Last Modified: 2014-01-06
I have a code that is triggered by a send event.
If the Item.to = some group then add the name to the sendonbehalfofname and if the From field contains that name save it to a different sent folder.

I am able to get this to work with the code below, but when I try and set it to a folder in a mailbox that is attached to the Mapi session it does not save it there and the item remains in the OUTBOX.

I do receive the message, but just does not move from the Outbox to the targeted dest folder.

Private Sub olSentTrg_ItemSend(ByVal Item As Object, Cancel As Boolean)

    Dim fldr As Outlook.MAPIFolder




'Step 1
'This is where the Regular Expression is challanged. Function ProcessString
'is trigger upon Sending.  This will look at both the TO field and Subject Line
'to see if there are any 5 numeric characters or more - 3 numeric characters or more
'sequence.  If this is true the next step is to see if the From field is blank

If InStr(1, Item.To, "00000-000", 1) Or InStr(1, Item.To, "XXX Managers", 1) = True Then
        If Len(Item.SentOnBehalfOfName) = 0 Then
                 If MsgBox("You are sending to a Distbuition Group and you did" & vbCrLf & _
                                "not fill in the From field with Managing Partner " & vbCrLf & vbCrLf & _
                                "Do you want to add Managing Partner" & vbCrLf & _
                                "to the From Field?", _
                                vbYesNo + vbExclamation) = vbYes Then
                                Cancel = True
           
           Item.SentOnBehalfOfName = "Managing Partner"
           
           End If
        End If
End If
    If Item.SentOnBehalfOfName = "Managing Partner" Then
        Set fldr = GetFolder("\\Mailbox - Smith\Sent Items\ManagerCopy")
        Set Item.SaveSentMessageFolder = fldr
    End If
    

End Sub

Open in new window


My goal is to have the code do something like this

    If Item.SentOnBehalfOfName = "Managing Partner" Then
        Set fldr = GetFolder("\\Mailbox -Managing Partner\Sent Items")
        Set Item.SaveSentMessageFolder = fldr
    End If

Open in new window

0
Comment
Question by:yo_bee
  • 14
  • 12
27 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Can you provide your code for the function get folder?

Chris
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 500 total points
Comment Utility
For example see olNav2folder here which does much the same as I imagine getFolder does:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_221-Outlook-folder-navigation.html

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
Chris thank you for the reply.

I used the built-in function GetFolder to look at the Sent folder of my Mailbox
and if I use Getfolder on the Managing Partner mailbox to get a item count it works, but when I try to set it as the Item.SaveSentMessageFolder  the item is never moved to the sent folder of that mailbox and the item remains in the Outbox.
 The good thing is that it does reach the recipient every time.

To your second reply I have tried the olNav2folder Function prior to even posting this questions with the same results.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
You have clearly already a lot of work regarding the problem but ...

Have you checked what you are using for the managing partner and the sentonbehalfofname property ... check case etc.

Also is your function olSentTrg_ItemSend nested from within the send event ... which I believe it needs to be in order for the sentonbehalfofname to be modifiable.

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
Sorry, I did have a Function called GetFolder.
it was taken from

http://msdn.microsoft.com/en-us/library/bb177014(v=office.12).aspx

Function GetFolder(ByVal FolderPath As String) As Outlook.Folder
    Dim TestFolder As Outlook.Folder
    Dim FoldersArray As Variant
    Dim i As Integer
        
    On Error GoTo GetFolder_Error
    If Left(FolderPath, 2) = "\\" Then
        FolderPath = Right(FolderPath, Len(FolderPath) - 2)
    End If
    'Convert folderpath to array
    FoldersArray = Split(FolderPath, "\")
    Set TestFolder = Application.Session.Folders.item(FoldersArray(0))
    If Not TestFolder Is Nothing Then
        For i = 1 To UBound(FoldersArray, 1)
            Dim SubFolders As Outlook.Folders
            Set SubFolders = TestFolder.Folders
            Set TestFolder = SubFolders.item(FoldersArray(i))
            If TestFolder Is Nothing Then
                Set GetFolder = Nothing
            End If
        Next
    End If
    'Return the TestFolder
    Set GetFolder = TestFolder
    Exit Function
        
GetFolder_Error:
    Set GetFolder = Nothing
    Exit Function
End Function

Open in new window


Note: If I use that link exact script and replace the path with the \\mailbox - managing Partner\Sent Items"
it does display the folder.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I may be missing your point but I inquired if ...

You have checked what you are using for the managing partner and the sentonbehalfofname property ... check case etc.

Also is your function olSentTrg_ItemSend nested from within the send event ... which I believe it needs to be in order for the save folder to be modifiable.

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
Not sure what you are asking regarding the Property?
Can you give me an example?
When the senditem event catches that the there is a Dist List in the TO field the various parts are triggered properly and it does ask if I want to add the Managing Partner address to the SendonBehalfof
This part of the code works perfect, but went I change the path in the Getfolder() it does not save it.  

    If Item.SentOnBehalfOfName = "Managing Partner" Then
        Set fldr = GetFolder("\\Mailbox - Smith\Sent Items\ManagerCopy")
        Set Item.SaveSentMessageFolder = fldr
    End If

Open in new window


When you say nested I should not be calling the GetFolder Function?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
What I am getting at is there is nothing that I can see that is 'wrong' in your code BUT I am concerned that when you set the sentonbehalfofname that the value stored in the email is different to that you are testing therefore for example generate an output to show for example:

MsgBox ">" & Item.SentOnBehalfOfName & "<"
    If Item.SentOnBehalfOfName = "Managing Partner" Then
        Set fldr = GetFolder("\\Mailbox - Smith\Sent Items\ManagerCopy")
        Set Item.SaveSentMessageFolder = fldr
    End If

Open in new window

0
 
LVL 21

Accepted Solution

by:
yo_bee earned 0 total points
Comment Utility
I have a solution that is working now, but it might be a bit of taking the long road to get to my next door neighbor.

So I have to Public withEvents function.
1: ItemSend
2: Itemadd

So as you saw the first class module works and places the sent Item in an alternative folder than the default Sent folder.

I added another initialize_Handler for ItemAdd.
That will move an item from that alternative folder when it is added.

This meets my needs, but It seems like I am taking the long road here.

The snippet you reference is my sent items\ManagerSent folder that works.

The \\Mailbox - Manager Partner\SentItems is the one I want to really save to.
The path I know for sure is right because was able to disable it when I ran vba item.folderpath.

Not sure why this is not working, but my work around seems to address my needs.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Can I comment that it is difficult to try and help when you do not reply ... I have twice asked for you to confirm that the actual value of Item.SentOnBehalfOfName matches to that you are expecting ... including case on the basis that in a test everything seemed to work as expected.

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
The actual value is correct:
Managing Partner XXXX XXX it is resolved by GAL without any issues.
Mailbox_Img1

Private Sub olSentTrg_ItemSend(ByVal Item As Object, Cancel As Boolean)

    Dim fldr As Outlook.MAPIFolder




'Step 1
'This is where the Regular Expression is challanged. Function ProcessString
'is trigger upon Sending.  This will look at both the TO field and Subject Line
'to see if there are any 5 numeric characters or more - 3 numeric characters or more
'sequence.  If this is true the next step is to see if the From field is blank

If InStr(1, Item.To, "00000-000", 1) Or InStr(1, Item.To, "XXX Managers", 1) = True Then
        If Len(Item.SentOnBehalfOfName) = 0 Then
                 If MsgBox("You are sending to a Distbuition Group and you did" & vbCrLf & _
                                "not fill in the From field with Managing Partner " & vbCrLf & vbCrLf & _
                                "Do you want to add Managing Partner" & vbCrLf & _
                                "to the From Field?", _
                                vbYesNo + vbExclamation) = vbYes Then
                                Cancel = True
           
           Item.SentOnBehalfOfName = "Managing Partner XX XXXX"
           
           End If
        End If
End If
    If Item.SentOnBehalfOfName = "Managing Partner XX XXXX" Then
        Set fldr = GetFolder("\\Mailbox - XXXXXXXX, Michael K.\Sent Items\ManagerCopy")
        Set Item.SaveSentMessageFolder = fldr
    End If
    

End Sub 

Open in new window


Mailbox_img2

The Code above works perfectly.  The sent item is placed in the FLDR Folderpath without any issues and I am able to reply back to Managing Partner XX XXXX as if it was sent from that Managing Partner Mailbox.

If I change the path the Managing Partner Sent Folder the item never is placed in the sent folder of the mailbox and the copy remains in the Outbox.
    If Item.SentOnBehalfOfName = "Managing Partner XX XXXX" Then
        Set fldr = GetFolder("\\Mailbox - Managing Partner XX XXXX\Sent Items")
        Set Item.SaveSentMessageFolder = fldr
    End If

Open in new window


Note sure if this answers your question. Please let me know
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
The graphic shows the PST as "Mailbox - Managing Partner" but what I was interested to see was what is actually stored in the email for the managing partner i.e. the code I posted earlier replacing:

    If Item.SentOnBehalfOfName = "Managing Partner XX XXXX" Then
        Set fldr = GetFolder("\\Mailbox - Managing Partner XX XXXX\Sent Items")
        Set Item.SaveSentMessageFolder = fldr
    End If

Open in new window


with

MsgBox ">" & Item.SentOnBehalfOfName & "<"
    If Item.SentOnBehalfOfName = "Managing Partner XX XXXX" Then
        Set fldr = GetFolder("\\Mailbox - Managing Partner XX XXXX\Sent Items")
        Set Item.SaveSentMessageFolder = fldr
    End If

Open in new window


i.e. what exactly does the msgbox show?

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
Debug.Print Item.SentOnBehalfOfName
Results:
Managing Partner XX XXXX

Does that Help.


As stated before this If Then Statement works if I save to a folder within the actual senders mailbox, but does not save if I set it to the Second Mailbox Store that is mounted.

I can try the PickFolder method and see if that works.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
I need to try and set up a representative test ... so are you calling your olSentTrg_ItemSend routine from inside the application_itemsend function?

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
I have it initialized at startup.

Class Module
Public WithEvents olSentTrg As Outlook.Application


Sub Initialize_handler()
 
 Set olSentTrg = Outlook.Application
 
 
End Sub

Private Sub olSentTrg_ItemSend(ByVal Item As Object, Cancel As Boolean)

    Dim fldr As Outlook.MAPIFolder




'Step 1
'This is where the Regular Expression is challanged. Function ProcessString
'is trigger upon Sending.  This will look at both the TO field and Subject Line
'to see if there are any 5 numeric characters or more - 3 numeric characters or more
'sequence.  If this is true the next step is to see if the From field is blank

If InStr(1, Item.To, "<Replace with Dist List Name", 1) = True Then
        If Len(Item.SentOnBehalfOfName) = 0 Then
                 If MsgBox("You are sending to a Distbuition Group and you did" & vbCrLf & _
                                "not fill in the From field with Managing Partner " & vbCrLf & vbCrLf & _
                                "Do you want to add Managing Partner" & vbCrLf & _
                                "to the From Field?", _
                                vbYesNo + vbExclamation) = vbYes Then
                                Cancel = True
           
           Item.SentOnBehalfOfName = "<Replace with Senders name>"
           
           End If
        End If
End If
    If Item.SentOnBehalfOfName = "<Replace with Senders name>" Then
        Set fldr = GetFolder("\\Mailbox - <"replace with mailbox name">\Sent Items\ManagerCopy")
        Set Item.SaveSentMessageFolder = fldr
        Debug.Print ">" & Item.SentOnBehalfOfName & "<"
    End If
    

End Sub

Open in new window


Module Getfolder
Function GetFolder(ByVal FolderPath As String) As Outlook.folder
    Dim TestFolder As Outlook.folder
    Dim FoldersArray As Variant
    Dim i As Integer
        
    On Error GoTo GetFolder_Error
    If Left(FolderPath, 2) = "\\" Then
        FolderPath = Right(FolderPath, Len(FolderPath) - 2)
    End If
    'Convert folderpath to array
    FoldersArray = Split(FolderPath, "\")
    Set TestFolder = Application.Session.folders.Item(FoldersArray(0))
    If Not TestFolder Is Nothing Then
        For i = 1 To UBound(FoldersArray, 1)
            Dim SubFolders As Outlook.folders
            Set SubFolders = TestFolder.folders
            Set TestFolder = SubFolders.Item(FoldersArray(i))
            If TestFolder Is Nothing Then
                Set GetFolder = Nothing
            End If
        Next
    End If
    'Return the TestFolder
    Set GetFolder = TestFolder
    Exit Function
        
GetFolder_Error:
    Set GetFolder = Nothing
    Exit Function
End Function

Open in new window

0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
I just tried using Pickfolder and debug.print the output.
The path is correct and the same results.

Maybe there is some sort of security on the mailbox.
I am able to move it without any issues, but not able to direct the sent item to be saved in the desired location.

For now I will be using the Save locally and have the ItemAdd event move items when they arrive.
Like said earlier it seems like I am taking the long road here.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Can you confirm the calling process for your sub?
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
Are you talking about the get folder  function
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
No, the main sub i.e. olSentTrg_ItemSend, how is this triggered?

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
It is called upon the startup when Outlook is opened

The Class is initialized during startup of Outlook.
Placed in the ThisOutlookSession
Dim mAddItems As New Class3
Dim mReminder As New Class1
Dim mDistGroup As New Class4
Dim mItemAdd As New Class2


Private Sub Application_startup()
mAddItems.Initialize_handler
mReminder.Initialize_handler
mDistGroup.Initialize_handler
mItemAdd.Initialize_handler
End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
No, I am referring to the routine you initially posted i.e olSentTrg_ItemSend.  HOw / where is this routine triggered?

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
its SendItem Event
http://msdn.microsoft.com/en-us/library/office/aa280532(v=office.11).aspx

How it works is when The Send event happens it is caught prior to sending and allows you to run various steps prior to sending.

http://msdn.microsoft.com/en-us/library/office/aa171284(v=office.11).aspx

ie.
Say you want to have a certain message appended to all e-mails.
You can use the ItemSend Event to catch the e-mail parse the body and append to the message body then have it send out or do something else.

I just placed it in its own class and initialized it upon startup.

If you placed the a sub in THISOUTLOOKSESSSION it will do the same thing as the ITEMSend link appove, but just rather than using all the other lines all you need is

Private Sub application_ItemSend(ByVal Item As Object, Cancel As Boolean)
	Dim prompt As String
	prompt = "Are you sure you want to send " & Item.Subject & "?"
	If MsgBox(prompt, vbYesNo + vbQuestion, "Sample") = vbNo Then
		Cancel = True
	End If
End Sub

Open in new window



It is the same thing as my class3 being initialized and then anytime there is a Send Event this is called  olSentTrg

Does that help in anyway.
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
To help lay this out,
ThisOutlookSession
ThisOutlookSessionHere is the Class
Class1
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
Ah,

I would expect you to use the itemsend event:

application_itemsend and either place your code therein or better place a call to your own routine therein passing the itemsend properties down.

Temporarily disable the code for the class and you should see it work.

Chris
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
I wi give that a shot later this week.
0
 
LVL 21

Author Closing Comment

by:yo_bee
Comment Utility
I am closing this question and awarding you the full points because your suggested a solution that was partial used  to accomplish the results I was looking for.

I also selected my solution as part of the resolution.

Happy New Year
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Email signatures have numerous marketing benefits. Here are 8 top reasons to turn your email signature into a marketing channel.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now