Sending an Active Worksheet with VBA

ChrisBurns

New Member
Joined
Sep 4, 2014
Messages
13
Hi all,

I am using a modified version of the popular code from Ron de Bruin and all is working fine...... in Excel 2003
I manage around 80 workbooks for a team of 18 Project managers and have included a button on the required sheet in their master workbook to send it to my offices email address so that any of my team can access the email from the public folder in outlook
however..... every time a colleague opens the single sheet attachment they get the 'cannot update external links' dialog box.

Is there an additional piece of code that will remove all links from the newly created 'temp' workbook?

Thanks

Chris
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

ChrisBurns

New Member
Joined
Sep 4, 2014
Messages
13
That sounds like a good idea........ I'll give that a go
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
Something like this after the first end with:

Code:
With Activesheet.UsedRange
.Value=.Value
End With
 

ChrisBurns

New Member
Joined
Sep 4, 2014
Messages
13
Ahhhh.... i had overlooked the protection on the sheet

This now works as intended

Code:
Sub Mail_ActiveSheet()
'Working in 97-2010
    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 I As Long
    

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    ActiveSheet.Unprotect Password:="fluffy"
    Set Sourcewb = ActiveWorkbook

    'Copy the sheet 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
       
        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 = "Lessons Learned from " & Sourcewb.Name & " " _
                 & Format(Now, "dd-mmm-yy at h-mm")

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        For I = 1 To 3
            .SendMail "PMO@gstt.nhs.uk", _
                      "Lessons Learned"
                      
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    MsgBox "This sheet has been emailed to the PMO", vbOKOnly, "Thank You"


End Sub

Thanks for the quick response
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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
Top