Loop through all worksheets - execute code

[chefdt]

I have a simple email code that changes A2 from "lastname, firstname" and then emails the worksheet as an attachment. It works great. Now I want to be able to send all worksheets in the workbook, using the same method.

I've tried the standard:

For each ws in thisworkbook.sheets
next ws

It doesnt work. It send the first sheet and then stops. Can anyone help me figure this out?

Sub Email_Sheet()

Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String


'Turn off screen updating
Application.ScreenUpdating = False




'Format Email Address in A2. Can change to any cell.
ActiveSheet.Range("A2").Select
ActiveCell.Replace What:=", ", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


'Copy the active worksheet and save to a temporary workbook
ActiveSheet.Copy
Set LWorkbook = ActiveWorkbook


'Create a temporary file in your current directory that uses the name
' of the sheet as the filename
LFileName = LWorkbook.Worksheets(1).Name
On Error Resume Next
'Delete the file if it already exists
Kill LFileName
On Error GoTo 0
'Save temporary file
LWorkbook.SaveAs FileName:=LFileName


'Create an Outlook object and new mail message
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)


'Set mail attributes (uncomment lines to enter attributes)
' In this example, only the attachment is being added to the mail message
With oMail
.To = Range("A2").Value & "@ok.sysco.com"
.Subject = "THIS IS A TEST"
'.body = "This is the body of the message." & vbCrLf & vbCrLf & _
'"Attached is the file"
.Attachments.Add LWorkbook.FullName
.Send
End With


'Delete the temporary file and close temporary Workbook
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False




'Turn back on screen updating
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing


End Sub

 

[njimack]

As well as using For Each ws, you also need to change your code so that it doesn't refer to the ActiveSheet. For example (untested)...

Sub Email_Sheet()


Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String
Dim ws As Worksheet




'Turn off screen updating
Application.ScreenUpdating = False


For Each ws In ThisWorkbook.Worksheets
[COLOR=#ff0000][B]    With ws[/B][/COLOR]


    'Format Email Address in A2. Can change to any cell.
        [COLOR=#ff0000][B]ws.[/B][/COLOR]Range("A2").Replace What:=", ", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
    
    'Copy the active worksheet and save to a temporary workbook
[COLOR=#ff0000][B]        .Copy
    End With[/B][/COLOR]
    Set LWorkbook = ActiveWorkbook




    'Create a temporary file in your current directory that uses the name
    ' of the sheet as the filename
    LFileName = LWorkbook.Worksheets(1).Name
    On Error Resume Next
    'Delete the file if it already exists
    Kill LFileName
    On Error GoTo 0
    'Save temporary file
    LWorkbook.SaveAs Filename:=LFileName
    
    
    'Create an Outlook object and new mail message
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    
    'Set mail attributes (uncomment lines to enter attributes)
    ' In this example, only the attachment is being added to the mail message
    With oMail
    .To = Range("A2").Value & "@ok.sysco.com"
    .Subject = "THIS IS A TEST"
    '.body = "This is the body of the message." & vbCrLf & vbCrLf & _
    '"Attached is the file"
    .Attachments.Add LWorkbook.FullName
    .Send
    End With
    
    
    'Delete the temporary file and close temporary Workbook
    LWorkbook.ChangeFileAccess Mode:=xlReadOnly
    Kill LWorkbook.FullName
    LWorkbook.Close SaveChanges:=False
    
    
    
    
    'Turn back on screen updating
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
Next ws




End Sub

 

[chefdt]

It stopped at the

.send

The error message was that it "didn't recognize the name".....