Emailing Worksheet with VBA Code

[David]

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

 

[Ron de Bruin]

Look good at the example on my site

ActiveSheet.Copy is after
Set wb = ActiveWorkbook

in your macro

I must go to work

 

[Shaka215]

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 recipients:="(e-mail address removed)"
End Sub

 

[David]

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!!

 

[David]

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]

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 "(e-mail address removed)", _
"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 "(e-mail address removed)", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

Hi ?

Read my reply to David