The other day I was working on an Outlook post, and I thought it would be nice to have a really easy way to just drop some code into an Excel project that made it really easy to create an Outlook email. After a while, I decided to create a class module that contained all the code needed to bind to Outlook, create the email, send/preview it and then disconnect.
I'm pretty happy with what I've got here, but I'd like some people to test it before I publish it as an article.
It should be pretty easy. Just download, extract and import the attached class module, then create an email using a routine simliar to what's below:
Some things I was hoping to accomplish with this:
Public Sub EmailViaOutlook()
'Create the email object
Dim oEmail As New clsOutlookEmail
'Add a recipient
.AddToRecipient = "To_someone@somedomain.com"
'Add a couple of people to CC on the email
.AddCCRecipient = "CCing_someone@somedomain.com"
.AddCCRecipient = "CCing_someone_else@somedomain.com"
'Add a blind carbon recipient
.AddBCCRecipient = "BlindCarbon_someone@somedomain.com"
'Set the subject
.Subject = "The files you requested"
'Set the body
.Body = "Hi there" & vbNewLine & vbNewLine & _
"Here are the files you requested."
'Add a couple of attachments
.AttachFile = "D:\My Documents\Special Report.xls"
.AttachFile = "D:\My Documents\Another Report.xls"
'Preview the email (or use .Send to send it)
'Release the email object
Set oEmail = Nothing
Some questions for you if you feel like testing it:
- You should be able to add unlimited To, CC, or BCC recipients, as well as attachments. (At least, unlimited up to the limit that Outlook sets, I guess.)
- It should work with any version of Outlook back to 2000, I believe. (I've used a SPLIT function, and iirc that came into play in Office 2000)
- Should I do any other validation? It occurs to me that I never check for a "To" address before sending, and I probably should.
- Did I miss anything important?
- Does it work as advertised?
- Do you think this would be a good solution for a user who has limited coding experience to use, since the main functionality is essentially written?