Looping struggles....and flash fill errors

[chefdt]

Trying to put together some code that will loop through all sheets in a workbook, and after converting the email address in A2 from lastname, firstname to an email address, send the email.

I get a flash fill error that will come up, or I get success, but only with the first sheet.

I'm not very good at this yet. Any help will be appreciated. My code to change the address in A2 is from a recording.

Sub Email_Sheet()




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




For Each WS In ThisWorkbook.Sheets






'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




Next

 

[chefdt]

Figured out the Flash Fill Issue, but still struggling with looping. The email code seems very elegant and fast. Works perfectly!

I've tried the standard ;

For each ws in thisworkbook.sheets
next ws

Not working! It will only send the first sheet.