View Full Version : Looking for testers

Ken Puls
2011-11-30, 07:50 AM
Hi everyone,

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:

Public Sub EmailViaOutlook()

'Create the email object
Dim oEmail As New clsOutlookEmail

With oEmail
'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)
End With

'Release the email object
Set oEmail = Nothing
End Sub

Some things I was hoping to accomplish with this:

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)
Some questions for you if you feel like testing it:

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?
Feedback welcome. :)

2011-12-16, 12:39 AM
Ken, tried it out, works flawlessly for me, Office 2007 on Windows XP with an Exchange account.

I wouldn't make the "To" required, in many uses I'd use it for you'd bcc a lot of people without having a "to". It certainly provides a great framework for sending emails from Excel, I can think of several uses that I would have for it that I've just been too busy/lazy to implement.

Limited coding experience would be the ideal user, it certainly seems flexible enough to adapt to a number of uses.

The only option I can think that would be helpful for automated email is adding a signature that's called by signature name.


Ken Puls
2011-12-16, 04:51 PM
You know, that's funny... I've NEVER sent an email without a "To". When I do mass BCC'ing, I always send it to myself then do the BCC's. In fact... I'm not actually sure I even knew that you could send a mass BCC email without including a "To" recipient. :redface:

I'll make an update for it, and add the signature part as well. :)

2011-12-16, 06:24 PM
I tested it to be sure you could before I posted it. I just avoid sending email to myself, seems a little cyber-crazy-cat-lady-esque to me ;)


Ken Puls
2011-12-24, 05:04 AM
I've fooled with this a bit. Adding text signature is easy, but for some reason I can't get the image signatures to actually render in Outlook 2010. They keep showing up with the box with the red x in it. Not sure why that is. Ran out of time to play with it though, so will circle back on it in the new year.