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
Bookmarks