May 22 2016

v2.0 – Excel Hint, how to use Excel VBA to send a copy of your Microsoft Excel workbook via email – 2nd in the series

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.

 

excelconsultantnet5

 

 

 

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.

 

email-code-v2-before-code-1

 

 

 

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.

 

email-code-v2-before-code-2

 

 

 

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.

 

email-excel-image-2

 

 

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”.

 

email-code-v2-email-recepient

 

 

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.

 

email-code-v2-code-1

 

 

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.

 

code-v2-1-2

 

 

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:

 

Option Explicit

Sub EmailWorkbook()

Dim i                   As Long
Dim LastRow             As Long
Dim AppOL               As Object
Dim EmailItem           As Object

Const olMailItem    As Long = 0

‘Initialze Objects
Set AppOL = CreateObject(“Outlook.Application”)

‘Save Workbook
ActiveWorkbook.Save

‘Get Last Row
LastRow = Range(“A” & Rows.Count).End(xlUp).Row

‘Process Each Row
For i = 2 To LastRow
‘Create Email
Set EmailItem = AppOL.CreateItem(olMailItem)

‘Format Email
With EmailItem
.Subject = “Insert Subject Here”
.Body = “Dear ” & Range(“A” & i).Value
.to = Range(“B” & i).Value
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
Next i

ExitSub:

‘Release Object Variables
Set EmailItem = Nothing
Set AppOL = Nothing

End Sub

 

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

 

TAGS:
May 13 2016

v1.0 – Excel Hint, how to use Excel VBA to send a copy of your Microsoft Excel workbook via email

Excel Hint, how to send a copy of your Microsoft Excel workbook to one or more users as an attachment to a Microsoft Outlook email, and how to do so programmatically by adding VBA code to your Excel workbook.

It is actually pretty easy to do, and there are plenty of different ways to do it, some simple, as below, and some more advanced, coming shortly. For over the next several weeks we will post a few of those methods, so check back frequently to see how those would work.

The code below was written by Microsoft Excel MVP,  Jacob Hilderbrand.

 

If you have any questions about the code below, please post your question(s) on our Excel forums at vbaexpress.com.

 

Follow these steps to test the code on your computer (Or download the fully functional workbook at the end of this post)

  1. Create a new Excel workbook.
  2. Create a new procedure and name it ‘EmailWorkbook’.
  3. Create an Active X Command button on the worksheet.
  4. Drop the code below into the ‘EmailWorkbook’ procedure.
  5. Call the ‘EmailWorkbook’ in the command button module.
  6. From the Excel worksheet, click the Command button.
  7. Review the Outlook email that is created.
    1. This will show you just one of the many options available to you, based on the code added to the workbook.

 

Past the code below into your Visual Basic Editor

Option Explicit

Sub EmailWorkbook()

Dim AppOL               As Object

Dim EmailItem           As Object

Const olMailItem    As Long = 0

 

‘Initialze Objects

Set AppOL = CreateObject(“Outlook.Application”)

Set EmailItem = AppOL.CreateItem(olMailItem)

‘Save Workbook

ActiveWorkbook.Save

 

‘Create Email

With EmailItem

.Subject = “Insert Subject Here”

.Body = “Insert message here”

.To = “User@Domain.Com

.Attachments.Add ActiveWorkbook.FullName

.Display

End With

ExitSub:

 

‘Release Object Variables

Set EmailItem = Nothing

Set AppOL = Nothing

End Sub

 

Visit our sister site, VBAExpress.Com, you can reach us live at 1-844-VBA-Help, or you can email info@vbaexpress.com

 

Click Here to Download The Workbook: vba-code-to-send-email-from-excel-version-1-by-jacob-hilderbrand-at-excelandaccessllc

 

 

May 2 2016

The Story of Excel

The Story of Excel

blog-banner-what--is-excel-mini

Do you ever wonder whatever happened to Lotus 123 and other products that once seemed to rival Microsoft Excel? If you are new to Excel you are likely to be unaware of them, or the history of Excel. The story of Excel dates back to 1982 when its predecessor was called Multiplan also marketed by Microsoft to compete against a spreadsheet program called Visicalc which was the first electronic spreadsheet program created for Personal Computers. Excel was first released for the Mac in 1985 and the personal computer version was released in 1987.

Our company, Excel and Access, LLC has been working with Microsoft Excel since day one.  One of our friends and previous Excel developers and trainers is now working at Microsoft on the Microsoft Excel team, in addition to being a Microsoft Excel MVP and author.

By 1993 Excel had overtaken Lotus 1-2-3 as the electronic spreadsheet application of choice. One of the most important  changes/additions of the Excel object model is the integration of VBA Visual Basic for Applications, aka, Macros) into Excel took the program to unprecedented levels of functionality.

The notable versions of Excel were 5, 9, Excel 97 (PC), Excel 2000 (PC), Excel 2002 (PC), Excel 2003 (PC), Excel 2004 (Mac), Excel 2007 (PC), Excel 2008 (Mac), Excel 2010 (PC), Excel 2011 (Mac), Excel 2013 (PC), Excel 2016(PC), Excel 2016 (Mac).

Would you like to know more, see the full article on one of our company websites, as this site is about “Excel Hints”, not the full story, Excel Consultant.

Excel 2016 had a host of new features and among them was one of Excel 2010’s most important feature, the PowerPivot add-in. With this feature you could use Excel and work on several 100 millions of rows of data as compared to the one million rows of Excel 2007.

If you need help with Excel or Access, be it consulting or training, please contact Christopher at 877-392-3539.

TAGS:
Apr 27 2016

Hire our Consultants, Programmers & Trainers

Yes, this site which is seen as an educational site on the Microsoft Excel application also offers paid services through Vbaexpress.Com, one of the top five Microsoft

banner

 

VBA Express, Inc. is not just one of the top Microsoft Help Forums in the USA and the UK, it is also one of the best places to get affordable, rapid programming solutions with all of the Microsoft applications from Excel and Access, to SQL Server, Word, Outlook, and even VB.net. If you work on the Mac, don’t fret, we work on the Mac just as well.

Jacob is our Senior Excel, Access, SQL Server, Word, Outlook, VB.Net Developer. Jacob has worked as a VBA, VB.NET, Microsoft Office programming consultant since 2004. Jacob has completed 1,280 projects to date for 304 unique clients and companies. These projects are primarily for Microsoft Office and use VBA automation either as a stand-alone for one application or to add interoperability between the Microsoft Applications. Additionally, Jacob develops windows applications with VB.NET as well as SQL databases: Access, Microsoft SQL Server, and MySQL Server.

Want to learn more, visit Vbaexpress.com or our new site, http://www.excelconsultant.net

Apr 22 2016

Watch our latest Microsoft Access Training Video

Recently we conducted several Microsoft Access training webinars.  We recorded them and we are making them available to the users of this site. No cost, no strings, just watch the video if you find the topic to be of interest.

Stan is the trainer, and he is one of our senior Microsoft Access programmers and trainers.

So while this is not an Excel hint, it is educational.  So if you have the time and the interest, take a peek.

And if you want to see all of our Microsoft Excel training videos, for free, go to the video page on our website.

Click here to open the PowerPoint Slide Deck that goes along with the video.

MS Access Training

Page 1 of 22123451020...Last »

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives