Update on Email List Projects - PayPal® User Agreement

Everyone,

Remember those targeted email list projects that people have requested? Well, I recently learned that under the PayPal® User Agreement, I may not use PayPal's services to sell such services.

What does this mean? From this point on whenever anyone requests a targeted email list from me, as a $50 Project or otherwise, I'll be requiring payment in the form of a check, cashier's check, or money order. Note: I'll still be requiring people making such requests to attest to me that they'll not be using the list in violation of the CAN SPAM Act of 2003 or any other laws in their jurisdiction.

If you're interested, you can contact me for more details.

Later,

Excel_Geek

Email list Project done

Done.

That was pretty simple and quick, and because the keywords used in the Google search were very specific to the intended target (in some cases even specifying the URL to search), I feel that the list was quite effectively targeted.

I pulled several hundred email addresses off in just a few minutes.

Anyone else?

Email address list request project - CAN-SPAM Act of 2003

Recently I was contacted by (anonymous as requested) with a request for a $50 Project under which I was to use my Google search results page parsing code in conjunction with my email address parsing code to compile a list of email addresses from web pages returned for Google searches of some very specific search keywords and terms provided by (anonymous).

I told (anonymous) that I would have to first research anti-SPAM legislation to be sure that in doing said project, I wouldn't be breaking the law. Specifically, somewhere I had been lead to believe that it may be illegal to harvest email addresses from website "automatically" and then send unsolicited emails to those addresses.

What I found is that the CAN-SPAM Act of 2003 is THE law of the land when it comes to unsolicited bulk email, and interpretation of the CAN-SPAM Act of 2003 is all over the board, depending upon which interest group (Advertisers or Anti-SPAM groups) is providing their interpretation.

Here's my (as) objective (as possible) take:

WHAT IT IS ILLEGAL FOR BULK EMAILER TO DO:

1. Access a computer without authorization and intentionally initiate the transmission of multiple commercial electronic mail messages from or through such computer.


2. Use a computer to relay or retransmit multiple commercial emails with the intent to deceive or mislead recipients or any ISP as to the origin of such emails


3. Materially falsify header information in multiple commercial emails


4. Register, using false registrant information, for five or more email or online user accounts or two or more domain names, and use such accounts to send multiple commercial emails.


5. Falsely represent onself to the the registrant or the legitimate successor in interest to the registrant of five or more IP addresses and use such addresses to send multiple commercial emails.


6. Send multiple commercial emails with false or misleading header information


7. Send multiple commercial emails with deceptive or misleading subject lines.


8. Send multiple commercial emails with no functioning return email address (and such address must remain capable of receiving message for 30 days after transmission of said emails)


9. Send a commercial email message to a recipient who has asked to be removed from the list or not to receive any more messages.


10. Send commercial email messages without including "identification" that the message is an advertisement, without an opt-out mechanism, and without the physical address and contact information of the sender.

Further, it will be considered an "aggravated offense" to break any of the rules in 6 - 10 when also doing the things in 1 - 5, or when having either 1) gathered the email addresses using automated means from websites, or 2) using a dictionary-type attack, generating possible email addresses by combining names, letters, or numbers into numerous permutations.

CONCLUSION:

Now, I am not an attorney by any stretch (and I will not be held liable for any damages relating from others' actions on the "advice" of my interpretation of the Act), but my interpretation of the CAN-SPAM Act of 2003, as it relates to the project requested by (anonymous), is that it would NOT be a violation of the Act on my part to provide a list of email addresses collected using "automated" means from websites to someone else provided I had reasonably been assured by that person that they would not be using those email addresses in such a way as to violate the Act, and (anonymous) would not be violating the Act provided that he/she did not do any of the things in 1 - 5 and that he/she did include all of the things required in 6 - 10.

I will perform this $50 Project and any similar others requested by people who give me an assurance that they will not use the addresses in violation of the CAN-SPAM Act of 2003.

Fixed an annoying little problem with my email parser

I was having trouble with error handling on my email parsing macro. Whenever the script tried to return a bad URL or one that could not be returned as text, I was using "On Error Goto..." and directing to an error handling branch that ran (marking the URL as "BAD" by coloring it RED and then directed back to the next URL.

This would run just fine once (it could handle one bad URL), but when it encountered another bad URL, it would puke.

I have since changed to using "On Error Resume Next" and following the two lines of code prone to erroring out, I placed this bit of code:

If Err Then
GoTo BAD_URL ' Error Handling Branch
End If

Then, at the end of the error handling branch, just before directing back to the next URL, I cleared the Err object.

Err.Clear

Now it can handle errors an unlimited number of times. I'm so happy.

Later.

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.