I have used Ron's code for using excel vba to open outlook and send a message to several people. Here is where I am stuck. The code uses the active sheet as the information to place into the body of an email, how do I loop through all the worksheets and create all new emails? Should I call them all by name in a loop? Then how do I use my named ranges to send out the emails and associate the correct email list to the worksheet? Does anybody have some ideas? Any help is much appreciated.
Set rng = Nothing
Set rng = ActiveSheet.UsedRange
' You can also use a sheet name here.
'Set rng = Sheets("YourSheet").UsedRange
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set rngeAddresses = Sheets("Email List").Range("dynamic_named_range1")
For Each rngeCell In rngeAddresses.Cells
strRecipients = strRecipients & ";" & rngeCell.Value
Next
Set rng = Nothing
Set rng = ActiveSheet.UsedRange
' You can also use a sheet name here.
'Set rng = Sheets("YourSheet").UsedRange
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Set rngeAddresses = Sheets("Email List").Range("dynamic_named_range1")
For Each rngeCell In rngeAddresses.Cells
strRecipients = strRecipients & ";" & rngeCell.Value
Next
