Blocking SPAM from Known Domains in Outlook

As per my previous posting on the subject, for some reason, I have been getting hit recently by SPAM.

HARP Refinance <HARPRefinance@businessthere.eu>
Sitting Solution <SittingSolution@pappend.xyz>
SittingSolution <SittingSolution@lendorate.xyz>
BizCashPros <bizcashpros@fuud-eaat.eu>
Diet Notice <DietNotice@laseral.xyz>
Diet Notice <DietNotice@dashend.xyz>
Diet Notice <DietNotice@pemender.xyz>
Diet Notice <DietNotice@huffinter.xyz>
SittingSolution.com <SittingSolution.com@jakery.xyz>
SittingSolution.com <SittingSolution.com@menastad.xyz>
Curves <Curves@lewaster.xyz>
Curves <Curves@mengert.xyz>
6MinutestoSkinny <6MinutestoSkinny@laserbym.xyz>
6MinutestoSkinny <6MinutestoSkinny@lefferal.xyz>
and so on…

Now, originally, I would simply click on the Junk e-mail option to Block Sender.  The issue with this is that these SPAMmers never use the same e-mail account twice, so this is completely ineffective.

So by examining the sources of the problem, we can very quickly find a nice pattern,  all the SPAM is comming from .xyz and .eu domains.  Knowing this we can use Outlook’s Blocked Senders to build a simple pattern to block them all!  Here’s how it is done.

On the Home tab on the Ribbon, click on the Junk drop-down

Outlook 2013 - RibbonClick on the Junk E-mail Options…

Outlook 2013 - Ribbon - Junk E-mail Options MenuGoto the Blocked Senders Tab, followed by the Add… button

Outlook 2013 - Junk E-mail Options DialogThen simply add the domain suffix for the domain to block in the provided textbox.

Outlook 2013 - Junk E-mail Options Add address or domain to blocked sender listingFinally simply click OK twice to return back to Outlook.

 

It’s that easy.  Since having taken this step, I no longer have to deal with any of the garbage sent by these perpetual SPAMmers as their e-mails go directly into my Junk E-mail folder.  Life is good once again!

 

Important Note:

  • In this example I am blocking outright any e-mail from a .xyz or .eu domain.  That said, you need to carefully examine which domains are SPAMming you and act accordingly by using their suffix.
  • Blocked e-mails are not deleted, but rather moved directly to the Junk E-mail folder.  As such, you should review the content of that folder from time to time to ensure no legitimate e-mails end up in there.
  • At any time you wish, you can go back into the Blocked Senders Tab of the Junk E-mail Options… dialog and highlight an entry and simply delete it by clicking on the Remove button.  So nothing is permanent and you can undo this process at any time.

Stellar Phoenix Access Database Repair Review

Stellar Phoenix Access Database Repair

I was approached by Stellar Phoenix to review their Access Database Repair software.

After performing a few tests, here are my thoughts on the matter:

Password Encrypted Files

Stellar Phoenix Access Database Repair - Not a Valid MS Access fileThe first hurdle that I faced was the fact that I encrypt most of my databases with a password. Sadly, Access Database Repair cannot work with such files! To me this is a major limitation.

Recovering Deleted Records

One of the nice feature was the fact that Access Repair offers the ability to recover deleted records.  That said, the way in which it works had me very confused.  It offers no way to reinserted the deleted records back into the originating table, but rather one must save the recovered database in which a new table will be created with the deleted records.  It is not the way I would have built the software, but it does work and then you can use queries to recover the records of interest.

As per other freely available recovery techniques, it is impossible to recover deleted records once a database is compacted.

File Corruption

File corruption comes in all sorts of shapes and forms.  One form of corruption that I recently was faced with was the fact that an unknown user had erroneously open an MS Access database in MS Word (why, I have no clue).  By doing so, the MS Access database became corrupted and no longer functioned.  I then checked with fellow MVPs and was surprised that this occurs more often than I thought.  So I decided to try to recover a recently corrupted database using Stellar Phoenix, but it was unable to recover the file stating:

Stellar Phoenix Access Database Repair - Not a Valid MS Access file

I also tried to repair a database which had a corrupted form.  What the exact nature of the form corruption was is unknown to me, but I do know that it became corrupted.  Sadly, Access Database Repair was unable to fix the problem.

 

I then sent an e-mail to my point of contact at Stellar Phoenix to ask for clarifications and below was the list of corruptions that they claim Access Database Repair Addresses:

Symptom 1: Cannot open a form or report
Symptom 2: Number of records varies, depending how the data is sorted (index corruption)
Symptom 3: Some table rows show ‘Deleted’
Symptom 4: Memo field contains strange characters.
Symptom 5: “An error occurred while loading Form_FormName”
Symptom 6: “Error Accessing File. Network Connect May Have Been Lost”
Symptom 7: “AOIndex is not an index in this table”
Symptom 8: Key field is no longer primary key, and relationships are gone

 

Conclusions

I’m not sure what to think.  I will need to do further testing to truly know how effective a tool Access Database Repair truly is.

