How to: VBA email validation

Author: Robert Martim | Posted: 2. July 2009 19:10 6

This is a follow up to my post on sending email messages using Windows Vista. You can read the previous post here: http://www.msofficegurus.com/post/How-to-Windows-Vista-SMTP-Server-Using-CDOMessage.aspx

In this post, I discuss email validation using VBA. VBA email validation is a very simple process that does not require much, but you will need to install the Microsoft VBScript Regular Expressions 5.5.

In order to install the reference, follow these steps:

1. Open VBE (Alt+F11)
2. Go to “Tools --> References…”
3. Search in the reference list for Microsoft VBScript Regular Expressions 5.5, select it and click OK to continue.

The figure below shows the reference installed:

Figure 1: Microsoft VBScript Regular Expressions 5.5 reference installed

NOTE:
Only today, after receiving a comment on this post, I noticed that I was using Excel in Portuguese and not English. Since I am always changing the language settings, I sometimes forget to change back to the language I am writing on. I will leave the image as it is, as I am sure everyone can get the picture (if you will forgive me the pun). Added: July 3rd, 2009

Now, you can create a boolean user-defined function to test an email string to check whether it is a “regular expression”. In my case, I check for the following pattern:

\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

There are a number of patterns that you can you and you should be able to find my patterns, especially in sites that teach java or php (check RFC 2822 for info on pattern). You can then borrow one of those patterns and use in your code.

The user-defined function will look like this:

Function ValidateEmail(ByVal sEmail As String) As Boolean

    Dim oRegularExpression     As RegExp

'   Sets the regular expression object
    Set oRegularExpression = New RegExp

    With oRegularExpression
'   Sets the regular expression pattern
        .Pattern = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"

'   Ignores case
        .IgnoreCase = True

'       Test email string
       ValidateEmail = .Test(sEmail)
    End With

End Function

You can now test the user-defined function with a simple call:

Sub Test_ValidateEmail()
    MsgBox ValidateEmail("my.email@hotmail.com")
    MsgBox ValidateEmail("your.EMAIL@hotmail,com")
End Sub

c554edcf-f8fb-44a2-b06c-a1aa348a9238|1|5.0

Tags: microsoft office, excel, vba, udf, vista

How to: Windows Vista SMTP Server - Using CDO.Message

Author: Robert Martim | Posted: 22. June 2009 21:38 2

