• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1431
  • Last Modified:

Need help with VB Macro Else IF

I would like for the attached code's Else If statement to work.  When I use the top IF statement by itself, then it works.

See the code:
Private WithEvents olkSentItems As Outlook.Items
 
Private Sub Application_Quit()
    Set olkSentItems = Nothing
End Sub
 
Private Sub Application_StartupCampbells()
    Set olkSentItemsCambpbells = Session.GetDefaultFolder(olFolderSentMail).Items
End Sub
 
Private Sub olkSentItemsCampbells_ItemAdd(ByVal Item As Object)
    Dim olkFolder As Outlook.MAPIFolder
    If Item.Class = olMail Then
        If Item.SenderName = "Campbells" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - Campbells\Sent Items")
            Item.Move olkFolder
        ElseIf Item.SenderName = "TacoCabana" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - TacoCabana\Sent Items")
            Item.Move olkFolder
        ElseIf Item.SenderName = "McCain" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - McCain\Sent Items")
            Item.Move olkFolder
        ElseIf Item.SenderName = "McCormick" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - McCormick\Sent Items")
            Item.Move olkFolder
        End If
    End If
    Set olkFolder = Nothing
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

Open in new window

0
bbanis2k
Asked:
bbanis2k
  • 7
  • 7
  • 2
1 Solution
 
Tristan ShortlandCommented:
There doesn't appear to be anything wrong with the syntax, although it maybe easier to use a select case:

Select Case Item.SenderName
    Case "A"
        Do something....
    Case "B"
        Do something...
End Select

Have you run the code to check that Item.SenderName contains one of the values, you may need an else in case it doesn't...
0
 
bbanis2kAuthor Commented:
It gives me a bunch of errors when I try "Select Case" and "Case"

Maybe I am typing in the syntax wrong...
0
 
Arthur_WoodCommented:
This block:
       If Item.SenderName = "Campbells" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - Campbells\Sent Items")
            Item.Move olkFolder
        ElseIf Item.SenderName = "TacoCabana" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - TacoCabana\Sent Items")
            Item.Move olkFolder
        ElseIf Item.SenderName = "McCain" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - McCain\Sent Items")
            Item.Move olkFolder
        ElseIf Item.SenderName = "McCormick" Then
            Set olkFolder = OpenOutlookFolder("Mailbox - McCormick\Sent Items")
            Item.Move olkFolder
        End If

would be (as Select Case ....)

       Select Case SenderName 
          Case "Campbells" 
            Set olkFolder = OpenOutlookFolder("Mailbox - Campbells\Sent Items")
            Item.Move olkFolder
        Case "TacoCabana"
            Set olkFolder = OpenOutlookFolder("Mailbox - TacoCabana\Sent Items")
            Item.Move olkFolder
        Case "McCain" 
            Set olkFolder = OpenOutlookFolder("Mailbox - McCain\Sent Items")
            Item.Move olkFolder
        Case "McCormick" 
            Set olkFolder = OpenOutlookFolder("Mailbox - McCormick\Sent Items")
            Item.Move olkFolder
        End Select

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Arthur_WoodCommented:
This could also be simplified as (with no Select Case bloc at all, just these two lines to completely replace the If..ElseIf..End If block
set olkFolder = Set olkFolder = OpenOutlookFolder("Mailbox - " & SenderName & "\Sent Items")
            Item.Move olkFolder

 
AW
0
 
Tristan ShortlandCommented:
Or

Set olkFolder = OpenOutlookFolder("Mailbox - " & Item.SenderName & "\Sent Items")
Item.Move olkFolder
0
 
bbanis2kAuthor Commented:
I did the code as below, and it gives me the error: "Cannot Move Items".  The debugger highlights the ITEM.MOVE olkfolder.  

Private Sub olkSentItems_ItemAdd(ByVal Item As Object)
    Dim olkFolder As Outlook.MAPIFolder
        Set olkFolder = OpenOutlookFolder("Mailbox - " & SenderName & "\Sent Items")
        Item.Move olkFolder
    Set olkFolder = Nothing
End Sub
0
 
Arthur_WoodCommented:
Change you code to:
Private Sub olkSentItems_ItemAdd(ByVal Item As Object)
    If Item.Class = olMail Then
        Dim olkFolder As Outlook.MAPIFolder
           Set olkFolder = OpenOutlookFolder("Mailbox - " & Item.SenderName & "\Sent Items")
           Item.Move olkFolder
        Set olkFolder = Nothing
    End If
End Sub

Open in new window

0
 
bbanis2kAuthor Commented:
That worked!!  You are awesome!


0
 
bbanis2kAuthor Commented:
This EXPERT is golden.
0
 
Arthur_WoodCommented:
Glad to be of assistance, and thank you for the compliment.
 
AW
0
 
bbanis2kAuthor Commented:
Do you know what kind of certificate I could sign this Macro with?  Would I be able to get a cheap cert from GoDaddy?
0
 
Arthur_WoodCommented:
where are you using this?  The original question just indicarted Visual Basic Programming as the zone.  Is this code being used in another application - I would assume Outlook from the use of email and folders???
You do not usually sign a single module.  What are you trying to accomplish with the certificate?
0
 
bbanis2kAuthor Commented:
I am going to use this in Outlook.  I can associate a certificate with this Project/Module and I was just needing to know the specifications.
0
 
Arthur_WoodCommented:
If you go to Start>All Programs>Microsoft Office   do you have a choice for Microsoft Office Tools?
If Yes, then you should have a choice there to create your own digital certificate, which you can then use to Sign your module.  You would then provide that same certificate to users who would install that certificate.
I have not used Digital Certifactes for about 5 years, so I am a bit rusty.
0
 
bbanis2kAuthor Commented:
That works perfectly!  Thanks again!
0
 
Arthur_WoodCommented:
Again- glad to be of assistance.
 
AW
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now