Roy Osherove

View Original

A highly primitive solution for fighting .Text blog spam - an outlook script

A few days ago I opened my machine and saw that I had 348 new spam comments on my blog.
Now, .Text is now smart enough to provide a link for you in the notification email that allows you to "hide" each comment via one click, but when it comes to doing this same thing 350 times it becomes - well, not so effective.
So I created a simple solution to my problem.
 
I created a simple VBA script that runs inside outlook. Whenever a new email arrives, it checks whether it is a blog comment, and if so, if it comes from a specific IP that you have specified as "banned". if it does, than it will automatically find the entry ID inside the email body, and execute the link that hides the comment for you inside the default browser. Simple, but it works.
 
Here's the file that contains all the script.
It comes with comments inside it that explain exactly what you need to do to make it work for your own blog (you basically need to just change a couple of string on the top, and add simple lines for each IP address you would like to ban). It works on Office 2003 (uses the Application_NewEmailEx event which is only available in that version..) cause that what I have. I'm sure this can be implemented for working in older version too.
 
Currently this just works on new email. I should add another ability that cleans out existing spam too. Should not be too hard to implement. Will post more when there is more to tell...
 
Hope this helps!
 
[update:]
Add the following function to the file and you have a macro that allows you to look at existing folders for comment spam. It lets you select a folder and applies the same rules on unread emails inside it:
 
Public Sub SearchEmailsForCommentSpam()
 
Dim oNS As NameSpace
Dim counter As Integer
 
Set oNS = Application.GetNamespace("MAPI")
Dim oFolder As MAPIFolder
Set oFolder = oNS.PickFolder
 
Dim oMessage As MailItem
Dim bannedIds As Collection
Set bannedIds = GetBannedIPAddresses()
 
For Each oMessage In oFolder.Items
    If oMessage.UnRead = True Then
        If HandleMailIfIsValidCommentSpam(oMessage, bannedIds) Then
            counter = counter + 1
        End If
    End If
Next oMessage
 

MsgBox "Took care of " & counter & " blog spam items!"
 
End Sub
 
Private Function HandleMailIfIsValidCommentSpam(mail As MailItem, varBannedIds As Collection) As Boolean
          If IsMailABlogComment(mail) Then
                If IsMailFromBannedIP(mail, varBannedIds) Then
                    Call DisableCommentInMailByClickingLink(mail)
                    HandleMailIfIsValidCommentSpam = True
                    Exit Function
                End If
          End If
          HandleMailIfIsValidCommentSpam = False
End Function
 
update:
Well, this sucks!
I can't get shellexecute to actually "hide" the link. When I open the required "remove" link in the browser it simply shows the list of comments , but does not actually remove any of them. HOWEVER, if I actually CLICK on the mail message link, (which points to the SAME link exactly) the message will be hidden. Any idea what's going on here?