VBA to Send Outlook Emails with Excel Information

chicka7684

New Member
Joined
May 15, 2017
Messages
10
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!!!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
if you research activesheet, then that would suffice, also a FOR NEXT loop would constrain the sending
 

Watch MrExcel Video

Forum statistics

Threads
1,112,814
Messages
5,542,649
Members
410,566
Latest member
Jonniehoffman
Top