VBA to Send Outlook Emails with Excel Information

[chicka7684]

I've figured out how to send an Outlook email with VBA (I'm a newbie with VBA), using specific information out of an Excel worksheet.

My Excel worksheet has the following information that I'm using in the VBA:

Column H = Email To
Column I = Email CC
Column J = Email Subject

The VBA code I have, that works for only 1 cell, is this:

Sub ABSInvoices()

Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem

Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)

myMail.To = Sheet1.Cells(2, 8)
myMail.CC = Sheet1.Cells(2, 9)
myMail.Subject = Sheet1.Cells(2, 10)
myMail.Body = "To Whom it May Concern," & Chr(10) & Chr(10) & "Attached is your invoice for review and payment. If you have any questions or concerns regarding payment, please contact our AR Department. Thank you!"

myMail.Display True

End Sub

I have this set to Display instead of Send because I need to attach different invoices to each email.

What I need is for this to Loop until I get to a row that is blank. I can end up with 1 row of information or 100 at any time I run this reporting... I just have no clue how to work in the Loop function.

Also, will this work for any worksheet that I create or only for the one I'm working with now? Is there a way to create a macro that would work with any worksheet I create?

Thanks in advance!!!

 

[mole999]

if you research activesheet, then that would suffice, also a FOR NEXT loop would constrain the sending