Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email Sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email Sheet
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) -- Allllen "Judsen Jones" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email Sheet
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 -- HTH... Jim Thomlinson "Judsen Jones" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Email Sheet
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. "Allllen" wrote: 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) -- Allllen "Judsen Jones" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
send email to each customer email in excel sheet. | Excel Discussion (Misc queries) | |||
One Sheet by email | Excel Programming | |||
eMail a sheet | Excel Programming | |||
eMail a sheet | Excel Programming | |||
eMail a sheet | Excel Programming |