Solved

MsgBox in a Outlook Macro

Posted on 2014-03-21
10
380 Views
Last Modified: 2014-03-21
This goes along with another question I asked on here and got the answer to. I would like to add a message box or prompt to signify that the Macro is complete. As it is now, it just sits there and the only way I know its doing something is if my inbox count goes down. I would like a box to pop up and say the process is complete. However, when I put the message box code at the end I get compile errors.

Here is my code:

Sub MoveAgedMail()

'adjust mailboxNameString to match your name - it will be the way it displays in outlook
    mailboxNameString = "email@test.com"

'Check to make sure variable stored string correctly.  Opportunity to kill script before it starts.
    MsgBox mailboxNameString
    
'Define outlook objects:
    Dim objOutlook As Outlook.Application
    Dim objNameSpace As Outlook.NameSpace
    Dim objSourceFolder As Outlook.MAPIFolder
    Dim obDestFolder As Outlook.MAPIFolder
    Dim objVariant As Variant

'Define Counters

    'Used for loop to comb through Inbox folder
    Dim intCount As Integer

    'Different ways to do with logic I did it quick and messy without it will make a copy in original folder and then also another folder that you set for all older mail if you setup similarly.
    Dim moveOnce As Integer
             
    ' Set variable values
    Set objOutlook = Application
    Set objNameSpace = objOutlook.GetNamespace("MAPI")
    Set objSourceFolder = objNameSpace.GetDefaultFolder(olFolderInbox)
             
    'Start loop to loop through each item in the inbox
    For intCount = objSourceFolder.Items.Count To 1 Step -1
    'This defines the variable as the mail item you are currently on in the loop which you can then manipulate, move or apply logic to.
        Set objVariant = objSourceFolder.Items.Item(intCount)

        DoEvents
        'Set move counter to 0 so it will reset upon every loop, it will add 1 if its processed by the logic and at the end if it had been passed by the custom folder rules it will move to the catch Older than a Day folder.
        'NOTE: Older than a day folder is completely optional you can leave out and items that dont match any othe folder / sender criteria will just remain in inbox.  Completely up to you.
        moveOnce = 0
        
        'Used for testing - Used to make sure that the variable was resetting.
        'MsgBox "Move Once = " & moveOnce
        
        'If object is a mail item Then...
        If objVariant.Class = olMail Then
            'Perform logic to determine difference between NOW and the date of the item.
            intDateDiff = DateDiff("d", objVariant.SentOn, Now)
            'If Date is older than 0 days (or today) then apply the following logic
            If intDateDiff > 0 Then
                'Define Sender of item
                strSender = objVariant.SenderName
               
                'IF Certain Sender then send to a folder
                'NOTE: Please check script description for a logical example to follow along with

'Move items older than a day to standalone folder to keep e-mails organized

'Example 1
                If strSender = "Voicemail Sender" Then
                    Set obDestFolder = objNameSpace.Folders(mailboxNameString).Folders("Inbox").Folders("Voicemail")
                    objVariant.Move obDestFolder
               'Add move counter so at the end item wont be moved to two locations
                    moveOnce = 1
                End If

            End If
        End If
    
    Next
    Exit Sub
End Sub

Open in new window

0
Comment
Question by:Zac Harris
  • 4
  • 4
  • 2
10 Comments
 
LVL 19

Accepted Solution

by:
Ken Butters earned 267 total points
ID: 39945210
On the 2nd to last line, you don't need an Exit Sub right before an End Sub.

Just replace Exit Sub with Msgbox "Process Complete" or whatever message you are wanting to display.

If you get compile errors doing this, then please post the code with the compile error.
0
 
LVL 24

Assisted Solution

by:-MAS
-MAS earned 133 total points
ID: 39945223
Firts of all it is always recommended to use error handler in your codes.
Please try the below code


'Example 1
                If strSender = "Voicemail Sender" Then
                    Set obDestFolder = objNameSpace.Folders(mailboxNameString).Folders("Inbox").Folders("Voicemail")
                    objVariant.Move obDestFolder
               'Add move counter so at the end item wont be moved to two locations
                    moveOnce = 1
                End If

            End If
        End If
   
    Next
MsgBox "Process completed successfully"
' you can customize the message box
    Exit Sub
On Error GoTo ErrorHandler
Error_exporttoexcel:
MsgBox "An error occurred "
'or use this to see the error not both
MsgBox Err & ": " & Error(Err)


End Sub
0
 
LVL 19

Assisted Solution

by:Ken Butters
Ken Butters earned 267 total points
ID: 39945235
@MAS

You cannot do error handling that way.  If you want to handle an error, your On Error GoTo ErrorHandler statement must be somewhere higher up in the code prior to where the error might occur.

Coding your "On Error" statement at the bottom of the routine will never trap anything.

Think of it like a try catch construct like you would use in C++ or Java.  It must precede the code you want to catch the error in.

Other error is that your error label is incorrect. You specified the label in the on Error statement as "ErrorHandler"... but then your defined label was "Error_exporttoexcel".

