Hi all,

I have a couple of problems that I cant seem to fix!

Firstly - The following code copies and sends an excel spreadsheet file (containing 3 sheets). The code seems to works on some computers and not on others and I’m getting an error message box (Microsoft Visual Basic with a red circle and a cross in it, and the number 400). The worksheets are protected could this be a contributing factor? (they need to remain protected) I should prolly also mention that the file is sent and recieved between two types of email applications (a government based email platform and outlook).

Secondly - Recent testing of the code has produced a small problem for the email receiver - when the file is opened they’re prompted with a message box to ‘update or not update links’ (an oversight on my behalf - I should have mentioned this to Leith when he wrote the code for me)…. But can a line of code be added asking it ‘not to update’ so the pop up will not occur, or would a paste special (values) be the way to go? I’m new to VBA so I’m not sure how to write this in.

Can anyone help me with these issues? – code below

Sub CustomEmail3Sheets()
 
  Dim Wks As Worksheet
  Dim Wkb As Workbook
  Dim WkbName As String
  
   'Create a new workbook with 1 sheet
    Set Wkb = Workbooks.Add(xlWBATWorksheet)
   'Rename the sheet as space to prevent sheet naming conflicts
    Wkb.Worksheets(1).Name = " "
    
     'Add the custom sheet named to the new workbook
      For Each Wks In ThisWorkbook.Worksheets
        Select Case Wks.Name
          Case Is = "Sheet name 1", "Sheet name 2", "Sheet name 3"
            Wks.Copy After:=Wkb.Worksheets(Wkb.Worksheets.Count)
        End Select
      Next Wks
      
       'Delete the original sheet and save the workbook
        Application.DisplayAlerts = False
          Wkb.Worksheets(1).Delete
          Wkb.SaveAs "Copy of " & ThisWorkbook.Name
        Application.DisplayAlerts = True
      
       'Email the workbook
        Application.Dialogs(xlDialogSendMail).Show Arg2:=Wkb.Name
      
     'Save the path to the new workbook and close it
      WkbName = Wkb.FullName
      Wkb.Close
      
   'Delete the new workbook
    Kill WkbName
      
End Sub