Scripting Outlook with VBS: harder than it should be



  • Since Outlook's built-in filters are shit, I decided to script the filter I wanted in VBS. That was probably my first mistake.

    Basically: for all messages in inbox matching a given subject and sender_email, do stuff with them, and then delete them permanently.

    After finding a lot of ways not to do it, this actually works:

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Outlook = CreateObject("Outlook.Application")
    Set Session = Outlook.GetNamespace("MAPI")
    Session.LogOn
    
    Set SubjectPattern = new RegExp
    SubjectPattern.Pattern = "^initech$"
    SubjectPattern.IgnoreCase = True
    
    Set SenderPattern = new RegExp
    SenderPattern.Pattern = "^anonymous@initech\.org$"
    SenderPattern.IgnoreCase = True
    
    Set Inbox = Session.GetDefaultFolder(6) '6 = Inbox, naturally
    Set Trash = Session.GetDefaultFolder(3) '3 = Deleted Items, naturally
    
    For Each Msg In Inbox.Items
      If Msg.Class = 43 Then  '43 = olMail, naturally (that's a MailItem object)
        If Msg.Sender.AddressEntryUserType <> 0 Then
          Sender = Msg.Sender.Address
        Else
          Sender = Msg.Sender.GetExchangeUser.PrimarySmtpAddress  'Whyyyyyyyyyyyyyyyyy! Why can't Msg.Sender.Address JUST WORK?!?~
        End If
        
        If SenderPattern.Test(Sender) And SubjectPattern.Test(Msg.Subject) Then
          '*** snip ***
          
          'Since Msg isn't already in Trash, Msg.Delete would put it there, but won't return it after moving it; Msg.Move does...
          Set DeletedMsg = Msg.Move(Trash)
          'At this point, DeletedMsg.Delete really SHOULD work, but it doesn't work, because... well, it just doesn't
          'Instead, get its (new? since it's in the trash now) EntryID so we can go digging through the trash to find it again
          EntryID = DeletedMsg.EntryID
          
          'Use the subject field to filter the items in the trash so we're not looping through thousands of deleted items
          Subject = DeletedMsg.Subject
          Set DeletedItems = Trash.Items.Restrict("[Subject] = '" & Subject & "'")
          DeletedItems.Sort "[SentOn]", True  'It should be a recent item, so sort them with more recent items first
          
          For Each DeletedMsg In DeletedItems
            If DeletedMsg.EntryID = EntryID Then
              'This is the same DeletedMsg as before, but now DeletedMsg.Delete DOES work, because... well, just because
              'DeletedMsg is already in Trash, so DeletedMsg.Delete permanently deletes it this time
              '(yes, the Delete method really does different things based on where the message is :WTF:)
              '(no, there's really not an easy way of saying "permanently delete this message" :WTF:)
              DeletedMsg.Delete
              Exit For
            End If
          Next
        End If
      End If
    Next
    
    Session.LogOff
    


  • @anotherusername said:

    do stuff with them

    I find it hard to imagine what kind of "stuff" you might want to do with an email that Outlook's mail rules don't already support.

    Since you snipped that part of the code, I guess it will forever remain a mystery.



  • Take your pick...

    Use something other than the message verbatim (reformat, subtract from or add to it)
    Print it to something other than the default printer (like a PDF printer)
    Forward or redirect it to something other than an email address
    Save a copy on a physical (or network) drive instead of in an Outlook folder



  • I guess I wouldn't do that in Outlook. But sure why not.



  • I admit it's a really edge-casey use case, but that's what scripting is for, right?


  • Grade A Premium Asshole

    @anotherusername said:

    but that's what scripting is for, right?

    It is mostly used for butchery, fuckery and skullduggery in cases like these.



  • Also, FWIW, my "Outlook's built-in filters are shit" comment was partly also due to the fact that most of them won't fucking run unless Outlook is currently running. The Exchange server is completely helpless to do most of the useful actions you'd want it to perform, even the ones that it should be able to perform. Like, say, forwarding emails.



  • I keep my outlook running all day and my automatic filters don't run automatically. What gives?



  • No idea?

    I keep my Outlook running all day, and my automatic filters run automatically. Until I.T. decides to push updates on a Friday afternoon and my computer installs updates and then shuts down for the weekend. Then none of the filters run until Monday morning when I start it back up again and start Outlook.



  • I'd probably just write a quick-and-dirty Windows Service in C# that's an email consumer, does what you want, then forwards the emails on your inbox when finished.

    That might have actually been easier than screwing around with VBA. But I admit it's also kind of "own a hammer, see a nail."



  • I've noticed that .NET has Office interop stuff. Not sure how well it works, though.... or if you can even do something like filtering with it in Outlook.


  • I survived the hour long Uno hand

    Oh hey, while we're talking about Exchange....

    Does it have functionality to like.... alter the subject lines of emails that originated from specific servers? to, say, prepend "[TEST EMAIL]" so our sales staff don't get concerned when someone in test uses a live email address on accident? Granted, I'd rather rip out the connection to live exchange servers for a mailcatcher service on the box instead, but I want options to present.



  • @powerlord said:

    I've noticed that .NET has Office interop stuff.

    Well yeah, but I wouldn't involve Outlook at all.



  • This is a CodeSOD thread.



  • @Yamikuronue said:

    Does it have functionality to like.... alter the subject lines of emails that originated from specific servers?

    That should definitely be possible from the Exchange configuration. I don't know how, but I've received emails that had it done to them, so it has to be possible.

    edit: mSexChange.org has a tutorial on Exchange transport rules which might help:
    http://www.msexchange.org/articles-tutorials/exchange-server-2007/compliance-policies-archiving/Transport-Rules-Exchange-Server-2007.html

    Users can't write filters like that in Outlook... although, since it's possible to edit emails that you've received, I think a filter that runs a script could also do it.



  • @blakeyrat said:

    I'd probably just write a quick-and-dirty Windows Service in C# that's an email consumer, does what you want, then forwards the emails on your inbox when finished.

    That sounds less quick and more dirty than my VBS.



  • You'd be surprised how easy shit like this is in C#.



  • Oh, I should probably mention (since I didn't have an aggravated comment in the source)... I ended up needing

    If Msg.Class = 43 Then
    

    because MeetingItem objects don't have a Sender property. So the script crashed if there were any meeting invitations in the inbox.

    I originally wrote it to use SenderEmailAddress to get the sender's email address (which both MeetingItem and MailItem objects do have), but silly me... sometimes it's an Exchange address instead of a regular email address (and it's not even consistently one or the other for the exact same sender).


  • Discourse touched me in a no-no place

    @Yamikuronue said:

    Does it have functionality to like.... alter the subject lines of emails that originated from specific servers? to, say, prepend "[TEST EMAIL]" so our sales staff don't get concerned when someone in test uses a live email address on accident?

    Remove the email addresses or remove the ability to send mails to Production mailboxes IMO.



  • It could be worse, one person I know would attempt to fix this in PHP using the COM plugin to try to talk to Outlook, as his first solution attempt, assuming it's even possible.



  • @blakeyrat said:

    I find it hard to imagine what kind of "stuff" you might want to do with an email that Outlook's mail rules don't already support.

    Filtering spam from one of the Enterprise news lists or our internal gift shop comes to mind. The subjects are always different and they both change domains every time as well. The Enterprise news list is :rolleyes:. But our own internal gift shop? 😡


Log in to reply