Clicky

We need have the user know the last email that has been received that has a specific value.  Is there a way to go about in a VBA script?

asked 12/17/2011 12:23

Ramante's gravatar image

Ramante ♦♦


11 Answers:
You can create a search folder for that purpose, but that will then show all fitting mails. With the proper order the latest (most recent) one is at the top.

There are a lot of other options, like marking the mail by VBA code on receive, or the like. What do you want to see exactly? Would it be sufficient to have a button doing the search and display on demand?
link

answered

Qlemo's gravatar image

Qlemo

We could check for the string in each mail as received and update each time ... Question is how do want to view the mail I.e. we could save a copy each time deleting the old one in a specific folder
link

answered 2011-12-17 at 09:27:35

chris_bottomley's gravatar image

chris_bottomley

Something like is what we are searching for.

To be exact, what we want is to have a button where when click it will display by a MsgBox the last email received that email of the sender has "MCAGROUP".
link

answered 2011-12-17 at 10:27:44

Ramante's gravatar image

Ramante

Do you insist of a message box? I would instead just open the email itself.

And what exactly do you mean with "of the sender has "MCAGROUP""? Do you mean it is from a specific person, or company, both reflected by the sender's email address?

Further, is the email to be searched for only in one specific folder, say Inbox, or could it have been moved, and we need to search in several folders?
link

answered 2011-12-17 at 14:32:54

Qlemo's gravatar image

Qlemo

Here the answers:

    - Do you insist of a message box? I would instead just open the email itself.
      Answ: Yes.  That could be it; always filtering the emails by "MCAGROUP"

    - And what exactly do you mean with "of the sender has "MCAGROUP""? Do you mean it
      is from a specific person, or company, both reflected by the sender's email address?
      Answ: we mean that we need to view all senders emails address and choose the
      most recent email that the email has within its addres "MCAGROUP".  For example,
      "MCAGROUP@domain.com or RoyMCagroup@domain.com"


    - Further, is the email to be searched for only in one specific folder, say Inbox, or
      could it have been moved, and we need to search in several folders?
      Answ: Yes, only inbox

Hope this helps, help us.

Thanx in advance
   
link

answered 2011-12-17 at 16:10:00

Ramante's gravatar image

Ramante

1. load the code below into a normal module and then during stage 2 use the sub mcagroupFilter to open the most recent email where the sender address contains the key phrase mcagroupFilter.

2. To add the button, see the following from:
http://www.howto-outlook.com/howto/macrobutton.htm

Create a button on a Toolbar
Adding a button to a toolbar can be done in Outlook 2003 and previous or in the main window of Outlook 2007.
As stated earlier, note that when you are using Outlook 2003 or earlier and are using Word as the e-mail editor, the macro has to be created in Word when you want to have it available in the compose message window. Other than that, the instructions to create the macro button are exactly the same.

First put the Toolbar in Edit Mode;
View-> Toolbars-> Customize…
Verify that the Commands tab is selected.
In the Categories list on the right, select Macros.
The Commands list on the right will now display all you macros
Drag and drop the macro that you wish to create a button for to the location on the toolbar where you want it.
To modify the name and icon, right click the icon on the Toolbar;
You can edit the name to something more meaningful.
If you want to use an ALT+key shortcut for the button, place an & for the letter that you want to use.
For instance, let’s assume you call it “My Button” and want to use the ALT+Y combination.
You’d then write “M&y Button”
You can change the button image via the Change Button Image selector or free hand via the Edit Button Image… dialog.
In the free hand editor, you can actually paste another image or icon you have copied from via CTRL+V.
You can also disable the text or button or image if you don’t like to see it.
Close the Customize dialog to leave Edit Mode of your Toolbars and use your button.

Chris
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
Sub mcagroupFilter()
Dim olFolder As Object
Dim filteredItems As Object
Dim folderItems As Object
Dim sortedItems As Object
Dim strFilter As String

    Set olFolder = Application.Session.GetDefaultFolder(olFolderInbox)
    Set folderItems = olFolder.Items
    strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:fromemail" & Chr(34) & " like " & "'%" & "mcagroup" & "%'"
    Set filteredItems = olFolder.Items.Restrict(strFilter)
    
    filteredItems.Sort "receivedtime", True
    If filteredItems.Count = 0 Then
        MsgBox "No such ail found"
    Else
        filteredItems(1).GetInspector.Activate
    End If

End Sub
link

answered 2011-12-17 at 16:36:34

chris_bottomley's gravatar image

chris_bottomley

It worked perfectly!!!

Please give us a brief explanation the following lines for our benefit:

    -> strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:fromemail" & Chr(34)
                     & " like " & "'%" & "mcagroup" & "%'"
    -> Set filteredItems = olFolder.Items.Restrict(strFilter)
    -> filteredItems.Sort "receivedtime", True
link

answered 2011-12-18 at 00:57:19

Ramante's gravatar image

Ramante

-> strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:fromemail" & Chr(34)
                     & " like " & "'%" & "mcagroup" & "%'"
Creates a string to use in the restriction
It is called a DASL query and is what outlook uses when you set up a filter on a folder using the application filter option.
The leading and trailing % signs act as wildcards for DASL filters so in normal *mcagroup*
The rest is simply the required syntax

-> Set filteredItems = olFolder.Items.Restrict(strFilter)
Takes the string and in this case process the inbox, (olfolder) items to return only those with mcagroup in the addy

-> filteredItems.Sort "receivedtime", True
SImply sorts the collection of mcagroup senders according to receiver date so that we know exactly which is the newest

Chris
link

answered 2011-12-18 at 01:14:31

chris_bottomley's gravatar image

chris_bottomley

BTW, the line:

    Set folderItems = olFolder.Items

was left over from a bit of cut and paste on another question so is not needed here ... as you might already have realised.

Chris
link

answered 2011-12-18 at 02:42:34

chris_bottomley's gravatar image

chris_bottomley

Thank you very much!

Please see the EE link below, it's a question placed that maybe it can be solved similar how you assisted us here.

http://www.qa.downappz.com/Software/Office_Productivity/Groupware/Outlook/Q_27499402.html

We'll proceed to close the question.
link

answered 2011-12-18 at 02:44:41

Ramante's gravatar image

Ramante

Thax
link

answered 2011-12-18 at 02:52:34

Ramante's gravatar image

Ramante

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×6

Asked: 12/17/2011 12:23

Seen: 344 times

Last updated: 12/17/2011 06:53