Send Outlook email with attached Excel that opens at a specific tab for recipient

Question

Hope you are well. I have a VBA code to generate an email and attached the Excel workbook on Outlook. Works fine. The button is on 1 tab and I want to ensure when the recipient opens workbook, it's to another specific tab. ie the button to generate the email is on tab 1 and user will open to tab 2. As there are multiple tabs, I can't just send tab 2 alone. Can someone please advise if there is a simple solution to this? Thank you

My code:

Sub Rectangle1_Click()
Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim signature As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    With xOutMail
        .display
    End With

    signature = xOutMail.body

    
    xMailBody = "" & vbNewLine & vbNewLine & _
              "" & vbNewLine & _
              ""
                  On Error Resume Next
    With xOutMail
        .To = Range("T2")
        .CC = Range("U2")
        .BCC = ""
        .Importance = 2
        .Subject = Range("V2")
        .body = xMailBody & signature
        .Attachments.Add ActiveWorkbook.FullName
        .display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Answer 1

this code will create a temporary copy of the wordbook and activate the desired tab e.g (sheet2), generate the email with the attachment and then store the original workbook from the temporary copy after the email has been composed

Sub Rectangle1_Click()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim signature As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    On Error GoTo 0
    
    ' Copy the Excel workbook to a temporary file to preserve the original workbook's state
    ActiveWorkbook.SaveCopyAs "C:\Temp\TempWorkbook.xlsx"
    
    Dim targetTabName As String
    targetTabName = "Sheet2" ' Replace "Sheet2" with the name of your desired tab
    
    Worksheets(targetTabName).Activate
    
    With xOutMail
        .Display
    End With
    
    ' Restore the original workbook from the temporary file
    Application.DisplayAlerts = False ' Suppress alert for overwriting the original file
    ActiveWorkbook.Close SaveChanges:=False
    Kill ActiveWorkbook.FullName ' Delete the original workbook
    Name "C:\Temp\TempWorkbook.xlsx" As ActiveWorkbook.FullName 
    Application.DisplayAlerts = True 
    
    signature = xOutMail.Body
    xMailBody = "" & vbNewLine & vbNewLine & _
               "" & vbNewLine & _
               ""

    On Error Resume Next
    With xOutMail
        .To = Range("T2")
        .CC = Range("U2")
        .BCC = ""
        .Importance = 2
        .Subject = Range("V2")
        .Body = xMailBody & signature
        .Attachments.Add ActiveWorkbook.FullName
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Answer 2

You can achieve this by using the Workbook_Open event to activate the sheet you want the user to see first when they open the workbook. This event is triggered every time the workbook is opened.

You need to put this code in the ThisWorkbook module in your VBA editor. Here's how you can do this:

1. Press Alt + F11 to open the VBA editor.
2. In the Project Explorer on the left, find your workbook and double click ThisWorkbook.
3. Paste the following code:

Private Sub Workbook_Open()
    Sheets("Sheet2").Activate
End Sub

Replace "Sheet2" with the name of the sheet you want to be active when the workbook opens.

Please note that this is a global setting for the workbook. Every time the workbook is opened, regardless of how it is opened, this code will run and activate the specified sheet.

Your email sending code can remain unchanged. When the recipient opens the workbook, it will automatically navigate to the specified tab due to the Workbook_Open event.

The suggested VBA code is intended to be placed inside the ThisWorkbook object in the VBA Editor. This object contains events that are related to the workbook itself, such as when it's opened, closed, or when it's activated or deactivated.

In this case, we're using the Workbook_Open event. As the name suggests, this event is triggered whenever the workbook is opened. The code inside this event will automatically run. The line Sheets("Sheet2").Activate will make "Sheet2" the active sheet when the workbook is opened.

Here are the steps to add the Workbook_Open event in your workbook:

1. Press Alt + F11 in Excel to open the VBA Editor.
2. In the Project Explorer on the left side of the editor, find your workbook. If the Project Explorer isn't visible, you can show it by pressing Ctrl + R.
3. Double click on ThisWorkbook under your workbook's name. This will open a new code window related to the workbook.
4. In the new code window, choose "Workbook" from the left dropdown menu and "Open" from the right dropdown menu at the top of the window.
5. This will automatically create a Workbook_Open event. Inside this event, write Sheets("Sheet2").Activate. Replace "Sheet2" with the name of your sheet that you want to be active when the workbook opens.
6. Close the VBA Editor.

Here is your modified VBA code:

Sub Rectangle1_Click()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Dim signature As String

    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    
    With xOutMail
        .display
    End With

    signature = xOutMail.body

    xMailBody = "" & vbNewLine & vbNewLine & _
              "" & vbNewLine & _
              ""
    
    On Error Resume Next
    With xOutMail
        .To = Range("T2")
        .CC = Range("U2")
        .BCC = ""
        .Importance = 2
        .Subject = Range("V2")
        .body = xMailBody & signature
        .Attachments.Add ActiveWorkbook.FullName
        .display
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Private Sub Workbook_Open()
    Sheets("Sheet2").Activate
End Sub

With this modified code, when you run the Rectangle1_Click subroutine, it will send the email with the workbook as an attachment. When the recipient opens the workbook, it will automatically navigate to the specified sheet due to the Workbook_Open event.