asked on

VBA Code to Check MX Record

Heyas,

Is their anyway in Excel VBA to check the MX record of a domain name i.e. hotmail.com (valid) or gailm1.com (Invalid) and verify if MX record record can be associated with that domain name.

Thank you.

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zack

ASKER

Damn thank you so much wasn't expecting a turnkey solution.

Thank you.

RobSampson

No problem. Thanks for the grade. It didn't take very long, so I decided I may as well make it as easy as possible to use.

Regards,

Rob.

ramrom

There is probably a more sophisticated way to do this.

Windows has an nslookup command. From a command prompt you can enter nslookup /? to get the details. Here's a VBA function that invokes nslookup, redirects the output to a file, reads the file and determines if it has at least 5 lines. A bad domain result has only 3 lines.

Function nslookup(domain) As Boolean
Dim i, x As String
Shell ("cmd /c nslookup -type=MX " & domain & " > c:\foo.txt")
Open "c:\foo.txt" For Input As 1
On Error GoTo error
For i = 1 To 5
 Line Input #1, x: Debug.Print i, x
Next
nslookup = True
Exit Function

error:
If err.Number <> 62 Then MsgBox "Error: " & err.Number & " " & err.Description
Close #1
End Function

Open in new window

RobSampson

That's essentially what I did too. Here's the macro I used in the sample I attached:

Function CheckMX(strDomain As String) As String
    If Trim(strDomain) <> "" Then
        Set objShell = CreateObject("WScript.Shell")
        strCommand = "nslookup -type=mx " & strDomain
        Set objExec = objShell.Exec(strCommand)
        While objExec.Status <> 0
            Application.Wait ("00:00:01")
        Wend
        intReturnedLines = UBound(Split(objExec.StdOut.ReadAll, vbCrLf))
        If intReturnedLines > 3 Then
            CheckMX = "Valid"
        Else
            CheckMX = "Invalid"
        End If
    End If
End Function

Open in new window

ramrom

Yep. I was writing my post while you were writing yours. When I submitted mine I also discovered yours. It confirmed that great minds think alike. No intention to steal your thunder. I am glad to see the "more sophisticated way". I am less familiar with wscript.shell so I did not know how to get the process output directly. (I know how to do that in Python.)

There is an interesting article at http://technet.microsoft.com/en-us/library/ee156605.aspx.

RobSampson

Thanks ramrom, that's a very good article of the differences between .Run and .Exec. I'll be sure to save that if anyone wants a good explanation.