VBA Email copy as Values.

bombergirl61

New Member
Joined
Nov 19, 2014
Messages
7
I am using the below VBA email code and works great, only when it emails the worksheet to recipient it send the work sheet with all the formulas and when recipient opens the speadsheet some cells have not populated correctly.

this work book has 10 worksheets that all go to an individual recipient

Question - where in the is code do i add the pastespecial values. I have tried after the SH.Copy to no avail. Our do I need to change to destWB somewhere

Sub Z_Mail_SHEET_BUY_all_copy()
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim DueDate As String
Dim Body As String
Body = Format(ThisWorkbook.Sheets("National").Range("ad1").Value, "VB")

TempFilePath = Environ$("temp") & "\"

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2016
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set OutApp = CreateObject("Outlook.Application")

For Each sh In ThisWorkbook.Worksheets
If sh.Range("A1").Value Like "?*@?*.?*" Then


sh.Copy
Set wb = ActiveWorkbook


TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

Set OutMail = OutApp.CreateItem(0)

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next
With OutMail
.to = sh.Range("A1").Value
.CC = ""
.BCC = ""
.Subject = "SS SHEET and PLATE REQUIREMENTS"
.Body = Format(ThisWorkbook.Sheets("National").Range("Ad1").Value, "VB")
.Attachments.Add wb.FullName
.Display 'disable display and enable send to send automatically

End With
On Error GoTo 0

.Close savechanges:=False
End With

Set OutMail = Nothing

Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi bombergirl61,

replace these two lines in your code

VBA Code:
  sh.Copy
  Set wb = ActiveWorkbook

with these lines which copy the sheet to the last position in the workbook, convert everything to values and then move the sheet to a new workbook

VBA Code:
    sh.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    With ActiveSheet
      .UsedRange.Value = .UsedRange.Value
      .Name = sh.Name & Format(Now, "dd-mmm-yy h-mm-ss")
      .Move
    End With
    Set wb = ActiveWorkbook

Ciao,
Holger
 
Upvote 0
Solution
Hi bombergirl61,

replace these two lines in your code

VBA Code:
  sh.Copy
  Set wb = ActiveWorkbook

with these lines which copy the sheet to the last position in the workbook, convert everything to values and then move the sheet to a new workbook

VBA Code:
    sh.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    With ActiveSheet
      .UsedRange.Value = .UsedRange.Value
      .Name = sh.Name & Format(Now, "dd-mmm-yy h-mm-ss")
      .Move
    End With
    Set wb = ActiveWorkbook

Ciao,
Holger
Thank you so much this works perfectly. greatly appreciated
 
Upvote 0
Thank you so much this works perfectly. greatly appreciated
The marked solution post has been changed accordingly.

@bombergirl61: In your future questions, that would be great if you could mark the post as the solution that actually answered your question instead of your feedback post as it will help future readers. No further action is required for this thread.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top