Easy Excel Solution: Splitting and Emailing Data in Two Clicks

S

SavorySilicon

Senior Member
Joined
Jan 28, 2020
Messages
111
Hello, I am good with Excel, I create and manage large spreadsheets with algorimths.
This question is not for me, this is for my friend. He has almost no skill or experience with Excel. He can barely use Excel, and he can successfully check his email most of the time.

He wants to take a large sheetsheet of data, and manually break it up into blocks. Then email that block. The block is about 20 rows and 50 columns, but the exact amount does not matter.
For me, I would highlight the block, copy paste it into a new spreadsheet, then email the file. But that is too complicated for him, he needs to be able to to this in 2 mouse clicks or less. My friend has a memory issue, he has a hard time remembering most new things, and even a process of 2 mouse clicks will strain him.
If I highlight the block, and then print to file, it puts the file onto several separate papers, and that is not a solution, the block must stay on 1 sheet.

What solution is there that takes 2 mouse clicks or less, and has a low chance of mistakes and failure, thank you.
 
Solution
Creating a very simple solution that requires minimal effort and steps from someone with limited Excel skills involves automating the process. Here are a few possible solutions:

Solution 1: Excel VBA Macro with Button​

You can set up a macro in Excel that will automate copying a block of data to a new workbook and then opening an email with that workbook attached. Your friend can then attach it to an email with a couple of clicks.

Steps to Create the Macro:​

  1. Open Excel and press ALT + F11 to open the VBA editor.
  2. Go to Insert > Module to create a new module.
  3. Copy and paste the following VBA code into the module:
    Code: 
    vba Sub CopyAndEmailBlock() Dim emailApp As Object Dim emailItem As Object Dim wb...
Sort by date Sort by votes
Creating a very simple solution that requires minimal effort and steps from someone with limited Excel skills involves automating the process. Here are a few possible solutions:

Solution 1: Excel VBA Macro with Button​

You can set up a macro in Excel that will automate copying a block of data to a new workbook and then opening an email with that workbook attached. Your friend can then attach it to an email with a couple of clicks.

Steps to Create the Macro:​

  1. Open Excel and press ALT + F11 to open the VBA editor.
  2. Go to Insert > Module to create a new module.
  3. Copy and paste the following VBA code into the module:
    Code: 
    vba Sub CopyAndEmailBlock() Dim emailApp As Object Dim emailItem As Object Dim wb As Workbook Dim ws As Worksheet Dim copiedRange As Range ' Define the range to copy Set copiedRange = ThisWorkbook.Sheets("Sheet1").Range("A1:AX20") ' Adjust range as needed ' Create a new workbook and copy the range to it Set wb = Workbooks.Add Set ws = wb.Sheets(1) copiedRange.Copy Destination:=ws.Range("A1") ' Save the new workbook temporarily TempFilePath = Environ$("temp") & "\" TempFileName = "CopiedBlock.xlsx" wb.SaveAs TempFilePath & TempFileName ' Create email item and attach the file Set emailApp = CreateObject("Outlook.Application") Set emailItem = emailApp.CreateItem(0) With emailItem .To = "" .CC = "" .BCC = "" .Subject = "Extracted Data Block" .Body = "Please find attached the data block." .Attachments.Add TempFilePath & TempFileName .Display ' or use .Send to send directly End With ' Clean up wb.Close False Set ws = Nothing Set wb = Nothing End Sub
  4. Close the VBA editor.
  5. Create a Button on your spreadsheet:
    • Go to the Developer tab (enable it from Excel options if not visible).
    • Click on Insert > Button (Form Control), and draw the button on your workbook.
    • Assign the CopyAndEmailBlock macro to the button.

      Explanation:​

      • The macro copies a predefined range (A1:AX20 from "Sheet1").
      • It creates a new workbook, pastes the data, saves the workbook temporarily.
      • It creates an email with the extracted data block attached and opens it for review.

      Solution 2: Use Workbook Linking and Shared Folder​

      Another option is to have a pre-prepared master file and use a shared folder setup so your friend only needs to open and then save-as a new workbook.
    []Create a Master Template:
    • Prepare a master template with the desired 20x50 block.
    • Save this template in a shared folder or on the desktop.
    [    ]Guide Your Friend:
    • Instruct your friend to open the template file.
    • Use File > Save As to save a new version of the file for each block. This method is less ideal because it may involve more steps if saving the file in a specific location consistently is a challenge.

      Solution 3: Third-party Tools or Simple Scripts​

      Consider using simple scripting tools or third-party tools designed for non-expert users that can streamline this process even further.

      Summary of the Easiest VBA Solution​

      By following the VBA macro setup, your friend can perform the action in just two clicks (one to select the range and one to click the button).
    []Click the range, then []Click the button. The above VBA solution would be a low-effort, near-foolproof method to help your friend efficiently extract and email the data. Let me know if you need further assistance with setting up the macro or any other method!
 
Upvote 0 Downvote
Solution
You must log in or register to reply here.