| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a single worksheet
I am trying to ise a command button to save a single worksheet from a
workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a single worksheet
Check out this link...
http://www.rondebruin.nl/sendmail.htm -- HTH... Jim Thomlinson "Shawn" wrote: I am trying to ise a command button to save a single worksheet from a workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a single worksheet
Hi Shawn
See http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... I am trying to ise a command button to save a single worksheet from a workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a single worksheet
ya'll are awesome...I have been working on this all day!
"Ron de Bruin" wrote: Hi Shawn See http://www.rondebruin.nl/sendmail.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... I am trying to ise a command button to save a single worksheet from a workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a single worksheet
Ron,
I used the script for Mil one sheet in Outlook object model (attachment). I am trying to apply this macro to a command button in the sheet. The command button applies: Private Sub CommandButton1_Click() Which is expecting and End command. If I delete that nothing happens when I try the button. What should I do? Also, if I use display under the lines for OutMail will it stop at the point where I will have to push the send button for the message to go? With OutMail .To = " .CC = "Central Lab" '.BCC = "" .Subject = "Central Lab Weekly WIG Update" .Body = "Weekly WIG Update" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display "Ron de Bruin" wrote: Hi Shawn See http://www.rondebruin.nl/sendmail.htm Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... I am trying to ise a command button to save a single worksheet from a workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a single worksheet
Use only this line in the click event
Call MacroName Also, if I use display under the lines for OutMail will it stop at the point where I will have to push the send button for the message to go? Correct Download the example workbook from my site and you will see it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... Ron, I used the script for Mil one sheet in Outlook object model (attachment). I am trying to apply this macro to a command button in the sheet. The command button applies: Private Sub CommandButton1_Click() Which is expecting and End command. If I delete that nothing happens when I try the button. What should I do? Also, if I use display under the lines for OutMail will it stop at the point where I will have to push the send button for the message to go? With OutMail .To = " .CC = "Central Lab" '.BCC = "" .Subject = "Central Lab Weekly WIG Update" .Body = "Weekly WIG Update" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display "Ron de Bruin" wrote: Hi Shawn See http://www.rondebruin.nl/sendmail.htm Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... I am trying to ise a command button to save a single worksheet from a workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a single worksheet
Hate to seem stupid but where do I insert Call MacroName and do I replace
Name with something else? "Ron de Bruin" wrote: Use only this line in the click event Call MacroName Also, if I use display under the lines for OutMail will it stop at the point where I will have to push the send button for the message to go? Correct Download the example workbook from my site and you will see it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... Ron, I used the script for Mil one sheet in Outlook object model (attachment). I am trying to apply this macro to a command button in the sheet. The command button applies: Private Sub CommandButton1_Click() Which is expecting and End command. If I delete that nothing happens when I try the button. What should I do? Also, if I use display under the lines for OutMail will it stop at the point where I will have to push the send button for the message to go? With OutMail .To = " .CC = "Central Lab" '.BCC = "" .Subject = "Central Lab Weekly WIG Update" .Body = "Weekly WIG Update" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display "Ron de Bruin" wrote: Hi Shawn See http://www.rondebruin.nl/sendmail.htm Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... I am trying to ise a command button to save a single worksheet from a workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving a single worksheet
If the name of the mail macro in your standard module = MailSingleSheet
Then the click event in the sheet module looks like this Private Sub CommandButton1_Click() call MailSingleSheet End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... Hate to seem stupid but where do I insert Call MacroName and do I replace Name with something else? "Ron de Bruin" wrote: Use only this line in the click event Call MacroName Also, if I use display under the lines for OutMail will it stop at the point where I will have to push the send button for the message to go? Correct Download the example workbook from my site and you will see it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... Ron, I used the script for Mil one sheet in Outlook object model (attachment). I am trying to apply this macro to a command button in the sheet. The command button applies: Private Sub CommandButton1_Click() Which is expecting and End command. If I delete that nothing happens when I try the button. What should I do? Also, if I use display under the lines for OutMail will it stop at the point where I will have to push the send button for the message to go? With OutMail .To = " .CC = "Central Lab" '.BCC = "" .Subject = "Central Lab Weekly WIG Update" .Body = "Weekly WIG Update" .Attachments.Add Destwb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Send 'or use .Display "Ron de Bruin" wrote: Hi Shawn See http://www.rondebruin.nl/sendmail.htm Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Shawn" wrote in message ... I am trying to ise a command button to save a single worksheet from a workbook to export to outlook and mail it. So far I have the following which works fine but mails the whole workbook. My code is not good so details are appreciated. Private Sub CommandButton1_Click() ' '' Email Sheet ' Taken from Dustin's timesheet macro ' 1/28/08 ' Dim Fname Fname = ActiveSheet.Name Range("A1:N41").Select Selection.Copy 'Sheets.Add Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayGridlines = False 'added Application.CutCopyMode = False 'added Application.StandardFont = "Tahoma" Application.StandardFontSize = "10" Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy Application.CutCopyMode = False Selection.Copy ActiveSheet.Paste With ActiveSheet.PageSetup .TopMargin = Application.InchesToPoints(0.5) .BottomMargin = Application.InchesToPoints(0.25) End With ChDir "C:\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="C:\SHD_current_week.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True Application.Dialogs(xlDialogSendMail).Show End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| merging single worksheet files into a single workbook | Excel Discussion (Misc queries) | |||
| Saving a single worksheet as new file before close | Excel Programming | |||
| Save a single worksheet in Excel as a single file. | New Users to Excel | |||
| Saving a single Worksheet w/o redundant prompts | Excel Programming | |||
| saving single worksheet | Excel Programming | |||
Hybrid Mode
