Once you've exposed Outlook's object model with binding, you have all the tools you need to begin automating Outlook. The only step that remains is to explore Outlook's object model and figure out how to do what you want. For my money, examples are king. So I've attempted to provide you with some examples in the next sections to show how you might use your new found knowledge and hopefully provide you with some tips and tricks to speed up the learning process. I'll start with a very common activity, sending e-mail.
This procedure adds two attachments to the e-mail; the ActiveWorkbook and a Word document. The CreateItem method is used to create the new e-mail. This method can be used to create just about anything in Outlook; Tasks, Appointments, Journal Entries, etc. olMailItem is a built in constant that tells VBA which type of item to create.
Because the ActiveWorkbook will be one of the attachments, it needs to have a path. To ensure that it does, the workbook is saved. This also ensures that the most current copy of the workbook will be attached and not the previously saved copy.
The .To property of the MailItem object is an easy way to define the recipients address. There is also a Recipients collection to which you can add recipients. The next example uses the Recipients collection.
The body of the e-mail will consist of whatever is in D4 of the ActiveSheet. You can concatenate cells to create whatever body you would like. You can also use the .HTMLBody property to create as complicated a body as you want. The .HTMLBody property was introduced in Outlook 2000 and is not available in earlier versions. Creating a body using HTML is the subject of another example.
Adding attachments to the e-mail is accomplished by adding file names to the Attachments collection. The Add method of the Attachments collection accepts strings that are valid file names including the full path.
The .Display property shows the e-mail without sending it. I generally use .Display while I'm testing the procedure and change it to .Send when I'm finished.
To send one sheet of the current workbook, the .Copy method of the Worksheet object is used with no arguments. This creates a new workbook with the copied sheet in it and makes it the ActiveWorkbook. Then the workbook is saved so that it has a proper path so it can be attached later.
Note that the Recipients collection is used instead of the .To property. The Recipients collection is useful when you are adding recipients inside of a loop. Otherwise, the .To property works just as well.
The .FullName property used when adding the attachment was introduced in Excel 2000 and was not available in earlier versions. It can be recreated in earlier versions by using ActiveWorkbook.Path & "\" & ActiveWorkbook.Name.
The newly created workbook is closed and the Kill statement is used to delete the file from the disk.
Nothing too new here. The HTMLBody property is set to a string that represents HTML. You can code the string yourself, but in this example, I used a function that turns a worksheet into an HTML file and reads the contents of that file. You can see the SheetToHTML function, but it's nothing too fancy. There is also a RangeToHTML function for when you just want to use a range instead of the whole sheet.
Remember, the HTMLBody property was introduced in Outlook 2000 and is unavailable in earlier versions.
Below are the two functions mentioned above that covert a sheet and a range to html, respectively.
Public Function SheetToHTML(sh As Worksheet)
SheetToHTML = ts.ReadAll
Function RangetoHTML(Rng As Range)