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.
Subscribe to:
Posts (Atom)
