VBA Code to e-mail active Excel worksheet

Question

Hello,

I need help. I'm looking for a VBA code that allows me to e-mail one worksheet (active worksheet) as attachment but the information must be sent as value. I don't know much about VBA and the few codes I've found do the work but they also copy the formulas. File has more than one worksheet.

Thank you

Byron

Answer 1

Looks like you replied twice. Did the you have a problem with posting.

The following modified code is untested but should do what you want.

Sub EmailWithOutlook()

    'Variable declaration

    Dim oApp As Object, _

    oMail As Object, _

    WB As Workbook, _

    FileName As String

    'Turn off screen updating

    Application.ScreenUpdating = False

    'Make a copy of the active sheet and save it to

    'a temporary file

    ActiveSheet.Copy

    Set WB = ActiveWorkbook

   'New workbook will have data on worksheet(1).

'Cells is the entire worksheet range of new worksheet

'Copy and PasteSpecial -> Values for entire worksheet

    With WB.Worksheets(1).Cells

.Copy

.PasteSpecial xlPasteValues

'Entire worksheet will remain selected so select just first cell

.Range("A1").Select

End With

    FileName = "Name of the workbook.xls"

    'On Error Resume Next

    'Kill "C:" & FileName

    'On Error GoTo 0

    WB.SaveAs FileName:="C:" & FileName

    'Create and show the outlook mail item

    Set oApp = CreateObject("Outlook.Application")

    Set oMail = oApp.CreateItem(0)

    With oMail

    'Uncomment the line below to hard code a recipient

    '.To = "*** Email address is removed for privacy ***"

    'Uncomment the line below to hard code a subject

    '.Subject = "Look at my workbook!"

    .Attachments.Add WB.FullName

    .Display

    End With

    'Delete the temporary file

    WB.ChangeFileAccess Mode:=xlReadOnly

    'Kill WB.FullName

    'WB.Close SaveChanges:=False

    'Restore screen updating and release Outlook

    Application.ScreenUpdating = True

    Set oMail = Nothing

    Set oApp = Nothing

 End Sub

Answer 2

Hi,

This is the code I have. It creates a new workbook with only the active sheet but it also copies the formulas. I want it to be only values.

Sub EmailWithOutlook()

     'Variable declaration

    Dim oApp As Object, _

    oMail As Object, _

    WB As Workbook, _

    FileName As String

     'Turn off screen updating

    Application.ScreenUpdating = False

     'Make a copy of the active sheet and save it to

     'a temporary file

    ActiveSheet.Copy

    Set WB = ActiveWorkbook

    FileName = "Name of the workbook.xls"

    On Error Resume Next

    Kill "C:" & FileName

    On Error GoTo 0

    WB.SaveAs FileName:="C:" & FileName

     'Create and show the outlook mail item

    Set oApp = CreateObject("Outlook.Application")

    Set oMail = oApp.CreateItem(0)

    With oMail

         'Uncomment the line below to hard code a recipient

         '.To = "******@somedomain.com"

         'Uncomment the line below to hard code a subject

         '.Subject = "Look at my workbook!"

        .Attachments.Add WB.FullName

        .Display

    End With

  With Dest.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial Paste:=xlPasteValues

.Cells(1).PasteSpecial Paste:=xlPasteFormats

.Cells(1).Select

Application.CutCopyMode = False

End With

     'Delete the temporary file

    WB.ChangeFileAccess Mode:=xlReadOnly

    Kill WB.FullName

    WB.Close SaveChanges:=False

     'Restore screen updating and release Outlook

    Application.ScreenUpdating = True

    Set oMail = Nothing

    Set oApp = Nothing

End Sub

The lines in bold were added from another code hopefully to do what I want but it doesn't

Answer 3

Hi,

This is the code I have. It creates a new workbook with only the active sheet but it also copies the formulas. I want it to be only values.

Sub EmailWithOutlook()

     'Variable declaration

    Dim oApp As Object, _

    oMail As Object, _

    WB As Workbook, _

    FileName As String

     'Turn off screen updating

    Application.ScreenUpdating = False

     'Make a copy of the active sheet and save it to

     'a temporary file

    ActiveSheet.Copy

    Set WB = ActiveWorkbook

    FileName = "Name of the workbook.xls"

    On Error Resume Next

    Kill "C:" & FileName

    On Error GoTo 0

    WB.SaveAs FileName:="C:" & FileName

     'Create and show the outlook mail item

    Set oApp = CreateObject("Outlook.Application")

    Set oMail = oApp.CreateItem(0)

    With oMail

         'Uncomment the line below to hard code a recipient

         '.To = "******@somedomain.com"

         'Uncomment the line below to hard code a subject

         '.Subject = "Look at my workbook!"

        .Attachments.Add WB.FullName

        .Display

    End With

  With Dest.Sheets(1)

.Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial Paste:=xlPasteValues

.Cells(1).PasteSpecial Paste:=xlPasteFormats

.Cells(1).Select

Application.CutCopyMode = False

End With

     'Delete the temporary file

    WB.ChangeFileAccess Mode:=xlReadOnly

    Kill WB.FullName

    WB.Close SaveChanges:=False

     'Restore screen updating and release Outlook

    Application.ScreenUpdating = True

    Set oMail = Nothing

    Set oApp = Nothing

End Sub

The lines in bold were added from another code hopefully to do what I want but it doesn't

Answer 4

Hi Byron,

I need help. I'm looking for a VBA code that allows me to e-mail one worksheet (active worksheet) as attachment but the information must be sent as value. I don't know much about VBA and the few codes I've found do the work but they also copy the formulas. File has more than one worksheet.

Try as follows:

• Alt+F11 to open the VBA editor
• Alt+IM to insert a new code module
• In the new module, paste the following code by Ron de Bruin https://www.rondebruin.nl/win/s1/outlook/amail2.htm '=========>>Option ExplicitSub Mail_ActiveSheet() '\ Working in Excel 2000-2016 '\ For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
  
    Set Sourcewb = ActiveWorkbook
  
    'Copy the ActiveSheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
  
    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2016
            Select Case Sourcewb.FileFormat
            Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
            Case 52:
                If .HasVBProject Then
                    FileExtStr = ".xlsm": FileFormatNum = 52
                Else
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If
            Case 56: FileExtStr = ".xls": FileFormatNum = 56
            Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        End If
    End With
  
        'Change all cells in the worksheet to values if you want
        With Destwb.Sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
  
    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
  
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
  
    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .to = "******@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = "Hi there"
            .Attachments.Add Destwb.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
        .Close savechanges:=False
    End With
  
    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr
  
    Set OutMail = Nothing
    Set OutApp = Nothing
  
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
  

  End Sub '<<========= Alt+Q to close the VBA editor and return to Excel Save the file as a macro-enabled workbook with an xlsm extension Alt+F8 to open the macro window Select Tester | Run

===

Regards,

Norman

Answer 5

Hello Byron,

I am assuming from your question that everything is working fine with the code you have except for removing the formulas and pasting values in lieu. If that is correct then please post the code you have an I will see if I can edit the part to paste values instead of formulas.