Looking for testers

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,531
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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:

Code:
Public Sub EmailViaOutlook()

'Create the email object
    Dim oEmail As New clsOutlookEmail

    With oEmail
        'Add a recipient
        .AddToRecipient = "[EMAIL="To_someone@somedomain.com"]To_someone@somedomain.com[/EMAIL]"

        'Add a couple of people to CC on the email
        .AddCCRecipient = "[EMAIL="CCing_someone@somedomain.com"]CCing_someone@somedomain.com[/EMAIL]"
        .AddCCRecipient = "[EMAIL="CCing_someone_else@somedomain.com"]CCing_someone_else@somedomain.com[/EMAIL]"

        'Add a blind carbon recipient
        .AddBCCRecipient = "[EMAIL="BlindCarbon_someone@somedomain.com"]BlindCarbon_someone@somedomain.com[/EMAIL]"

        '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)
        .Preview
    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. :)
 

Attachments

  • clsOutlookEmail.zip
    1.7 KB · Views: 28
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.

Jesse
 
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. :)
 
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 ;)

Jesse
 
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.
 
Back
Top