In my previous 10-Minute Solution, "Handling Automatic Attachments in Outlook", I talked about automatically handling inbound messages with attachments. In this Solution I'm going to show you how to create and send a message with an attachment to a predefined address with simply one click. This trick is useful if you need to send a report or other file frequently or regularly to a specific address.
To begin, start Outlook and select Tools > Macro > Macros. Type a new name for your macro—such as "SendReport"—and click "Create."
The Visual Basic for Applications editor will appear and start you off with the standard opening and closing statements for a VBA macro:
Sub SendReport()
End Sub
Create the mail item that you're going to send:
Set NewMsg = ThisOutlookSession.CreateItem(olMailItem)
This line uses the CreateItem method of the ThisOutlookSession object to create a new item—in this case an olMailItem (a.k.a Outlook Mail Item).
Address your new message:
Set dest = NewMsg.Recipients.Add("CorporateHQ")
dest.Resolve
The first line uses the .Add method of the NewMsg.Recipients object to set the "dest" (destination) of your item to an address you have in your Personal Address Book, such as "CorporateHQ." You could also substitute a standard e-mail address instead, such as "manager@mycompany.com," but if that address ever changes you'll have to come back to your code and change it here. By using an entry from your Personal Address Book (or Contacts folder, if you prefer) for the item, you make it easy to modify it later, should you need to, without affecting your code.
The second line uses the .Resolve method of your dest object in order to resolve the address.
These next two lines set the subject and body text of the message. They are pretty self-explanatory and easy to use. Basically, you're just setting the Subject and Body properties of the NewMsg object to the text you specify here:
NewMsg.Subject = "Daily Report"
NewMsg.Body = "Attached please find the daily report in Excel 2000 format."
Now you're up to actually adding the attachment:
Set rep = NewMsg.Attachments
rep.Add "c:\my documents\reports\daily.xls"
The first line creates a REP object that is the NewMsg.Attachments collection. The second line uses the .Add method to add the "c:\my documents\reports\daily.xls" file to that collection. If you have multiple files to attach, just include additional Rep.Add lines specifying those files as well.
Finally it's time to send the message using the .Send method of the NewMsg object:
NewMsg.Send
You can End Sub after that, or if you'd like confirmation that your process has actually completed, you can add this line:
MsgBox "It's been sent!"
...just to let you know it finished and went out.
When you're completed, the entire macro looks like this:
Sub SendReport()
Set NewMsg = ThisOutlookSession.CreateItem(olMailItem)
Set dest = NewMsg.Recipients.Add("CorporateHQ")
dest.Resolve
NewMsg.Subject = "Daily Report"
NewMsg.Body = "Attached please find the daily report in Excel 2000 format"
Set rep = NewMsg.Attachments
rep.Add "C:\my documents\reports\daily.xls"
NewMsg.Send
MsgBox "The daily report has been sent."
End Sub
