Tiny little mod to email system

I added a tiny piece of code to the email system so that it checks to see if each email is marked as "BAD" or "REMOVED" before sending it.

More small updates to follow.

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.

oh....this is going to get good...

Henry's helping me make something very special. I'm not going to let the cat out of teh bad yet, but i'll tell you this: it will work with the email sending macro that i just wrote in Excel.

stay tuned...

Okay...here's the code for the email blasting macro

Okay, okay...i was a bit lazy and copped out a bit when i told you to comment if you wanted to see the code for my latest macro, so here it is in all its glory...

Sub Send_Out_Emails()
'
' Send_Out_Emails Macro
' Written by Excel Geek
'

'DEFINE "cel" AS EACH CELL WITHIN THE RANGE DEFINED BELOW
Dim cel As Range
'SET THE RANGE OF EMAILS TO WHICH TO SEND
'YOU MAY WANT TO BE WORKSHEET OR EVEN FILE SPECIFIC IN NAMING THE RANGE,
' DEPENDING UPON HOW YOU USE AND EXECUTE THE MACRO
For Each cel In Range("B2:B201").Cells

'SET THE VALUE OF EACH CELL AS THE "EMAIL_ADDRESS" WHICH YOU'LL SET FOR EACH EMAIL LATER
EMAIL_ADDRESS = cel.Value

'VALIDATE THE EMAIL ADDRESS
'THANK YOU, HENRY (www.azule.info) FOR THIS REGULAR EXPRESSION
Set regEx = CreateObject("VBScript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = "^[a-z0-9\._-]+@+[a-z0-9\._-]+\.+[a-z]{2,4}$"
If regEx.Test(EMAIL_ADDRESS) = False Then
'IF THE EMAIL IS INVALID (="False") THEN I TYPE "invalid email addresss" IN THE CELL JUST RIGHT
cel.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "invalid email address"
ActiveCell.Offset(0, -1).Select

'OTHERWISE, IT'S A VALID EMAIL AND I SEND AN EMAIL TO THE ADDRESS
Else

'CREATE A MAIL ITEM IN OUTLOOK
Set oolApp = CreateObject("Outlook.Application")
Set Email = oolApp.CreateItem(0)
'CREATE A SAFEMAIL ITEM USING OUTLOOK REDEMPTION DLL
Set msg = CreateObject("Redemption.SafeMailItem")
'LOAD THE SAFEMAIL ITEM AS THE MAIL ITEM
msg.Item = Email

'CREATE THE HTML EMAIL BODY
'ALWAYS HAVE THESE FOUR LINES FIRST FOR AN HTML EMAIL
MailBody = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD W3 HTML//EN"">"
MailBody = MailBody & "<HTML>" & vbCrLf
MailBody = MailBody & "<HEAD><TITLE></TITLE></HEAD>"
MailBody = MailBody & "<BODY>" & vbCrLf
'NOTE: IN THE HTML BODY YOU MUST DOUBLE QUOTATION MARKS (LIKE THE ONES AROUND HYPERLINKS, ETC)
' AS VBA WILL THINK YOU'VE ENDED THE LINE OTHERWISE, AS EACH LINE BEGINS AND ENDS (IN VBA)
' WITH OPENING AND CLOSING QUOTATION MARKS
MailBody = MailBody & ""
MailBody = MailBody & ""
'CLOSE THE BODY AND HTML TAGS
MailBody = MailBody & "</BODY></HTML>"

'SET THE EMAIL ADDRESS, SUBJECT LINE, AND HTML BODY (AS WRITTEN ABOVE) TO EACH EMAIL TO SEND
With msg
.to = EMAIL_ADDRESS
' NOTE: BE SURE TO SET THE VALUE OF THE SUBJECT LINE PROPERLY!!!!
.Subject = "This is the subject line of the email"
.HTMLBody = MailBody
.Send
End With

'NOW THAT EMAIL IS SENT, I TIME AND DATE STAMP WHEN EACH IS SENT
' IN THE CELL TWO COLUMNS TO THE RIGHT
cel.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=now()"
ActiveCell.Copy
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.Offset(0, -2).Select

End If

'MOVE TO THE NEXT EMAIL ADDRESS IN THE RANGE SPECIFIED
Next


End Sub

sorry so long away...

Wow...how long was i asleep?

Anywho...i'm bored with the automatic stock picker excel file...today i finished (it'll never be done, really) an excel file that can blast emails in HTML format via Outlook without sending up those annoying "a program is trying to send email on your behalf..." messages. Check out a little program called Outlook Redemption. It's sweet...like a little wrapper for exteneded MAPI, so you don't have to know all that yourself.

Comment if you'd like to know how i did it.

out.