Search code examples
excelvbaoutlook

List all tabs in a workbook excluding the last tab

I want to list all the tabs in a workbook, in an email body, excluding the last tab.

My solution, noticing the last tab name is always 21 characters, is to delete the last 21 ​characters from the string.

I need to either have a better method or provide some kind of error handling process, with Left and Len functions, if somebody tries to use the macro on a workbook without enough tabs to subtract 21 characters from the end of the string. I am hesitant to just slap on "If on error resume next" because I feel like its a bad practice from my time browsing stack.

Here is a segment of the code:

​Dim EmailItem  As Object
​Dim xMailOut As Outlook.MailItem
Dim xOutApp As Outlook.Application
​Set xOutApp = CreateObject("Outlook.Application")
​Set xMailOut = xOutApp.CreateItem(olMailItem)
Dim wks As Worksheet, strName As String
​For Each wks In Worksheets
​    strName = strName & wks.Name & "<br>"
Next
MSG = "" & Left(strName, Len(strName) - 21)

The error will come in at the last line of the code given the workbook does not have enough tabs / a tab matching the characters of the fixed one to be deleted. I don't want to delete the tab. I just don't want the tab name in MSG.

Solution

  • You can use a different type of loop to exclude the last worksheet:

    Dim wb As Workbook, strName As String, i As Long

set wb = ThisWorkbook 'or ActiveWorkbook or whatever...
For i = 1 to wb.Worksheets.Count-1
    strName = strName & wb.Worksheets(i).Name & "<br>"
Next