Sometimes you may want to email some information from an Excel worksheet to people without actually emailing the whole workbook.
You could, of course, use copy and paste or even screen print to do this but there is also a way to email it directly from Excel itself
by using VBA to draft and send the email. This could be programmed to happen automatically without any interaction saving time and effort.
This page will step you through the basics of how to do this.
The first step is to activate the developer tab in Excel if it isn't already there.
There are two ways of achieving this.
If you want to permanently have the developer tab available you can do this by following the directions in the video alongside.
The other method is to press "ALT"+"F11" at the same time. Remember that your keyboard may also require you to press the "fn" key also.
Either of these methods should open the "Developer" screen.
Add The OutLook Object Library Reference
The next thing you need to do is to make sure that the "Microsoft Outlook 16.0 Object Library" reference is added to Excel.
The video alongside demonstrates how to do this but if you prefer here are the step by step instructions.
Navigate to the "Developer Tab" and click on the "Visual Basic" button. (this is usually the leftmost button on the "Developer" task bar)
Click on the "Tools" button.
Click on "References" on the Tools drop down
Find the entry for "Microsoft Outlook 16.0 Object Library" and click on the check box to activate it.
(If it is already checked then you don't need to do anything else.)
You are now all set to begin coding.
Define an Instance of the Outlook Application
Dim Email AsOutlook.Application
Set Email = NewOutlook.Application
This first thing you need to do is declare a new instance of the Outlook application.
In this case we have used the name "Email".
Define a New Email Item
Dim EmailMSG AsOutlook.MailItem
Set EmailMSG =Email.CreateItem(olMailItem)
This next thing you need to do is declare a new email item so we can build the email itself.
In this case we have used the name "EmailMSG".
Note that the word in red should be the same as the item declared in the previous step.
Construct Your Email Headers
EmailMSG.To = "Who.To@Where.com"' Who to send it to
EmailMSG.CC = ""' Who to carbon copy
EmailMSG.BCC = ""' Who to blind carbon copy
EmailMSG.Subject = "Snappy Title"' Subject of email
The next step is to construct your email headers.
The headers are used to direct the email in the usual way. The format is exactly as you would
construct the email in Outlook.
If you want to send the email to more than one recipient, then you need to separate them using a semi-colon (;).
If any of the email addresses are not in your Outlook Address Book the email send will fail.
You will need to have set up your Outlook Address Book or Excel
will enter a setup dialogue.
EmailMSG.HTMLBody =
"Hi,<br><br>Testing<br>Regards,<br>Kevin"' Your message
EmailMSG.Attachments.Add = ThisWorkbook.FullName' Attach this workbook
Constructing the body of the email is pretty straightforward, however, you do have options on how to do it.
If you want to send an attachment, then set the .Attachment.Add to the name of the file to attach.
When setting the body of the email
you need to consider which of two methods to use. You can use .Body and use VBA variables like vbNewLine, or you
can use .HTMLBody and use standard HTML to construct the email.
.Body will override .HTMLBody
Send Your Email
EmailMSG.Send' Send the email
To send the email it really couldn't be any simpler .... you use
EmailMSG.Send
You will not get any confirmation returned.
That is all there is to it.
If you want a sample workbook along with the working email code, then it is avaliable from
here .
The output from this sample workbook should look like this in your email:
If you need any support or assistance with any of the code on this site or if you would like to contact us, please click here
