Emailing system is running great...except

Things are humming along nicely.

I'm able to specify a list of web pages on which there are email addresses for people whom I believe will be interested in hearing about what I'm selling. Then I run a macro that views each page, parses from them the valid email addresses, compares those addresses to those already in my list, and if they are not in the list, adds them to the bottom of the list. Once I've gathered the email addresses, I can programmatically send each one an HTML formatted email, and mark the date and time of each sent email. Finally, when I receive any bad email bounce back messages or "remove me" replied, I can programmatically find the email addresses in the list and mark them as either "bad" or "removed", so that messages are no longer sent to those addresses.

Things I still need to do is make it easier to load a customized HTML email message -- sort of like building a library of messages, which I can programmatically specify. Right now I edit the HTML code each time I want a slightly different email message.

Also, I still haven't heard back from the guy who wrote Outlook Redemption, so I'll have to try to figure out on my own how to specify a particular email account from which to send emails, so that they don't accidentally "spill over" into my Gmail account, even though it's not the default account set in Outlook.

Another thing I'll do is add a macro to automatically mark email addresses with date and time stamps for positive reactions to the message -- i.e. views of certain web pages, requests for more info, etc.

It's getting pretty slick. Stay tuned...

Hooray for Outlook Redemption!

A key piece of using VBA to automate the sending of emails using Outlooks is a little .dll called Outlook Redemption


Outlook Redemption works around limitations imposed by the Outlook Security Patch and Service Pack 2 of MS Office 98/2000 and Office 2002 and 2003 (which include Security Patch) plus provides a number of functions to work with properties and functionality not exposed through the Outlook object model. What's this mean? You don't get those little "A program is trying to send an email on your behalf..." messages or the 5 second waiting period.

With Outlook Redemption you can...

• Make your VB and VBA code run unaffected by the Security Patch.
• Access properties not exposed by the Outlook Object Model (internet message headers, sender e-mail address and hundreds more properties)
• Directly access RTF body of any Outlook item
• Import MSG, EML (RFC822) and TNEF files
• Export messages to MSG, EML, TXT, HTML, TNEF and vCard formats.
• Directly access message attachments as strings or as arrays without saving them as files first
• Display Address Book
• Force immediate Send/Receive (Tools | Send/Receive in Outlook)
• Track new e-mail events with (unlike Outlook) new e-mail item passed to your handler.

Redemption supports Outlook 98, 2000, 2002 and 2003 (Outlook 97 is not supported).

I say...check it out if you haven't already.

One thing I'm unsure how to do (either using Outlook Redemption coding or standard VBA for Outlook) is how to specify a particular email account from which to send. Right now I've got two email accounts running through Outlook -- my business email and my personal Gmail POP account. Once in a great while an email I automatically generate and send using my code sends using the Gmail account...

I've emailed Dmitry Streblechenko (Outlook Redemption creator), asking if this can be specified using Outlook Redemption, and I'll post what I find out.

Later.

Doin' some really sweet Excel and Outlook integration

I'm convinced that using VBA, one can automate and integrate Office apps how ever he or she chooses. I've got a really sweet system of collecting and sending emails and automatically marking and deleting bounce backs, "remove me"s, and positive responses using Excel and Outlook. With a bit more coding and tweaking...who knows what is possible.

Okay...here's the first version of the code

Should be obvious what it does:

Sub Parse_Emails()
'
' Parse_Emails Macro
' Written by Excel_Geek
'

'I JUST ADDED THIS TO ACT AS A SAFEGUARD AGAINST ACCIDENTALLY CLICKING THE "GET EMAILS" BUTTON
varAnswer = MsgBox("Are you sure you're ready? You've got the URL range and the starting email cell set right?", vbYesNo, "HOLD ON THERE...")
If varAnswer = 7 Then
Exit Sub
End If

'SELECT THE CELL INTO WHICH THE FIRST EMAIL ADDRESS FOUND CAN GO
Range("C2").Select

'DEFINE "cel" AS EACH CELL WITHIN THE RANGE DEFINED BELOW
Dim cel As Range

'SET THE RANGE OF THE URLS FROM WHICH TO PARSE EMAILS
For Each cel In Range("A2:A49").Cells

'SET THE VALUE OF EACH CELL AS THE "WEB_PAGE" FROM WHICH YOU'LL PARSE EMAILS
WEB_PAGE = cel.Value

pageParseRequest = WEB_PAGE
If pageParseRequest <> "" Then

Set regEx = CreateObject("VBScript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = "([a-z0-9\._-]+@+[a-z0-9\._-]+\.+[a-z]{2,4})"

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
objHTTP.Open "GET", pageParseRequest, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

' If objHTTP.Status <> 200 Then ' Could not retrieve SOAP message
' Response.Write objHTTP.Status
' Set objHTTP = nothing
' Response.End()
' End If

pageToParse = objHTTP.responseText
Set objHTTP = Nothing
Set expressionmatch = regEx.Execute(pageToParse)

'varAnswer = MsgBox(expressionmatch.Count, vbOKOnly, "NOTICE!")

If expressionmatch.Count > 0 Then
For Each expressionmatched In expressionmatch

ActiveCell.FormulaR1C1 = expressionmatched.Value
'varAnswer = MsgBox(expressionmatched.Value, vbOKOnly, "NOTICE!")
ActiveCell.Offset(1, 0).Select

'Response.Write "<B>" & expressionmatched.Value & "</B> was matched at position <B>" & expressionmatched.FirstIndex & "</B><BR>"
'Response.Write Right(expressionmatched.Value, Len(expressionmatched.Value) - 7) & "<br />"
Next

'Else
'Response.Write "<B>" & regEx.Pattern & "</B> was not found in the string: <B>" & StringToSearch & "</B>."

End If

End If

'Response.Write( "<xmp>" & pageToParse & "</xmp>" )

With cel.Interior
.ColorIndex = 43
.Pattern = xlSolid
End With

Next

End Sub

Sorry about the commented out remnants of web programming in there.