if you have an On Error Goto statement... in this case "ErrorHandler"... then you must have a label for it to goto in case of error like this for your example:

"ErrorHandler:"
0
 
LVL 24

Expert Comment

by:-MAS
ID: 39945270
Apologize for the mistake
I was busy in my work simply copied from my code.

@Ken Butters
Thanks for pointing my mistake.
I guess below code is correct.



Sub MoveAgedMail()

'adjust mailboxNameString to match your name - it will be the way it displays in outlook
    mailboxNameString = "email@test.com"

'Check to make sure variable stored string correctly.  Opportunity to kill script before it starts.
    MsgBox mailboxNameString
   
'Define outlook objects:
    Dim objOutlook As Outlook.Application
    Dim objNameSpace As Outlook.NameSpace
    Dim objSourceFolder As Outlook.MAPIFolder
    Dim obDestFolder As Outlook.MAPIFolder
    Dim objVariant As Variant


On Error GoTo ErrorHandler


'Define Counters

    'Used for loop to comb through Inbox folder
    Dim intCount As Integer

    'Different ways to do with logic I did it quick and messy without it will make a copy in original folder and then also another folder that you set for all older mail if you setup similarly.
    Dim moveOnce As Integer
             
    ' Set variable values
    Set objOutlook = Application
    Set objNameSpace = objOutlook.GetNamespace("MAPI")
    Set objSourceFolder = objNameSpace.GetDefaultFolder(olFolderInbox)
             
    'Start loop to loop through each item in the inbox
    For intCount = objSourceFolder.Items.Count To 1 Step -1
    'This defines the variable as the mail item you are currently on in the loop which you can then manipulate, move or apply logic to.
        Set objVariant = objSourceFolder.Items.Item(intCount)

        DoEvents
        'Set move counter to 0 so it will reset upon every loop, it will add 1 if its processed by the logic and at the end if it had been passed by the custom folder rules it will move to the catch Older than a Day folder.
        'NOTE: Older than a day folder is completely optional you can leave out and items that dont match any othe folder / sender criteria will just remain in inbox.  Completely up to you.
        moveOnce = 0
       
        'Used for testing - Used to make sure that the variable was resetting.
        'MsgBox "Move Once = " & moveOnce
       
        'If object is a mail item Then...
        If objVariant.Class = olMail Then
            'Perform logic to determine difference between NOW and the date of the item.
            intDateDiff = DateDiff("d", objVariant.SentOn, Now)
            'If Date is older than 0 days (or today) then apply the following logic
            If intDateDiff > 0 Then
                'Define Sender of item
                strSender = objVariant.SenderName
               
                'IF Certain Sender then send to a folder
                'NOTE: Please check script description for a logical example to follow along with

'Move items older than a day to standalone folder to keep e-mails organized

'Example 1
                If strSender = "Voicemail Sender" Then
                    Set obDestFolder = objNameSpace.Folders(mailboxNameString).Folders("Inbox").Folders("Voicemail")
                    objVariant.Move obDestFolder
               'Add move counter so at the end item wont be moved to two locations
                    moveOnce = 1
                End If

            End If
        End If
   
    Next
MsgBox "Process completed successfully"
' you can customize the message box
    Exit Sub
ErrorHandler:
MsgBox "An error occurred "
'or use the below to see the error dont use both
MsgBox Err & ": " & Error(Err)
End Sub
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39945286
@MAS --- yes much better.  Looks like that error routine would work now.

Just one other tip... when you post that much code, it is a good idea to surround it with code tags like the original poster did.  it is much easier to read that way.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 24

Expert Comment

by:-MAS
ID: 39945295
Honestly I dont know how to embed the code
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39945314
You highlight the code, and the click on the code button you see in the menu bar of experts-exchange "Post a Comment" window... it is similar to how you would accent with bold/italics/underline etc.

It will look similar to this after you do this...   <code>Some code</code>
The only difference is that instead of angle brackets <code> the word code will be surrounded with square brackets [].  I could not use square brackets to show you otherwise all you would see is embedded code.

You can either use the button to insert the tags, or you can manually type them in yourself.  Very similar to html or xml markup.
0
 
LVL 14

Author Comment

by:Zac Harris
ID: 39945325
@MAS

When you want to enter code just click the "Code" button on the toolbar of the "Post a Comment" section. Another way is to highlight your code then click the same "Code" button.
0
 
LVL 14

Author Closing Comment

by:Zac Harris
ID: 39945338
Ken Butters,

Thanks for your input on that. I was wondering where the label was for the ErrorHandling section of the code. I added it in place of the Exporttoexcel. I wouldn't want to export anything anyways as this macro is for my machine and I'd rather see the error on screen.

Thanks to all who helped!
0
 
LVL 24

Expert Comment

by:-MAS
ID: 39945392
Thanks to both and apologize for the mistake.
Thanks for the point and glad to know it helped.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
If you don't know how to downgrade, my instructions below should be helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

706 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

21 Experts available now in Live!

Get 1:1 Help Now