Email Sheet

[Judsen Jones]

I have the following code as part of a sheet:

Private Sub Worksheet_Activate()
' CHANGES RFI# TO MATCH CURRENT SHEET NAME
Dim JUDD As String
Dim judd5
judd5 = ActiveCell.Address
JUDD = ActiveSheet.Name
Range("K8").Select
ActiveCell.FormulaR1C1 = Right(JUDD, 3)
Range(judd5).Select
End Sub


And I have the following to email the sheet:


Sub Mail_ActiveSheet()
Dim wb As Workbook

Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
' Names the worksheet the same as activesheet
.SaveAs ActiveSheet.Name & ".xls"
' Next line has "" which is a blank email. Allows you to send to whom you
want.
.SendMail "", _
ActiveSheet.Name
' "This is the Subject line" 'Type the subject in the previous quotes
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub



When I use the email macro it always excutes the first code and inserts the
value in the current cell. When this happens it is deleting information that
is needed.

Please help to make this quit.

Thanks in advance.

 

[Allllen]

Judsen, what are you trying to do?
What would you like to see happening? I don't understand enough.
If you don't like that first part, just delete that subroutine. Or change
this line
ActiveCell.FormulaR1C1 = Right(JUDD, 3)

 

[Jim Thomlinson]

Give this a try...

Sub Mail_ActiveSheet()
Dim wb As Workbook
On Error Goto ErrorHandler
Application.enableevents = false
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
' Names the worksheet the same as activesheet
.SaveAs ActiveSheet.Name & ".xls"
' Next line has "" which is a blank email. Allows you to send to whom you
want.
.SendMail "", _
ActiveSheet.Name
' "This is the Subject line" 'Type the subject in the previous quotes
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
ErrorHandler:
Application.ScreenUpdating = True
Application.enableevents = true
End Sub

 

[Judsen Jones]

I am using the first routine that links a cell value to the sheet name.

I am then using the second routine to email that sheet.

I want to be able to email the sheet and then have others input data and
email it back.

I can email the workbook if anyone wants it.