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?