- 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:
Some things I was hoping to accomplish with this:
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)
- 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?