CODEWITHSUNDEEP
excelvbaoutlook
user3138025
user3138025

Reputation: 825

Activate a Worksheet from Outlook VBA

On a Windows 10 PC I'm using Office Professional Plus 2013-32 bit including Outlook and Excel. I have a Macro in Outlook that sends eMails.

That macro uses two workbooks: one as a data source, the other as a log.

I'm having trouble activating the data source worksheet. I don't get a VBA error, but when I test I discover that the activation failed.

See the line: varTest = ActiveSheet.Name. Here are some parts of the Macro:

 Public objEmailWorkbook         As Excel.Workbook
 Public objEmailWorksheet        As Excel.Worksheet
 Public objLogWorkbook           As Excel.Workbook
 Public objLogWorksheet          As Excel.Worksheet
Sub Send_Emails()
 Dim objExcelApp                 As Excel.Application
 Dim strLogFullName              As String
 'Instantiate an instance of the Excel application.
 Set objExcelApp = CreateObject("Excel.Application")
 strXlsPathName = Environ("USERPROFILE") & "\Documents\"
 strLogFileName = "email_log.xlsx"
 strLogFullName = strXlsPathName & strLogFileName
 'Instantiate the Log Workbook object
 Set objLogWorkbook = objExcelApp.Workbooks.Open(strLogFullName, True, False)
 'There's only one worksheet in the Log workbook.  Identify the Log sheet name.
 strLogSheetName = objLogWorkbook.Sheets(1).Name
 'Instantiate the Log Worksheet object
 Set objLogWorksheet = objLogWorkbook.Sheets(strLogSheetName)
 objLogWorksheet.Activate

 'Instantiate the eMail Address Workbook object
 strEmailFileName = "emailTest.csv"
 strEmailFullName = strXlsPathName & strLogFileName
  'Instantiate the Email Worksheet object
 Set objEmailWorkbook = objExcelApp.Workbooks.Open(strEmailFullName, True, False)
 'There is only one sheet within a workbook.
 'Determine the eMail address sheet name.
 strEmailSheetName = objEmailWorkbook.Sheets(1).Name

 'Instantiate the eMail address worksheet object
 Set objEmailWorksheet = objEmailWorkbook.Sheets(strEmailSheetName)
 objEmailWorksheet.Activate
 '***Other Processing Code Here
 '***lngLastSheetCol and strSheetFieldName values are set here
 'Re-activate objEmailWorksheet
 '***The following ACTIVATE command doesn't appear to work
 objEmailWorksheet.Activate
 varTest = ActiveSheet.Name
 'Define rngSearchText as the header row in the worksheet
 Set rngSearchText = ActiveSheet.Range(Cells(1, 1), Cells(1, lngLastSheetCol))
 With rngSearchText
   'Find the column in the worksheet for strSheetFieldName
   Set rngFoundText = .Find(What:=strSheetFieldName, LookAt:=xlWhole, _
                                         MatchCase:=False, SearchFormat:=False)
   If Not rngFoundText Is Nothing Then
     lngFoundTextCol = rngFoundText.Column
   End If
 End With
Exunt:
Set objEmailWorksheet = Nothing
Set objEmailWorkbook = Nothing
Set objLogWorksheet = Nothing
Set objLogWorkbook = Nothing
Set objExcelApp = Nothing
Set rngSearchText = Nothing
Set rngFoundText = Nothing

End Sub

The objEmailWorksheet.Activate command never works. When I step through the code, varTest has a value of "log". I know the value of the EMail worksheet is "emailTest".

Since the Email worksheet isn't activated, the following search doesn't return the right results.

Is there another way to activate the worksheet or otherwise solve the problem?

Upvotes: 1

Views: 1320

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You must specify the Excel application objExcelApp

varTest = objExcelApp.ActiveSheet.Name

I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

but instead of this I recommend the following

'objEmailWorksheet.Activate 'you don't need activate at all
'varTest = objEmailWorksheet.Name 'this line is not needed too

Set rngSearchText = objEmailWorksheet.Range(objEmailWorksheet.Cells(1, 1), objEmailWorksheet.Cells(1, lngLastSheetCol))

Specify for every Range and Cells in which worksheet the range or cell is. Otherwise Excel cannot know that. If you do that you never need to .Activate any sheets at all.

You might benefit from reading How to avoid using Select in Excel VBA. The same for .Activate it should be avoided where ever possible.

Also these 3 lines

 strLogSheetName = objLogWorkbook.Sheets(1).Name
 Set objLogWorksheet = objLogWorkbook.Sheets(strLogSheetName)
 'objLogWorksheet.Activate 'don't need to activate!

can be just reduced to 1 line only

 Set objLogWorksheet = objLogWorkbook.Sheets(1)

and these 2 lines

strEmailSheetName = objEmailWorkbook.Sheets(1).Name
Set objEmailWorksheet = objEmailWorkbook.Sheets(strEmailSheetName)

can be just reduced to 1 line only

Set objEmailWorksheet = objEmailWorkbook.Sheets(1)

Upvotes: 3

Related Questions