Save specific sheet as new workbook in a specific path

[somers]

Hi,
The goal of my code is to save sheet 2 as pdf, sheet 3 as excel (new workbook) in the same folder as the active workbook and then email them as attachment.

But my code fails at the save of the new workbook.
This error is generated:

This is the code:

Sub Besteldoeken()

Dim savelocation, dealnr, klant, projectnaam, architect As String
Dim wbmast, wb As Workbook
Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant


    dealnr = Sheets(1).Range("C4")
    klant = Sheets(1).Range("C5")
    projectnaam = Sheets(1).Range("c6")
    architect = Sheets(1).Range("C7")
    Set wbmast = ActiveWorkbook
    savelocation = wbmast.Path & "\" & dealnr & "_" & klant & "_" & projectnaam & "_" & architect & "_" & "besteld"
    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olmailitem)


Sheets(2).ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=savelocation & ".pdf"

Workbooks.Add
Set wb = ActiveWorkbook
ThisWorkbook.Sheets(3).Copy before:=wb.Sheets(1)
wb.SaveAs Filename:=savelocation & ".xlsx"
wb.Close


For Each ToRecipient In Array("john@doe.com")
    OlMail.Recipients.Add ToRecipient
Next ToRecipient

For Each CcRecipient In Array("john1@doe.com")
    With OlMail.Recipients.Add(CcRecipient)
        .Type = olCC
    End With
Next CcRecipient

'Fill in Subject field
OlMail.Subject = "dealnr_projectnaam"

'Add the report as an attachment
OlMail.Attachments.Add (ThisWorkbook.Path & "\" & dealnr & "_" & klant & "_" & projectnaam & "_" & architect & "_" & "besteld.pdf")
OlMail.Attachments.Add (ThisWorkbook.Path & "\" & dealnr & "_" & klant & "_" & projectnaam & "_" & architect & "_" & "besteld.xlsx")

'Send Message
OlMail.Send


End Sub

I can't find what im doing wrong, any sugestions?

 

[bobsan42]

try this:

set wb = Workbooks.Add
ThisWorkbook.Sheets(3).Copy before:=wb.Sheets(1)
debug.print savelocation
debug.print "Filename length:",len(savelocation & ".xlsx")
debug. print thisworkbook.fullname
wb.SaveAs Filename:=savelocation & ".xlsx"

then check the immediate window for the output.
i guess that since pdf is saved with no issues the savelocation will be OK, but just in case lets check for not allowed characters and full filename length.
then make sure there is no vb code in the copied sheet, or specify the fileformat explicitly during saveas.
see if any of this helps.