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:
I can't find what im doing wrong, any sugestions?
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:
VBA 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?