Solved

Outlook 2007 Rules and VBA - How to run a script on a ReportItem

Posted on 2014-02-23
17
1,171 Views
Last Modified: 2014-02-25
In Outlook 2007, I have a VBA script that is used in a rule that runs on all messages when they arrive. It works fine on normal messages (MailItem), but is not even invoked on a report message (ReportItem). For example, let's say this is the script:

Sub DisplaySubject (MyMail as MailItem)
Dim strSubject = MyMail.Subject
MsgBox strSubject
End Sub

And let's say the only action in the rule is "run a script" and it is the script above. Problem is, it doesn't run on report messages. How would I get a script like the above to run on report items as well as mail items? Please provide detailed instructions/code, as my Outlook VBA skills (in fact, all forms of VB) are modest. Thanks very much, Joe
0
Comment
Question by:Joe Winograd, EE MVE
  • 9
  • 7
17 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39882264
Hi,

pls try
Sub RunDisplaSubject(ByVal myItem As Object)
    On Error Resume Next
    If TypeName(myItem) = "MailItem" Or TypeName(myItem) = "ReportItem" Then
	On Error goto 0
        DisplaySubject myItem
    End If
    On Error goto 0
End Sub

Sub DisplaySubject (MyMail as Object)
    Dim strSubject = MyMail.Subject
    MsgBox strSubject
End Sub

Open in new window

Or
Sub DisplaySubject (MyItem as Object)
    On Error Resume Next
    If TypeName(myItem) = "MailItem" Or TypeName(myItem) = "ReportItem" Then
    Dim strSubject = MyItem.Subject
    MsgBox strSubject
    End If
    On Error goto 0

End Sub

Open in new window

Regards
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39882453
Hi, Joe.

I'm afraid this isn't possible in conjunction with a rule.  Rules do not fire for report items.  They only fire for messages and meeting requests.  There are other ways to accomplish this though that don't require a rule.  If you're interested in that kind of solution, then I can help.
0
 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39882883
Hi Rgonzo1971,
Thanks for the ideas. Unfortunately, none of them produces a MsgBox when I create a rule with those scripts. I tried RunDisplaSubject (btw, did you really mean "Displa" or should it be "Display") and DisplaySubject with the first set of code, and DisplaySubject with the second set of code. After creating each rule (one at a time), I told it to run the script on messages already in the folder. None  of the scripts displayed a MsgBox. Also, both sets of code produce a red line of text...does that mean a problem? Here are screenshots:

Rgonzo first codeRgonzo second codeDoes any of this code work on your computer? Thanks, Joe
0
 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39882924
Hi Lee,
I don't mind if it's not a rule...I'm interested in any solution that will write a plain text file with the EntryID, Sender, Subject, and Body for all normal messages and report messages that arrive. My current code that accesses MyMail.EntryID, MyMail.SenderEmailAddress, MyMail.Subject, and MyMail.Body (where MyMail As MailItem) works perfectly on normal messages but doesn't fire on report messages (the rest of the code simply writes a plain text file with a file name of the EntryID and a file type of TXT, such as 00000000B5C08539BEF98C42B99342E6CEA9BEFB84012000.txt). How can I accomplish this without a rule? Thanks much, Joe
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 39883631
Joe,

Here's the code you need.  This code must go in the ThisOutlookSession module.  I'm not clear on whether you need the code that writes the data you want to a file, or if you just need the code that emulates a rule and captures both messages and reports.  The code below is the latter.  If you need me to add the code that writes the data to a file, then let me know and I'll add it.  If you already have that code, then insert it after my comment of "Your code goes here".

Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
    Dim arrEID As Variant, _
        varEID As Variant, _
        olkItm As Object
    arrEID = Split(EntryIDCollection, ",")
    For Each varEID In arrEID
        Set olkItm = Session.GetItemFromID(varEID)
        Select Case olkItm.Class
            Case olMail, olReport
                'Your code goes here
        End Select
    Next
    Set olkItm = Nothing
End Sub

Open in new window

0
 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39883668
