Results 1 to 5 of 5

Thread: Looking for testers

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14

    Looking for testers



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 = "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)
            .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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    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

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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.

    I'll make an update for it, and add the signature part as well.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    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

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •