How to create a macro that allows to automatically send a specific sheet in excel workbook as email?

VinayKumar_B 

You can create a macro in Excel to automatically send a specific sheet as an email attachment. Here are the steps to create such a macro:

Step 1: Open Excel and Enable Developer Tab

If you don't already have the Developer tab visible in your Excel ribbon, you need to enable it. Here's how:

1. Go to "File" > "Options."
2. In the Excel Options window, select "Customize Ribbon."
3. In the right-hand column ("Main Tabs"), check the box next to "Developer."
4. Click "OK" to enable the Developer tab.

Step 2: Open the Visual Basic for Applications (VBA) Editor

1. Click on the "Developer" tab in the Excel ribbon.
2. Click on the "Visual Basic" button to open the VBA Editor.

Step 3: Create a New Macro

1. In the VBA Editor, right-click on "VBAProject (Your Workbook Name)" in the Project Explorer on the left side.
2. Select "Insert" > "Module" to insert a new module.

Step 4: Write the Macro Code

You can use the following example VBA code as a starting point and customize it to fit your specific needs. This code will send the active sheet as an email attachment using Outlook. Make sure you have Outlook configured and running on your computer.

vba code:

Sub EmailActiveSheet()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim wb As Workbook
    Dim ws As Worksheet
    
    ' Set the workbook and worksheet you want to send
    Set wb = ThisWorkbook ' The current workbook
    Set ws = ThisWorkbook.Sheets("SheetName") ' Replace "SheetName" with your sheet's name
    
    ' Create a new Outlook instance
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0) ' 0 represents a mail item
    
    ' Compose the email
    With OutMail
        .To = "email address removed for privacy reasons" ' Replace with the recipient's email address
        .Subject = "Your Subject Here" ' Replace with your email subject
        .Body = "Hello, please find the attached sheet." ' Replace with your email body
        .Attachments.Add wb.FullName ' Attach the entire workbook
        ' .Attachments.Add ws.UsedRange.Address ' Attach only the used range of the worksheet
        .Send ' Uncomment this line to send the email immediately
    End With
    
    ' Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Step 5: Customize the Macro Code

• Replace "SheetName" with the name of the sheet you want to send.
• Replace "email address removed for privacy reasons" with the recipient's email address.
• Customize the email subject and body as needed.
• Decide whether you want to send the entire workbook or just a specific range from the worksheet (uncomment the appropriate line).
• You can choose to send the email immediately by removing the comment from the .Send line.

Step 6: Run the Macro

Close the VBA Editor, return to your Excel workbook, and run the macro:

1. Press "Alt" + "F8" to open the "Macro" dialog.
2. Select the "EmailActiveSheet" macro and click "Run."

The macro will send the specified sheet as an email attachment using Outlook.

Please note that this example uses Outlook for sending emails. If you are using a different email client or have specific email server settings, you may need to modify the code accordingly.

The text, the code and steps were edited with the help of AI.

Add. Info: Mail more then one sheet

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.