How to fix a macro so that it runs on the active sheet without giving it a name

[Jay3]

Hi All

In this macro when a task is complete I can select "Yes" from a drop down and it fires an email, but more importantly strikes through the task in the list.

I have defined the name of the worksheet which is causing issues as I am calling this module to replicate the same behaviours on a template which I am copying over and over.

Here's the code:

Option Explicit

Sub Confirm_Task_Complete(ByVal aRow As Long)
    
    Dim OutApp As Object ' Outlook.Application
    Dim OutMail As Object ' MailItem
    Dim strbody As String
    Dim wsData As Worksheet
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set wsData = ThisWorkbook.Sheets("New Task Not Requiring Board")
  
 
    
    strbody = "<font face='Arial,Helvetica' color='Black'>Dear " & wsData.Range("b" & aRow) & "<br><br>" & _
              "Your request relating to " & "<font color = 'Black'>" & Format(wsData.Range("c" & aRow).Value, "dd mmmm yyyy") & _
              " as outlined below has been completed by " & "<font color = 'red'><b>" & Format(wsData.Range("I" & aRow).Value, ".") & _
              "<font color='black'></b><br><br>" & _
              "Requirment: " & "<font color ='black'><br><br>" & wsData.Range("D" & aRow).Value & "</b></font>" & "<br><br>"
 
    
    
    
    
    On Error Resume Next
    With OutMail
        .To = wsData.Range("K" & aRow)
        .CC = ""
        .BCC = ""
        .Subject = wsData.Range("B" & aRow)
        .HTMLBody = strbody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    
    wsData.Range("B" & aRow & ":M" & aRow).Font.Strikethrough = True

    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

This line is the problem as sometimes the sheet is called something else:

Set wsData = ThisWorkbook.Sheets("New Task Not Requiring Board")

How can I fix this?

Thanks

 

[RoryA]

If it's the active sheet, use ThisWorkbook.Activesheet

 

[mikerickson]

Are you looking for sytax like

Set wsData = ActiveSheet

 

[Jay3]

Thanks both