 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Saving 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 Check out this link... http://www.rondebruin.nl/sendmail.htm  SignatureHTH... Jim Thomlinson > 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 [quoted text clipped - 39 lines] > Application.Dialogs(xlDialogSendMail).Show > End Sub Hi Shawn See http://www.rondebruin.nl/sendmail.htm SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm >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 [quoted text clipped - 39 lines] > Application.Dialogs(xlDialogSendMail).Show > End Sub ya'll are awesome...I have been working on this all day! > Hi Shawn > [quoted text clipped - 44 lines] > > Application.Dialogs(xlDialogSendMail).Show > > End Sub 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 = "mark.maycock@gbi.state.ga.us" .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 > Hi Shawn > > See > http://www.rondebruin.nl/sendmail.htm > Regards Ron de Bruin > http://www.rondebruin.nl/tips.htm [quoted text clipped - 42 lines] > > Application.Dialogs(xlDialogSendMail).Show > > End Sub 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 SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > Ron, > I used the script for Mil one sheet in Outlook object model (attachment). I [quoted text clipped - 67 lines] >> > Application.Dialogs(xlDialogSendMail).Show >> > End Sub Hate to seem stupid but where do I insert Call MacroName and do I replace Name with something else? > Use only this line in the click event > [quoted text clipped - 77 lines] > >> > Application.Dialogs(xlDialogSendMail).Show > >> > End Sub 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 SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > Hate to seem stupid but where do I insert Call MacroName and do I replace > Name with something else? [quoted text clipped - 80 lines] >> >> > Application.Dialogs(xlDialogSendMail).Show >> >> > End Sub I changed the button from command button to a tool bar button so it would not show on the emailed page. Now I need to change from ActiveSheet to a designated sheet or the button may send the wrong sheet. Also I am trying to set up an additional button that will mail the first three worksheets in the workbook. I looked at your instructions for mailing multiple sheets and I don't understand where I substitute in the macro the sheet names that I need to be mailed. Help again please! > If the name of the mail macro in your standard module = MailSingleSheet > [quoted text clipped - 88 lines] > >> >> > Application.Dialogs(xlDialogSendMail).Show > >> >> > End Sub http://www.rondebruin.nl/mail/folder1/mail3.htm Read the info above the macro This sub will send a newly created workbook with just the sheets in the Array. Sourcewb.Sheets(Array("Sheet1", "Sheet3")).Copy SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm >I changed the button from command button to a tool bar button so it would not > show on the emailed page. Now I need to change from ActiveSheet to a [quoted text clipped - 96 lines] >> >> >> > Application.Dialogs(xlDialogSendMail).Show >> >> >> > End Sub Sourcewb.Sheets(Array("Index", "MailSheet(s)")).Copy In the above line do I substitute the name of my pages in "Index" and leave Mailsheet(s) in. Also do I use the name of my sheets or Sheet1,Sheet2..etc. I can't seem to get it to work on this one > http://www.rondebruin.nl/mail/folder1/mail3.htm > [quoted text clipped - 104 lines] > >> >> >> > Application.Dialogs(xlDialogSendMail).Show > >> >> >> > End Sub You use the names of the sheets you want to send SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > Sourcewb.Sheets(Array("Index", "MailSheet(s)")).Copy > In the above line do I substitute the name of my pages in "Index" and leave [quoted text clipped - 109 lines] >> >> >> >> > Application.Dialogs(xlDialogSendMail).Show >> >> >> >> > End Sub What is the real difference between the macro for sending multiple pages with the SendMail method compared to the ones for sending multiples using Outlook object model. I can't really tell the difference and maybe that is why I cannot get it to work on my end. > You use the names of the sheets you want to send > [quoted text clipped - 111 lines] > >> >> >> >> > Application.Dialogs(xlDialogSendMail).Show > >> >> >> >> > End Sub Above the SendMail macro you can read it Note: With SendMail it is not possible to 1) Send text in the Body of the mail 2) Use the CC or BCC field 3) Attach other files Send me the file private and tell me which sheets you want to mail SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm > What is the real difference between the macro for sending multiple pages with > the SendMail method compared to the ones for sending multiples using Outlook [quoted text clipped - 116 lines] >> >> >> >> >> > Application.Dialogs(xlDialogSendMail).Show >> >> >> >> >> > End Sub I have sent it to the email address on your website! Thanks again! > Above the SendMail macro you can read it > [quoted text clipped - 125 lines] > >> >> >> >> >> > Application.Dialogs(xlDialogSendMail).Show > >> >> >> >> >> > End Sub I send you the file back with a example SignatureRegards Ron de Bruin http://www.rondebruin.nl/tips.htm >I have sent it to the email address on your website! Thanks again! > [quoted text clipped - 127 lines] >> >> >> >> >> >> > Application.Dialogs(xlDialogSendMail).Show >> >> >> >> >> >> > End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.