Sending an Active Worksheet with VBA

[ChrisBurns]

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

 

[steve the fish]

Copy PasteSpecial Values?

 

[ChrisBurns]

That sounds like a good idea........ I'll give that a go

 

[steve the fish]

Something like this after the first end with:

With Activesheet.UsedRange
.Value=.Value
End With

 

[ChrisBurns]

Ahhhh.... i had overlooked the protection on the sheet

This now works as intended

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