Emailing Worksheet with VBA Code
I'm having a tough time figuring out how to email a single worksheet that
used the PriorSheet Function. I've tried Ron's email and tips on a single sheet, but when I email the sheets with the priorsheet function, I get all errors in the cells. The email works just fine, just need to get the real data pasted into the email copy. I really need to fix this before tomorrow morning. Thanks for any help! Here's the code I'm using with Ron's in there notated: Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim FileNameEmail As String Dim Location As String Dim LocationNum As String Dim ForDate As Date Dim MyArr As Variant MyArr = Sheets("Setup").Range("Email") strdate = Format(Now, "mm-dd-yy") Application.ScreenUpdating = False Location = ActiveSheet.Range("B3") LocationNum = ActiveSheet.Range("B4") ForDate = ActiveSheet.Range("I4") FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Set wb = ActiveWorkbook With wb 'Ron's Code Starts Here ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False 'Ron's Code Stops Here .SaveAs "Daily " & FileNameEmail & " saved on " & strdate & ".xls" .SendMail MyArr, "Daily DMR from " & Location & "-" & LocationNum & " for " & ForDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub |
Emailing Worksheet with VBA Code
Look good at the example on my site
ActiveSheet.Copy is after Set wb = ActiveWorkbook in your macro I must go to work -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I'm having a tough time figuring out how to email a single worksheet that used the PriorSheet Function. I've tried Ron's email and tips on a single sheet, but when I email the sheets with the priorsheet function, I get all errors in the cells. The email works just fine, just need to get the real data pasted into the email copy. I really need to fix this before tomorrow morning. Thanks for any help! Here's the code I'm using with Ron's in there notated: Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim FileNameEmail As String Dim Location As String Dim LocationNum As String Dim ForDate As Date Dim MyArr As Variant MyArr = Sheets("Setup").Range("Email") strdate = Format(Now, "mm-dd-yy") Application.ScreenUpdating = False Location = ActiveSheet.Range("B3") LocationNum = ActiveSheet.Range("B4") ForDate = ActiveSheet.Range("I4") FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Set wb = ActiveWorkbook With wb 'Ron's Code Starts Here ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False 'Ron's Code Stops Here .SaveAs "Daily " & FileNameEmail & " saved on " & strdate & ".xls" .SendMail MyArr, "Daily DMR from " & Location & "-" & LocationNum & " for " & ForDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub |
Emailing Worksheet with VBA Code
I experianced a similar issue with this in the past...Ron's code, no
offense, is confusing and a simple code like below will get the job done...If you need a single sheet to be sent then consider writting a macro to copy the sheet to a new workbook and then name the work book something in the macro and then use the code below to send the file... Sub Email() ActiveWorkbook.SendMail " End Sub David wrote: I'm having a tough time figuring out how to email a single worksheet that used the PriorSheet Function. I've tried Ron's email and tips on a single sheet, but when I email the sheets with the priorsheet function, I get all errors in the cells. The email works just fine, just need to get the real data pasted into the email copy. I really need to fix this before tomorrow morning. Thanks for any help! Here's the code I'm using with Ron's in there notated: Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim FileNameEmail As String Dim Location As String Dim LocationNum As String Dim ForDate As Date Dim MyArr As Variant MyArr = Sheets("Setup").Range("Email") strdate = Format(Now, "mm-dd-yy") Application.ScreenUpdating = False Location = ActiveSheet.Range("B3") LocationNum = ActiveSheet.Range("B4") ForDate = ActiveSheet.Range("I4") FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Set wb = ActiveWorkbook With wb 'Ron's Code Starts Here ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False 'Ron's Code Stops Here .SaveAs "Daily " & FileNameEmail & " saved on " & strdate & ".xls" .SendMail MyArr, "Daily DMR from " & Location & "-" & LocationNum & " for " & ForDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub |
Emailing Worksheet with VBA Code
Thank you very much. I had to find a solution and I finally figured out how
Ron's code worked, I had it in the wrong place. You're right though, it was simple, but the example and instructions were terrible confusing. Took me four hours last night to resolve the issue. Then, Ron's code just copies everything and then paste it back on top of itself, wiping out the variables on that sheet. That was no good. So what I did was add a formatted sheet that is an exact copy of the source sheets, hide it, and unhide it in the macro, copy the target sheet to the copy sheet as paste values and email that worksheet, and then hide it again, that way my original work is not affected. Thanks again!! " wrote: I experianced a similar issue with this in the past...Ron's code, no offense, is confusing and a simple code like below will get the job done...If you need a single sheet to be sent then consider writting a macro to copy the sheet to a new workbook and then name the work book something in the macro and then use the code below to send the file... Sub Email() ActiveWorkbook.SendMail " End Sub David wrote: I'm having a tough time figuring out how to email a single worksheet that used the PriorSheet Function. I've tried Ron's email and tips on a single sheet, but when I email the sheets with the priorsheet function, I get all errors in the cells. The email works just fine, just need to get the real data pasted into the email copy. I really need to fix this before tomorrow morning. Thanks for any help! Here's the code I'm using with Ron's in there notated: Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim FileNameEmail As String Dim Location As String Dim LocationNum As String Dim ForDate As Date Dim MyArr As Variant MyArr = Sheets("Setup").Range("Email") strdate = Format(Now, "mm-dd-yy") Application.ScreenUpdating = False Location = ActiveSheet.Range("B3") LocationNum = ActiveSheet.Range("B4") ForDate = ActiveSheet.Range("I4") FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Set wb = ActiveWorkbook With wb 'Ron's Code Starts Here ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False 'Ron's Code Stops Here .SaveAs "Daily " & FileNameEmail & " saved on " & strdate & ".xls" .SendMail MyArr, "Daily DMR from " & Location & "-" & LocationNum & " for " & ForDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub |
Emailing Worksheet with VBA Code
You're right. Thanks much again. I added another sheet and after the copy
pasted to that sheet so I don't overwrite my original formulas. You might put a disclaimer in your example that your code does this. This is what I ended up with: Set sh = ActiveSheet Cells.Copy Sheets("Copy").Select Set sh2 = ActiveSheet Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False sh2.Select Range("A1").Select 'To make sure the copy selection is removed ActiveSheet.Copy 'etc "Ron de Bruin" wrote: Look good at the example on my site ActiveSheet.Copy is after Set wb = ActiveWorkbook in your macro I must go to work -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I'm having a tough time figuring out how to email a single worksheet that used the PriorSheet Function. I've tried Ron's email and tips on a single sheet, but when I email the sheets with the priorsheet function, I get all errors in the cells. The email works just fine, just need to get the real data pasted into the email copy. I really need to fix this before tomorrow morning. Thanks for any help! Here's the code I'm using with Ron's in there notated: Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim FileNameEmail As String Dim Location As String Dim LocationNum As String Dim ForDate As Date Dim MyArr As Variant MyArr = Sheets("Setup").Range("Email") strdate = Format(Now, "mm-dd-yy") Application.ScreenUpdating = False Location = ActiveSheet.Range("B3") LocationNum = ActiveSheet.Range("B4") ForDate = ActiveSheet.Range("I4") FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Set wb = ActiveWorkbook With wb 'Ron's Code Starts Here ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False 'Ron's Code Stops Here .SaveAs "Daily " & FileNameEmail & " saved on " & strdate & ".xls" .SendMail MyArr, "Daily DMR from " & Location & "-" & LocationNum & " for " & ForDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub |
Emailing Worksheet with VBA Code
You're right. Thanks much again. I added another sheet and after the copy
pasted to that sheet so I don't overwrite my original formulas. You might put a disclaimer in your example that your code does this. ??????????????? My code copy the activesheet to a new workbook and do nothing with your original workbook Code from my site : Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub If you want to make values then use this Sub Mail_ActiveSheet_2() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False Set wb = ActiveWorkbook With wb .SaveAs "Part of " & ThisWorkbook.Name _ & " " & strdate & ".xls" .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... You're right. Thanks much again. I added another sheet and after the copy pasted to that sheet so I don't overwrite my original formulas. You might put a disclaimer in your example that your code does this. This is what I ended up with: Set sh = ActiveSheet Cells.Copy Sheets("Copy").Select Set sh2 = ActiveSheet Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("A1").Select Application.CutCopyMode = False sh2.Select Range("A1").Select 'To make sure the copy selection is removed ActiveSheet.Copy 'etc "Ron de Bruin" wrote: Look good at the example on my site ActiveSheet.Copy is after Set wb = ActiveWorkbook in your macro I must go to work -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... I'm having a tough time figuring out how to email a single worksheet that used the PriorSheet Function. I've tried Ron's email and tips on a single sheet, but when I email the sheets with the priorsheet function, I get all errors in the cells. The email works just fine, just need to get the real data pasted into the email copy. I really need to fix this before tomorrow morning. Thanks for any help! Here's the code I'm using with Ron's in there notated: Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim FileNameEmail As String Dim Location As String Dim LocationNum As String Dim ForDate As Date Dim MyArr As Variant MyArr = Sheets("Setup").Range("Email") strdate = Format(Now, "mm-dd-yy") Application.ScreenUpdating = False Location = ActiveSheet.Range("B3") LocationNum = ActiveSheet.Range("B4") ForDate = ActiveSheet.Range("I4") FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Set wb = ActiveWorkbook With wb 'Ron's Code Starts Here ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False 'Ron's Code Stops Here .SaveAs "Daily " & FileNameEmail & " saved on " & strdate & ".xls" .SendMail MyArr, "Daily DMR from " & Location & "-" & LocationNum & " for " & ForDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub |
Emailing Worksheet with VBA Code
Hi ?
Read my reply to David -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... I experianced a similar issue with this in the past...Ron's code, no offense, is confusing and a simple code like below will get the job done...If you need a single sheet to be sent then consider writting a macro to copy the sheet to a new workbook and then name the work book something in the macro and then use the code below to send the file... Sub Email() ActiveWorkbook.SendMail " End Sub David wrote: I'm having a tough time figuring out how to email a single worksheet that used the PriorSheet Function. I've tried Ron's email and tips on a single sheet, but when I email the sheets with the priorsheet function, I get all errors in the cells. The email works just fine, just need to get the real data pasted into the email copy. I really need to fix this before tomorrow morning. Thanks for any help! Here's the code I'm using with Ron's in there notated: Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String Dim FileNameEmail As String Dim Location As String Dim LocationNum As String Dim ForDate As Date Dim MyArr As Variant MyArr = Sheets("Setup").Range("Email") strdate = Format(Now, "mm-dd-yy") Application.ScreenUpdating = False Location = ActiveSheet.Range("B3") LocationNum = ActiveSheet.Range("B4") ForDate = ActiveSheet.Range("I4") FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) Set wb = ActiveWorkbook With wb 'Ron's Code Starts Here ActiveSheet.Copy Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False 'Ron's Code Stops Here .SaveAs "Daily " & FileNameEmail & " saved on " & strdate & ".xls" .SendMail MyArr, "Daily DMR from " & Location & "-" & LocationNum & " for " & ForDate .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub |
All times are GMT +1. The time now is 12:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com