Hi Lee,
Here's my code snippet that writes the TXT file:

strID = MyMail.EntryID
strSender = MyMail.SenderEmailAddress
strSubject = MyMail.Subject
strBody = MyMail.Body
Set oShell = CreateObject("WScript.Shell")
strAppData = oShell.ExpandEnvironmentStrings("%APPDATA%")
strTXTfile = strAppData & "\mail\" & strID & ".txt"
Open strTXTfile For Append As 1
Print #1, strID
Print #1, strSender
Print #1, strSubject
Print #1, strBody
Close #1

Open in new window

Will this work as-is if inserted after "Your code goes here" comment or does it need changes? Thanks, Joe
0
 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39883795
Lee,
Before even worrying about my last question, here's an issue. I simply put a MsgBox statement in place of your comment. I then cleared everything out of ThisOutlookSession and removed all of the other Modules, so the VB environment was clean. I then copied/pasted the code into ThisOutlookSession and went to create a rule using it. But it doesn't even show up when I click the "a script" phrase in the Rules Wizard — the Select Script window is empty. So I figured maybe it's a macro, but when I went to Tools>Macro>Macros (Alt-F8) the Macros window is also empty. How do I invoke your code? Thanks, Joe
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39883975
Joe,  

The code runs automatically.  You can't run it and as I'd already said, you cannot do this with a rule.  With the code in place, send yourself a message, or have someone send you a message.  That will trigger the code and display the message box you added.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39884088
> The code runs automatically.

Indeed, it does! I'm not able to test it right now on a report message, but it worked fine on a normal email. So then I replaced the MsgBox with my 13 lines of code above and VB gives an error dialog with:

Run-time error '424':

Object required

What's wrong with my code? Thanks, Joe
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39884217
You need to change MyMail to match the name of the object I used (i.e. olkItm), or change the name of my object (i.e. olkItm) to MyMail.  Either one will do.
0
 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39884333
Ah, of course! Sorry for the newbie error. I'll try it in a moment with the fix.

Based on some research I've done, ReportItem does not have a SenderEmailAddress property (MailItem does). Do you know what will be returned in olkItm.SenderEmailAddress when a ReportItem is received? Also, so I can test code, how would I send a "report" email rather than a normal email? Thanks for all your help on this — I really appreciate it.
0
 
LVL 76

Assisted Solution

by:David Lee
David Lee earned 500 total points
ID: 39884582
Joe,

ReportItem objects don't have a SenderEmailAddress property.  The only solution I know of is to use Outlook's PropertyAccessor object to read the sender address from the unexposed properties of the ReportItem object.  To do that, add this code to what you already have.

Private Function GetSenderAddress(olkMsg As Object) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    GetSenderAddress = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C1F001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Open in new window


Once you've done that, change line #2 of your code from

strSender = MyMail.SenderEmailAddress

Open in new window


to

strSender = SenderEmailAddress(MyMail)

Open in new window


The only way I know of to get a ReportItem object is to send someone an email and request a read receipt.  When they read the message, Outlook will send you a receipt.  That receipt will be a ReportItem.
0
 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39885919
Hi Lee,
I'm sure you meant:

strSender = GetSenderAddress(MyMail)

And after changing it to that, everything works a charm! Also, the read receipt idea works perfectly! Thanks for all of your excellent work on this...everything is hunky-dory now! Regards, Joe
0
 
LVL 51

Author Closing Comment

by:Joe Winograd, EE MVE
ID: 39885938
Awesome solution...and he stuck with me persistently during some rookie errors! Very much appreciated!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39886790
Yes, that's what I meant.  Sorry, sometimes my mind takes unscheduled vacations.  

Glad you like the solution.  Cheers!
0
 
LVL 51

Author Comment

by:Joe Winograd, EE MVE
ID: 39887520
>  sometimes my mind takes unscheduled vacations

I'll be stealing that phrase from you! :)
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39887706
LOL.  You're welcome to it.
0

Featured Post

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.

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

18 Experts available now in Live!

Get 1:1 Help Now