v2.0 – Excel Hint, how to use Excel VBA to send a copy of your Microsoft Excel workbook via email – 2nd in the series
- 0 Comments
Last week we did our first post in a series, on how to send a Microsoft Excel workbook, via Microsoft Outlook email, by clicking a command button in Excel. This is the second post in the series, taking it up a level. So if you have not looked at post one, the most basic method, please do so. And look for the third in this series next week.
This post contains the actual code that will work immediately after dropping it into your file, use this link to download the zipped file.
will work as well. Below are a few print screens, and a short video showing the file in action.
If you have not already done so, please take the time to visit our new website, ExcelConsultant.Net which has a great new blog.
Image 1 (Below) - Microsoft Excel file immediately after opening. This file contains the data needed to populate the email when the macro is sent (Name – column A, Email Address – column B). The graphic in column D, that looks like an email, is the button you click to initiate the macro. Finally, you will notice the column C, the SendTime is blank. When the code runs, it will populate this field.
Image 2 – The green arrow is pointing to the button you need to click to run macro/code/vba. When you click this button, if your workbook is macro enabled, this will run the code to send the emails.
Image 3 – This is what the workbook will look like once the code has finished running you will see that the three cells in column C have been populated with the long date.
Image 4 – This is what you will see in Microsoft Outlook, a new email, the email address, subject and body populated, with the workbook to be sent attached. Our code has this set as ‘Display’, if you wanted the email to send, without your need to click the send/receive button, change the code to “Send”.
Image 5 – Here is what the code looks like in the VBE. This vba was written by Microsoft Excel MVP, Jacob Hilderbrand, owner of this site and of vbaexpress.com. This code works, Jacob’s comments help you to find the sections of code you may want to expand on or alter.
The image below is a split-screen, the left side is Microsoft Excel and the right side is the VBE. If you watch it closely you will see the macro being stepped through (F8), line by line, the yellow row being the currently active line. You will then see the workbook on the left being populated as the code moves through each line.
So that is it for now. Take the code below and drop it into your workbook, or download the file from this site, and run it as is. Then play with it, change a line of code, one at a time, and see what you can do with it. We have done the hard part, the easy part is for you to take these lessons we are posting, and take an hour to plat with it. That is all the time you need.
Here is the code, paste this into your Excel workbook:
Dim i As Long
Dim LastRow As Long
Dim AppOL As Object
Dim EmailItem As Object
Const olMailItem As Long = 0
Set AppOL = CreateObject(“Outlook.Application”)
‘Get Last Row
LastRow = Range(“A” & Rows.Count).End(xlUp).Row
‘Process Each Row
For i = 2 To LastRow
Set EmailItem = AppOL.CreateItem(olMailItem)
.Subject = “Insert Subject Here”
.Body = “Dear ” & Range(“A” & i).Value
.to = Range(“B” & i).Value
‘Release Object Variables
Set EmailItem = Nothing
Set AppOL = Nothing
If you have questions pertaining to this code, visit our site, VBAExpress.Com Excel forums, go to the forum, if you are not a member please join. Post your questions and they will be answered but some of the brightest, most passionate Excel experts out there, from Microsoft Excel MVP’s, Microsoft Certified Partners, Microsoft Vendors, Microsoft employees, and die hard in the trenches tried and true mavericks.
If you want to hire a team to assist you, we do that as well. For more info on the team and what we can do, please visit our main site, ExcelConsultant.Net. Jacob is the top VBA developer and programmer on the site, you can work with him, just shoot us an email, use the contact form, the chat window, or dial 844-VBA-Help and Jacob and I will help you.
Finally, please look for the third in this series of posts in about a week or two, taking it to an ever deeper level.
Thank you, Christopher