June 1, 2020
You use a loop to go through each sheet in turn. Without seeing your workbook though I can’t be sure that what I’d write would work for you.
At the top of the code there’s a section headed ‘ ***** You Can Change These Variables ********* and in there is one called Email_To. Change this to pick up the email address you’re sending to. For example to send to an email address stored in cell H1 you’d write Email_To = Range(“H1”).Value
To add some body text add this line to With.OutlookMail
.body = “Some text.” & vbNewLine & vbNewLine & “A 2nd line of text.”
and modify the text as desired.
If you want to post your question on our forum and attach your workbook I can look at writing the loop for you.
I have attached a snapshot of my workbook. I have the macro to run if the user executes ctrl+A while the workbook is open, however, I would like Outlook to open with a designated email address based on whatever current "Invoice -" sheet the user was on when he/she executed crtl+A.
November 8, 2013