download source code
E-mail address validation is a topic I've written about on numerous occasions. Last year I wrote an article titled "Validating E-mail Against the Mail Server", which discussed using .NET to effectively validate e-mail addresses. The idea was to save time and resources by not sending e-mails to "bad" e-mail accounts.
When organizations need to inspect and verify a list of e-mail addresses, sometimes it's nice to pop that list into Microsoft Excel, click a button that says "Who's your daddy," and receive a fairly reliable count of valid e-mail addresses -- even when you have obstacles like Yahoo!'s mail servers, which determine each and every e-mail address as valid.
The issues with Yahoo! were hot topics when I first implemented this code into production, and they continue today. Every month I am left with the task of verifying well over 10,000 e-mail addresses-and a good portion of them are from Yahoo!.
Yahoo!'s e-mail servers respond with an 'A-OK' to every e-mail address you try to send e-mail to. And although I can't quote the standards off-hand, I don't think that's exactly included in there. But regardless, there are some rules that can be bent while others can be broken, right?
All Yahoo! e-mail addresses are connected to Yahoo! profiles. So, if the e-mail address is through Yahoo!, you are going to first hunt them down in Yahoo!'s profile pages. This is less complicated now because nothing is impossible with .NET.
To address this, you will need to take the code from the validating e-mail Web service article and add the following function to that project.
If you add the ThanksYahoo function to the validation Web service you will know if a user is valid or not, even if it's a Yahoo! address. What's hilarious is that this is actually less resource intensive, and it takes much less work to validate an e-mail than talking to the mail server. So when you come across BeenSwindled@yahoo.com, you will pass the username portion to this function to see if it's valid. As luck would have it, it is valid. (Someone should send this guy an inspiration Yahoo! e-card).
This function will create a WebRequest object that will pull the HTML down from the Yahoo! profiles page. If a user is not present, it will respond with "user not found".
Function ThanksYahoo(ByVal name As String) As Long
Dim ret As String
' The Path to the Yahoo Quotes Service
Dim fullpath As String = "http://profiles.yahoo.com/" + name
'// Create a HttpWebRequest object on the Yahoo url
Dim webreq As HttpWebRequest = WebRequest.Create(fullpath)
'// Get a HttpWebResponse object from the Yahoo url
Dim webresp As HttpWebResponse = webreq.GetResponse
'// Create a StreamReader object and pass the Yahoo Server stream as a
parameter
Dim strm As StreamReader
strm = New StreamReader(webresp.GetResponseStream(), Encoding.ASCII)
'// Read a single line from the stream (from the server)
'// We read only a single line, since the Yahoo server returns all the
'// information needed by us in just one line.
Return strm.ReadToEnd.IndexOf("User not found")
End Function
Now that Yahoo! is out of the way, on to the fun stuff.
Microsoft Office 2003 has a new COM Interop for writing .NET code behind an Excel file-instead of using Visual Basic applications (VBA). This opens up a whole new world of possibilities for checking e-mail addresses in Excel. Now that your boss can check his own e-mail addresses, you can keep back to your EverQuest character. You can download the Interops from MSDN library.
After you have legally obtained a list of e-mail prospects, you can see how many of them are worth anything. If you obtained your list from one of those new worm viruses, just remember that famous saying, "What goes around, comes around." To get started, you will need to have the Micorsoft Office 2003 Interops. But unless you have an MSDN subscription, or a copy of Office 2003, you might be out of luck.
Once installed, Office 2003 Interops will provide you with more options and new project types in Visual Studio.
Choose an Excel Workbook project from the New Project window. (If you would rather do one in Word, or anything else, I wish you good luck with that.) My very first programming project, at a little company called GoldMine Software, was to figure out how to read cells from Excel and update numbers in a CRM system. I spent 12 hours figuring out that project and loved it. So I was pretty delighted to return to Excel once again.
When you create the project, Visual Studio will create an OfficeCodeBehind Class for you. This will give you access to the Excel object and events. Now that you have the setup, add a form to the project to display to the Excel user. This gives them the options to process the e-mail addresses in the workbook. In the ThisWorkbook_Open subroutine you're going to add this code, which creates the frmExcel form and displays it. You've also changed the form to accept an instance of the workbook object so you can gain access to the e-mail addresses in the worksheet.
Dim oform As New frmExcel(ThisWorkbook)
oform.Show()
In the project you'll need to add a reference to the Web service. In the sample project for this article the Web reference points to projectthunder.com. However, that will not process requests for you. You will need to setup your own copy of the Web service and update the reference. In the form, add a button. Then add the following code to the click event for the button.
Dim oSheet As Excel.Worksheet
Dim oEmail As New com.projecthunder.IWAMFW.ValidateEmail
Dim stemp As String
Dim stemp2 As String
oEmail.Timeout = 1000
'Using the local instance of the worksheet.
oSheet = CType(_workbook.Worksheets.Item(1), Excel.Worksheet)
'loop until there is nothing there
For i As Integer = 1 To 900
If oSheet.Range("C" + i.ToString).Text.ToString.Trim = "" Then
stemp = oSheet.Range("A" + i.ToString).Text.ToString
Try
stemp2 = oEmail.ChatMailServer(stemp).ToString
Catch
'in case something dies
oEmail = New com.futuredontics.services.ValidateEmail
End Try
'if something went wrong let's say we couldn't connect
If stemp2 <> "" Then
oSheet.Range("C" + i.ToString).Value = stemp2
Else
oSheet.Range("C" + i.ToString).Value = "3"
End If
stemp2 = ""
End If
'humor me and process windows messages although it seems to do nothing
System.Windows.Forms.Application.DoEvents()
Next
End Sub
If you're sharp you picked up on my Windows OLE API, which is an interface for setting up and displaying a compound document. This is nothing short of a hack to keep Excel 2003 from blowing up when the Web service takes a few more milliseconds than it cares to wait. You are just asking OLE to suppress a dialog that Excel presents when it is waiting for your Web service.
The integration between Office and .NET is new. Please keep in mind that if your Web service is under a heavy load with requests, Excel may appear to be hung-up while it updates each one. You could change the code to read all the e-mail addresses into an array, then check all of them, and then write them back to Excel all at once. I personally prefer to see the Excel spreadsheet being updated one by one.
You could use the older version of the Interop to do something similar with Office XP. Interop, I believe, is short for interoperability. Perhaps think of it as an underpaid translator between COM and the managed code world. But keep in mind that you must have the right version for your version of Office.
In conclusion, using the Web service from the article "Validating E-mail Against the Mail Server",, you figured out how to deal with Yahoo! e-mail addresses by checking for a Yahoo! profile. You also learned how to consume that Web service from Excel 2003 using a Microsoft Office Systems Project.
