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
andsender_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
-
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?
-
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.
-
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.
-
I've noticed that .NET has Office interop stuff.
Well yeah, but I wouldn't involve Outlook at all.
-
This is a CodeSOD thread.
-
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.htmlUsers 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.
-
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 aSender
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 bothMeetingItem
andMailItem
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).
-
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.
-
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 . But our own internal gift shop?