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...
Thursday, June 30, 2005
oh....this is going to get good...
Wednesday, June 29, 2005
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
Tuesday, June 28, 2005
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.
Wednesday, March 23, 2005
Hey - Tyler's got an online version of his legendary newsletter now!
Check it out...Craig helped him...
http://www.kohtz.org/tyler/
Tooting my own horn (again)
Here's an email from my accountant's office...
-------------------------------------------
From: Jan Westerman [mailto:jan@geacpa.com]
Sent: Tuesday, March 22, 2005 5:29 PM
To: Eric Hunzeker
Subject: RE: (removed for privacy)
Thanks, Eric – Fantastic Spreadsheets!!
-------------------------------------------
I am surely the greatest ever...and what I sent her was so simple relative to what can be done...boggles the mind!
Out! - (btw...still getting the excel-based stock trading system fixed...stay tuned.)
Monday, March 21, 2005
Apologies (to whoever) for lack of posts
I was gone over a long weekend at a friend's wedding in the Smoky Mtns in TN. Beautiful (where the tourism craziness hasn't completely taken over).
Anyway...i'm still getting some of the new wrinkles worked out in my Excel-based stock trading system.
I did fart around with a solver-based NCAA tournament bracket picker, but i've got to add in some heavy randomness, skewed by factors associated with the stats...those upsets are just not predictable, you know...(the bracket i picked based upon my judgement as well as one where i flipped a coin for each matchup are not statistically different from the solver-picked one.) Next year, right?
Tuesday, March 15, 2005
working on repairs to the excel-based trading system
This is taking a bit...trying to optimize the way in which the web queries gather and format the data...stay tuned...
Subscribe to:
Posts (Atom)