My other thought on the matter is that in many cases, the solutions to many (if not all) of the symptoms is freely available and various resources exist to help.  For instance:

For me, corruption, the possibility of corruption, simply reinforce the critical importance of Backups for all files (not just MS Access databases)!  By implementing a proper backup strategy, the repair of corrupted files becomes unnecessary.

 

more to come…

Smart Inderter Not Working in Office 2010, 2013

VBA Smart Indenter

If you do any serious VBA development, you should have heard, and installed, Office Automation Ltd’s Smart Indenter.  If you haven’t you should!  It is an exceptional FREE tool that helps properly indent your code to help with code legibility & standardization.  It offers numerous customizable options to suit your specific needs.

That said, I recently tried installing on a machine with Office 2013 and it would not work!?  What to do.

After so going back and forth with Mz-Tools (another must have VBA developer tool), I finally got it operational.

Now, I do not know exactly why or how, but the only conclusion I could come up with is the order of installation somehow fixes whatever the underlying problem is/was.  You must install Mz-Tools first and then Smart Indenter.  Smart Indenter on its’ own does not work, nor does installing Mz-Tools subsequently.  After trying different variations, the one that appear to have “fixed” the problem was to uninstall the Smart Indenter & Mz-Tools, then reinstall Mz-Tools followed by Smart Indenter.

So the conclusion drawn from my experience, it would appear, is that  Mz-Tools must be installed first!

 

If you have any insight into the nature of the problem (perhaps even an alternate solution) or if you can confirm my fix, please post a comment.

Maintaining your Virtual Machines – Windows XP Mode

Did you know you can actually maintain your Virtual Machines? I sure didn’t! I have been using Windows Virtual PC / Windows XP Mode for a little while now and never thought remotely about maintenance. It worked, so why concern myself.

That said, over time I noticed that some of my Virtual Machine were bloating in size when I thought of the contents. These Virtual Machines were GB larger than what I thought they should be and thus hogging valuable Hard Drive real estate for no reason. As such, I started nosing around and discovered that you can in fact Compact a Virtual Machine.

Sadly, once again Microsoft has buried something useful under layers of menus.  No clue why they chose to hide such a command under the Modify button, but if you dig a little, the option to regain a little hard drive space is indeed there.

Below are the instructions on how to do so.

  1. Open your Virtual Machine Folder (typically found in %UserProfile%/Virtual Machines/)
    Virtual Machines
  2. Select the Virtual Machine you would like to compact
  3. Click on the Settings button in the toolbar immediately above the Virtual Machine Listing (a pop-up dialog will appear)
    Windows Virtual PC Settings
  4. Select Hard Disk 1 (right-hand side of the dialog)
  5. Click on the Modify Button (Left-hand side of the dialog – another pop-up will appear)
    Modify Virtual Hard Disk
  6. Click on Compact virtual hard disk
    Compact Virtual Hard Disk
  7. Click on Compact
    Compacting Virtual Hard Disk
  8. Wait for the the Compaction Success Confirmation (this can take more or less time depending on many factors)
    Virtual Hard Disk Compaction Successful
  9. Click Close

Now, I’m not saying you need to do this every week, but after deleting a large amount of data, removing sizeable programs, … from a Virtual Machine, it may be a good idea to perform a quick compaction.

VBA – Remove HTML Tags from a String

I needed to clean some webpage texts for a private project I was working on and created the following function. Today, in a forum question, the subject resurfaced so I dug up my function and am posting it here should it might serve someone else.

So here is a simple function utilizing Regular Expressions to remove/sanitize/extract HTML Tags from the passed string. So you get returned the text and nothing else.

'---------------------------------------------------------------------------------------
' Procedure : RemoveHTML
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Remove any HTML tags and/or comments from a string
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sString   : String to sanitize (remove HTML tags from)
'
' Usage:
' ~~~~~~
' RemoveHTML("<html><b>And</b><!-- some comment --> <p>then<br/> some</p></html>")
'           Returns: And then some
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Aug-26             Initial Release
' 2         2009-Oct-30             Changed pattern to include comments
'---------------------------------------------------------------------------------------
Function RemoveHTML(sString As String) As String
    On Error GoTo Error_Handler
    Dim oRegEx          As Object
 
    Set oRegEx = CreateObject("vbscript.regexp")
 
    With oRegEx
        'Patterns see: http://regexlib.com/Search.aspx?k=html%20tags
        '.Pattern = "<[^>]+>"    'basic html pattern
        .Pattern = "<!*[^<>]*>"    'html tags and comments
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
    End With
 
    RemoveHTML = oRegEx.Replace(sString, "")
 
Error_Handler_Exit:
    On Error Resume Next
    Set oRegEx = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: RemoveHTML" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

VBA – Split/Break a Camel Case String

I was having a recent discussion with a fellow MVP and came across a function I developed a while back and thought it could be useful to others.  Below is a very simple function which uses a RegEx pattern to break apart a Came Case string into a legible string.

'---------------------------------------------------------------------------------------
' Procedure : SplitCamelCase
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Split/Break a Camel Case string
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sString   : Camel Case string to break/split
' sDelim    : Character to use as a spcaer, if omitted will use a space
'
' Usage:
' ~~~~~~
' ?SplitCamelCase("SplitCamelCase")
'       Returns Split Camel Case
' ?SplitCamelCase("SplitCamelCase", "_")
'       Returns Split_Camel_Case
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-May-03             Initial Release
'---------------------------------------------------------------------------------------
Function SplitCamelCase(sString As String, Optional sDelim As String = " ") As String
On Error GoTo Error_Handler
    Dim oRegEx          As Object
 
    Set oRegEx = CreateObject("vbscript.regexp")
    With oRegEx
        .Pattern = "([a-z](?=[A-Z])|[A-Z](?=[A-Z][a-z]))"
        .Global = True
        SplitCamelCase = .Replace(sString, "$1" & sDelim)
    End With
 
Error_Handler_Exit:
    On Error Resume Next
    Set oRegEx = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: SplitCamelCase" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Trusted Location Editor by CARDA Consultants Inc.

Tired of battling with Microsoft’s never ending changing layouts, navigating through an endless maze of menus, and the fact that the Trust Center is inaccessible on the runtime version of MS Access, I recently created the Trusted Location Editor. A very lightweight application which put the user back in the driver’s seat to manage all the various MS Office application Trusted Locations from one single screen! In essence, what Microsoft should have provided users 8 years ago!

Simplicity was the order of the day, so everything is straightforward and no more than 1 click of a button away!

Trusted Location Editor - Overview

For all the details on this application, please use the following link:

MS Office Trusted Location Editor

VBA – CDO Mail

Although I often refer people to use CDO mail and have briefly discussed it in prior postings, I never gave examples. Today that changes!

So what exactly is CDO mail and when is it useful?

CDO mail is one of many technique available to developer to send e-mails.

If you are an MS Access developer you are surely aware of the SendObject method, but it will only allow one attachment and that attahcment has to be an Access object (Not an external file).

Another very common approach is to use Outlook automation, but with new security impositions by Microsoft it has become unreliable and now requires workarounds. Furthermore, after a recent discussion with fellow MVPs, we find out that having an outdated virus definition can stop longstanding code from working! As such, although powerful, outlook automation simply is not reliable for true production. For production tools, one must then look at integrating Outlook Redemption, but the down side to this solution is that it requires registering a COM library for it to work.

Which brings us to CDO Mail!
CDO Mail is a technique that binds you directly to your e-mail server to send out email(s).

Pros

  • No e-mail client (outlook, thunderbird, …) need be installed
    • Hence you bypass any e-mail client limitations and/or security restrictions
  • Permits multiple attachments to be included
  • Permits external documents as attachments
  • Accepts both plain text and HTML formatted content
  • Can be used in any programs which uses VBA (MS Access, Excel, Word, PowerPoint, MS Project, …)
  • Can even be used in VBScript

Cons

  • Requires knowledge of all the server settings (port, username, password, authentication, …)
  • Does not keep a copy of sent e-mails so CCing or BCCing yourself becomes useful for archive pruposes
  • Does not permit attaching database objects directly, but you can export them or print them as PDFs and attach those

Below is a sample CDO Mail function

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Function SendCDOMail(sTo As String, sSubject As String, sBody As String, _
                     Optional sBCC As Variant, Optional AttachmentPath As Variant)
    On Error GoTo Error_Handler
    Dim objCDOMsg       As Object
 
    Set objCDOMsg = CreateObject("CDO.Message")
 
    'CDO Configuration
    With objCDOMsg.Configuration.Fields
        '
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
        'Server port (typically 25, 587)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        'SMTP server IP or Name
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.hitterslongrun.com"
        'Type of authentication, NONE, Basic (Base64 encoded), NTLM
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
        'SMTP Account User ID
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "accounts@hitterslongrun.com"
        'SMTP Account Password
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Uhdje!@@0#"
        'Use SSL for the connection (False or True)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
        .Update
    End With
 
    'CDO Message
    objCDOMsg.Subject = sSubject
    objCDOMsg.From = "accounts@hitterslongrun.com"
    objCDOMsg.To = sTo
    objCDOMsg.TextBody = sBody
    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
        If IsArray(AttachmentPath) Then
            For i = LBound(AttachmentPath) To UBound(AttachmentPath)
                If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
                    objCDOMsg.AddAttachment AttachmentPath(i)
                End If
            Next i
        Else
            If AttachmentPath <> "" And AttachmentPath(i) <> "False" Then
                objCDOMsg.AddAttachmentAttachmentPath
            End If
        End If
    End If
    objCDOMsg.Send
 
Error_Handler_Exit:
    On Error Resume Next
    Set objCDOMsg = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SendCDOMail" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Now that we have a function to call, we can now send out an e-mail by making a single call to the function along the lines of:

Call SendCDOMail("RecipientEmail", "Subject", "EmailBody", , array("C:\Users\Test\Documents\sample.pdf", "C:\Users\Test\Documents\ballons.gif"))

Other references on CDO Mail

Sending mail from Excel with CDO
VBScript To Send Email Using CDO ‘Information directly applies to VBA as well