Need vba help to run on active sheet, offset function, insert spacing, signature & email

Dear vba experts,

I managed to make a vba worked in a single sheet environment to send email automatically but it cannot work on an active worksheet when there are multiple worksheets. (prompt OutLookMailItem.Send error in this line)

I need to use offset command as the number of rows is not fixed for every month but don't know how to change this. The data will be filtered by date but the test vba sends email to all records, even if it's not the wanted data (meaning excluded from the wanted information using the filtering of the dates)

On the other hand, I want to arrange the body text with line spacing and it can only work on the first line with this code vbCrLf.

How to send emails with Outlook that has more than 1 email and set a default email with Signature (which already programmed in Outlook but could not work in the vba).

I need the data to looks like this in the email body:


Dear ABC Company, Andy,
The following is due for renewal:
YN2038J , expiring on 31 Aug 2016.
Please renew before due date.


Hoped that someone can helps.

Sub SendEmail(what_address As String, subject_line As String, mail_body As String)

Dim OutLookApp As Object
Set OutLookApp = CreateObject("OutLook.application")

    Dim OutLookMailItem As Object
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    
    OutLookMailItem.To = what_address
    OutLookMailItem.Subject = subject_line
    OutLookMailItem.Body = mail_body
    OutLookMailItem.Send
    
End Sub

Sub SendMassEmail()

row_number = 1

Do
DoEvents
    row_number = row_number + 1
    Dim mail_body_message As String
    Dim customer_name As String
    Dim valid_to As String
    Dim license_to As String
    
    mail_body_message = ActiveSheet.Range("U2")
    customer_name = ActiveSheet.Range("E" & row_number) & ", " & ActiveSheet.Range("F" & row_number) & ", " & vbCrLf & vbCrLf
    license_to = ActiveSheet.Range("D" & row_number)
    valid_to = ActiveSheet.Range("C" & row_number)
    mail_body_message = Replace(mail_body_message, "replace_name_here", customer_name)
    mail_body_message = Replace(mail_body_message, "license_to_replace", license_to)
    mail_body_message = Replace(mail_body_message, "valid_to_replace", valid_to)
        MsgBox mail_body_message
        
     Call SendEmail(ActiveSheet.Range("J" & row_number), "This is a Renewal Reminder for " & license_to, mail_body_message)
Loop Until row_number = 20
    
End Sub

The code looks like it will work just fine and will run on whatever the active worksheet is.

Do you wish to specify a sheet and have it run against that sheet no matter where you are in the workbook?

if so then do this with the code

dim sh as worksheet
....
set sh = Sheets(SheetName) ' You will have to figure out how to get SheetName. Use a cell in the workbook or inputbox or calculate it based on date.
...
' Wherever you see activeworksheet, replace it with sh.

You might also want to throw in some code to find the real last row number instead of hard coding it to 20.