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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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