Excel VBA Case Study #1 – Sheet Saver and Sender Macro (18 steps in 10 seconds)

Last updated on February 8, 2019 By Victor Chan

Summary: Why invest time learning VBA if it doesn’t make your life easier?


When I encounter a boring and repetitive task at work, I figure out how to get rid of it before it drives me insane.


I can either try – and fail – to convince my boss that the task is pointless, or I can impress my boss by writing a macro that will do the work for me. Guess what I do?


In this case study I explain how I used Excel VBA macros to do a repetitive 18 step task in just 10 seconds.



Download the Sample Workbook

Download the sample file with VBA code and the data workbooks to email

20190208-Excel-VBA-Sheet-Saver-Sender.zip (624 Kb)

#1 – Why did I create the Macro?

Each month I receive a financial performance workbook from each of my company’s subsidiaries. We have 50 subsidiaries, so I receive 50 workbooks per month.


Each workbook contains the following three sheets:

  1. Notes about the previous month’s business
  2. Financial statements as of the end of the previous month
  3. Investments as of the previous month

At one point, I had to manually save the “Investments” sheet in the same directory as the workbook, and then send this sheet via email to one of my colleagues. I had to repeat this process for each of the 50 subsidiaries.


I know this task seems pointless. I was spamming my colleague’s mailbox instead sharing the files via the server. Since my company often grew paranoid about server access rights, though, I had to find another solution.


Unfortunately, I wasn’t allowed to skip this task. Since I had already been experimenting with VBA, I bet you know my next thought. This cumbersome process needed programming! I got to work!

#2 – What does it do?

When I decided to automate this process with a macro, I first decided on the basic logic and rules:

  • I had to apply the proper file naming logic (Year_Company Number_Company Short Name_”Report”_Month of the report).
  • I needed to identify time periods. As I always received reports for the previous month, I had to use the number of the previous month in the filename.
  • I needed the ”Investment” sheet saved in the same directory as the monthly report.
  • After the workbook was saved in the appropriate directory, I wanted to automatically open a new email in Outlook and attach it.
  • I wanted the email’s text automatically written, and its addressees automatically chosen. I did not want to send the mail automatically, though, in case I needed to manually change anything.
  • The whole process needed to be faster with the macro – this was the main goal!

After laying out the requirements, I had to figure out how to write code to meet them.

#3 – How is it better than using Excel without VBA?

As I already mentioned, a macro would save loads of time. In daily work, I hate to waste energy on pointless processes. Who does?


However, there were a few parts of the process I couldn’t easily automate:

  • I still had to check the sheet “Investment” thoroughly to ensure everything was filled out properly
  • I had to compare several amounts between sheets

That being said, I focused my macro on saving the “Investment” sheet in the appropriate folder and emailing it.


Without the macro, the whole process looked like this:

  1. Select “Investment” sheet (1 second)
  2. Right Click on the same sheet (1s)
  3. Click “Move or Copy” (1s)
  4. Select “New Workbook” (1s)
  5. Click “Create a copy” (1s)
  6. In the new workbook click “File” (1s)
  7. Choose “Save as” (1s)
  8. Browse to find the folder (10s)
  9. Type in the name of the file (5s)
  10. Click “Save” (1s)
  11. Close the newly created and saved file (1s)
  12. Open Outlook (1s)
  13. Click “New Email” (1s)
  14. Attach the file after browsing (5s)
  15. Enter the email addresses (5s)
  16. Type in the email subject, which was the same as file name (5s)
  17. Write the text of the email by using a sample and making any necessary changes (5s)
  18. Click “Send” (1s)

The manual process took approximately 50 seconds. That may not seem like much, but remember that I had to do this for 50 companies! That is almost an hour of time wasted!


My company paid me for an hour of clicking and browsing on my computer and spamming my colleagues!


As you can see, I can’t stand useless processes. I needed a macro that automatically performed the 18 steps with only a couple of clicks!

With my new macro I can simply select the “Investment” sheet, bring up the Macro Dialog Box (ALT + F8) and run the “Sheet Saver” macro. Done in less than 10 seconds

#4 –How does the code work?

It’s time to get to the interesting part! Don’t feel overwhelmed, though. I will walk you through the code bit by bit.

Variables

As all programmers should, I started by declaring the variables:

Public CountryNr As String
Public CountryName As String
Public Year As String
Public ActMonth As String



Sub Sheet_Saver()

'Declaring variables
Dim ActPath As String
Dim Msg As String

As you can see, I publicly declared some variables before the beginning of the “Sheet Saver” macro. I did this because, although I wrote the “Sheet Saver” and “Send Mail” macros separately, they shared the public variables.

'Setting variables
CountryNr = ActiveWorkbook.Sheets("Financials").Range("B2").Value
ActPath = ActiveWorkbook.Path
Year = "2019_"

After declaring the variables, I assigned values to each.

  • “CountryNr” represented Country Number, which I took from cell B2 of the “Financials” sheet
  • “ActPath” represented the path of the currently active workbook – this is the original workbook’s file path; I knew I’d have to save the “Investments” sheet to the same folder
  • I hardcoded the value of the “Year” variable since it would be easy to manually change it once a year