I had been using CDO object with Windows XP for a while and the code always ran without a problem, as long as the SMPT server was installed and properly set up. That was easy to do. As I moved the code to Windows Vista, I come to realize that my code no longer worked. I tried installing the SMTP Server on Windows Vista, but that was pointless since it is no longer available. I was having some trouble believing it was not there given that I used Windows Vista Ultimate, but fellow MVPs Bernard Cheah (http://www.iis.net/ http://msmvps.com/blogs/bernard/) and Gary VanderMolen confirmed my worries. Ron de Bruin, a fellow Excel MVP, also took part in the conversation about the issue. He also has an article on the subject which can be found here: http://www.rondebruin.nl/cdo.htm

According to Gary, this is pretty much useless anyway given that most ISPs nowadays block all port 25 traffic except to their own SMTP server.

In that case, I had to adapt things slightly so that I could route my emails through my SMTP Server. Nonetheless, I can still use Vista to route the email to a local pickup folder so that I can at least test things.

Here’s how it is done (uncomment the code to test locally):

Sub CDOTest_OnVista()
    Dim oCDO                    As Object
    Dim oCDOConfig              As Object

    Const c_sUsername           As String = "Your_Username_Goes_Here"
    Const c_sPassword           As String = "Your_Passoword_Goes_Here"

'   if you are using localhost to route the e-mail, you need to define
'   a pickup folder. In this case, I use: "C:\inetpub\mailroot\Pickup"
    Const c_sPickupFolder       As String = "C:\inetpub\mailroot\Pickup"
    Const c_iSMPTPort           As Integer = 25


    Dim iSendUsing              As Integer
    Dim sSMPTServerName         As String

   iSendUsing = 2      'remote host
'   iSendUsing = 1      'localhost

'   Define the SMTP Server here. "localhost" for using Vista's localhost
'   as the server. In the case below, you should use your own server.
'   sSMPTServerName = "localhost"
    sSMPTServerName = "Your_SMTP_server_goes_here" 'e.g.: smtp.youcompanyname.com

    Set oCDO = CreateObject("CDO.Message")
    Set oCDOConfig = CreateObject("CDO.Configuration")

    With oCDOConfig.fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = iSendUsing
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = sSMPTServerName
        .Item("http://schemas.microsoft.com/cdo/configuration/username") = c_sUsername
        .Item("http://schemas.microsoft.com/cdo/configuration/password") = c_sPassword
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = c_iSMPTPort
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory") = c_sPickupFolder
    End With

    With oCDO
        Set .Configuration = oCDOConfig
            .From = Sender@Email.com
            .To = Receiver@Email.com
            .Subject = "Test Message"
            .Configuration.fields.Update
            .Send
    End With

    Set oCDOConfig = Nothing
    Set oCDO = Nothing

End Sub

e5f3b1e1-90a0-468e-88dc-a36db991dcc4|1|5.0

Tags: excel, vba, vista

Microsoft Excel | Microsoft Excel - VBA | Vista

SQL 2005 Reporting Services Labelling

Author: Robert Martim | Posted: 31. January 2009 10:19 0

In this video, I show you how to create a labelling system SQL Server Business Intelligence Development Studio 2005.

I do not go into the details of formatting the labels, as there are so many out there, but you will learn all you need in order to get started.

ce72f295-08f5-4974-bc43-38cc3543830b|4|5.0

Tags: sql, ssrs, vista, analysis services

SQL Server 2005 | SSRS 2005 | Vista

Copying and Moving files using VBA

Author: Robert Martim | Posted: 25. January 2009 12:34 0

This tip shows how to use VBA to copy of move files in Windows directories. You can apply this tip to any of the office suite (Word, Excel, Outlook, Access, etc).

In order for this tip to work using the code below, you need to ensure that the references to Windows Script Host Object Model are installed.

COPY FILE

Sub CopyFileToAnotherLocation()

Dim FSObj As FileSystemObject
Dim answer As Integer

Set FSObj = New FileSystemObject

    If FSObj.FileExists("C:\New Location\Filename.txt") Then
        answer = MsgBox("File already exists in this location. " _
            & "Are you sure you want to continue? If you continue " _
            & "the file at destination will be deletedo!", _
            vbInformation + vbYesNo)

If Not answer = vbYes Then Exit Sub
End If

FSObj.CopyFile "C:\Old Location\Filename.txt", _
"C:\New Location\Filename.txt", True

End Sub

MOVE FILE

Sub MoveFileToAnotherLocation()

Dim FSObj As FileSystemObject
Dim answer As Integer

Set FSObj = New FileSystemObject

    If FSObj.FileExists("C:\New Location\Filename.txt") Then
        answer = MsgBox("File already exists in this location. " _
            & "Are you sure you want to continue? If you continue " _
            & "the file at destination will be deletedo!", _
            vbInformation + vbYesNo)

If Not answer = vbYes Then Exit Sub
End If

On Error Resume Next
Kill "C:\New Location\Filename.txt"

FSObj.MoveFile "C:\Old Location\Filename.txt", _
"C:\New Location\Filename.txt"

End Sub

afeba7ee-55a5-41a7-a717-f95e2fc1fa95|1|5.0

Tags: excel, access, word, microsoft office, vba, xp, vista

Microsoft Office | Microsoft Office - VBA

MSOfficeGurus

Where MS Office Developers Meet

How to: VBA email validation

How to: Windows Vista SMTP Server - Using CDO.Message

SQL 2005 Reporting Services Labelling

Copying and Moving files using VBA

Month List

MSOfficeGurus

Where MS Office Developers Meet

How to: VBA email validation

How to: Windows Vista SMTP Server - Using CDO.Message

SQL 2005 Reporting Services Labelling

Copying and Moving files using VBA

Newsletter

Thank you

Tag cloud

Month List

Blogroll