Run VBScript on all sheets not just 1

audioguy

New Member
Joined
Jun 16, 2010
Messages
8
I'm trying to create a script that will Email a range of each page to an Email address in a column.
I've got a script that will Email 1 page at a time, but I would like for it to run as a batch (up to 200 worksheets in the book)

Heres what I've got that works perfectly for Sheet 1, but I want it to ignore the name of the sheet and just run the script on each.
Code:
Sub EmailRange()

Dim MailSelection As Object
Dim cell As Range
Dim Subject As String
Dim EmailAddress As String



ThisWorkbook.Sheets("Sheet1").Range("B6:o11").Copy



Set OutlookApp = CreateObject("Outlook.Application")

For Each cell In _
Columns("B").Cells
If cell.Value Like "*@*" Then
    Subject = "Subject"
    EmailAddress = cell.Value
    
    Set MailSelection = OutlookApp.CreateItem(0)
    With MailSelection
        .To = EmailAddress
        .Subject = Subject
        .Display
        SendKeys "^({v})", True
        End With
    End If
Next
End Sub

Like I said- this works well for Sheet1, but I need it for ALL sheets- is there a simple tweak to this line: ThisWorkbook.Sheets("Sheet1") to make it work?

Thanks for such an amazing resource.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Maybe try this, but NOTE it will go through every sheet in the workbook.

Code:
Sub EmailRange()
 
Dim MailSelection As Object
Dim cell As Range
Dim Subject As String
Dim EmailAddress As String
 
Set OutlookApp = CreateObject("Outlook.Application")
 
for each ws in ThisWorkbook.WorkSheets
ws.Range("B6:o11").Copy
 
For Each cell In _
ws.Columns("B").Cells
If cell.Value Like "*@*" Then
    Subject = "Subject"
    EmailAddress = cell.Value
 
    Set MailSelection = OutlookApp.CreateItem(0)
    With MailSelection
        .To = EmailAddress
        .Subject = Subject
        .Display
        SendKeys "^({v})", True
        End With
    End If
Next cell
next ws
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top