The next snippet gets more interesting:

'Choosing CountryName of list
Select Case CountryNr
  Case "11"
   CountryName = "Middle Earth"
  Case "12"
   CountryName = "Narnia"
  Case "13"
   CountryName = "Westeros"

'Error handling, if number is not in the list
  Case Else
   MsgBox ("Error! No country found with the given number!")
End Select
  

As you can see, I used the “Select Case” method to connect the country numbers to their short names. I hard-coded the names since I didn’t anticipate them changing.


If the “Case” (e.g., 11, 12, or 13) isn’t found, the MsgBox will alert the user that no country matches their number.


I determined the period using the below code:

'Choosing the right month: the month before the actual month
'If it's January, take month 12
ActMonth = month(Now) - 1
If ActMonth = "0" Then ActMonth = "12"


'If month is only 1-digit long, convert it to 2-digits long
If ActMonth < 10 Then ActMonth = "0" & ActMonth

After the macro sets the “ActMonth” equal to the period, I make sure it’s the right month by asking the user to confirm via a MsgBox:

    'Display question about the right month
    Msg = "The document will be saved as " & Year & CountryNr & "_" & _
    CountryName & "_Invest_" & ActMonth & ".xlsx" & " for month " & _
    ActMonth & vbNewLine & _
    "Is this the right month?"
        
        'If the defined month is wrong, the correct one must be typed in
        If MsgBox(Msg, vbYesNo, "Period") <> vbYes Then
            ActMonth = InputBox("Please, type in the right period (month)", _
            "Period", "01")
        End If

If the user clicks “No” on the MsgBox to indicate that the month is incorrect, I use an InputBox to allow the user to input the correct month. I then save the copy of active sheet to the path as a new workbook:

'Save the active sheet in the same directory where ActiveWorkbook is,
'with the specified naming logic
ActiveWorkbook.Sheets("Investments").Select
    Sheets("Investments").Copy
        ActiveWorkbook.SaveAs Filename:= _
        ActPath & "\" & Year & CountryNr & "_" & CountryName & _
        "_Invest_" & ActMonth & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

I created the above code snippet by recording it using the Macro Recorder, modifying the Filename property, and replacing hardcoded values with my own variables.


After the macro saves the sheet, it calls the “Send Mail” macro:

'Displaying E-mail form before sending it out.
'If you don't have Outlook installed, don't worry!
'The Send_Mail function won't run, and you are done with saving the sheet.
    Call Send_Mail


'Close the copied sheet, save changes
ActiveWorkbook.Close True

End Sub

Before digging into the “Send Mail” macro, note that after the “Send Mail” macro runs, the macro closes the copied sheet. The word “True” after the “Close” command means that the code will save the changes before closing the workbook.

Now let’s look at the “Send Mail” macro:

Private Sub Send_Mail()
'This example sends the last saved version of the ActiveSheet,
'via an Outlook Object
'Microsoft Office Library needs to be enabled under Tools -> References

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

I started with “Private Sub” to indicate that the routine can only work within the current module. It shouldn’t run until after the first macro saves the “Investments” sheet as a new workbook and activates it.


The code only works if you’ve enabled the Office objects by following the below steps in the VBA Editor:

The version number (e.g., “16.0”) depends on your current Office version, but the rest of the name stays the same. Make sure to activate the reference object before running the macro!


The next lines of code set the email’s properties:

    On Error Resume Next
    With OutMail
        .To = "harry.potter@roxfort.gb"
        .CC = ""
        .BCC = ""
        .Subject = Year & CountryNr & "_Invest_" & CountryName & "_" & _
        ActMonth & ".xlsx"
        
        .Body = "Dear Mr. Harry Potter," & vbNewLine & _
              vbNewLine & _
              "Attached you can find last month's investments from " & _
              CountryName & "." & vbNewLine & _
              vbNewLine & _
              "Best Regards," & vbNewLine & _
              vbNewLine & _
              "Daniel Lajosbanyai"

I think they’re relatively self-explanatory. When I built the macro, I recorded code to send an email and then replaced hard-coded values with variables.


In the “.Body” section, you can see the email’s text.


After the macro builds the emails, it attaches the workbook created from the “Financials” sheet:

        .Attachments.Add ActiveWorkbook.FullName
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

The macro uses “.Display” to show me the email instead of automatically sending it. This allows me to give it a once-over before sending it.


I knew I could easily change “.Display” to “.Send” after running the macro several times and confirming it worked properly. This would shorten my process even more.

#5 – Summary of key learning points

I love automating manual processes using macros!


In all the macros I write, I follow this basic process:

  1. Declare variables first
  2. Assign values to variables
  3. Perform the main task, which can include calling another macro
  4. Use the values of the variables

I built the “Sheet Saver” and “Send Mail” macros this way. Doing so helps build structured and easy-to-debug code. Over time you’ll learn that debugging often takes more time than coding!


Don’t be afraid to try Macro Recorder or solutions from internet forums. And if sample code doesn’t meet all your needs, experiment!

#6 – About the Author

Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)


To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.


Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!


Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.