Saving two different files at once

[Erik968]

my goal with this code is to save my file to PDF and as XLSM file to my pc, then after that it gets put in an automated email where the PDF and XLSM file are attachments.

My Problem: since I am just a beginner when it comes to VBA, I dont know what I am doing wrong..

Could you guys tell me what I need to change in my code so my file saves to my pc in PDF and XLSM with the name from cell 39 (Range ''39'')

I have been stuck with this all day...

Sub saveandsend()


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\(Range "39".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    
Dim Path2 As String
Dim filename2 As String
Path2 = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename2 = Range("P39")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm"


' SendEmail Macro
'


Dim outlookapp As Object
Dim OutlookMail As Object
Set outlookapp = CreateObject("Outlook.Application")
Set OutlookMail = outlookapp.CreateItem(0)
On Error Resume Next
With OutlookMail
    .To = "erikstoeken@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("X22")
    .Body = "Bon zit in de bijlage."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Attachments.Add (Path & filename & ".PDF")
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing




End Sub

 

[yky]

Could you guys tell me what I need to change in my code so my file saves to my pc in PDF and XLSM with the name from cell 39 (Range ''39'')

I think you're missing a letter. You need a column letter, A39, B39 or something.

I have been stuck with this all day...

Sub saveandsend()


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        [COLOR=#0000cd]"C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\" & Cells(39,16) & ".pdf"[/COLOR], _   [COLOR=#0000cd]<-if the range is P39[/COLOR]
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    
Dim Path2 As String
Dim filename2 As String
Path2 = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename2 = Range("P39")
ActiveWorkbook.SaveAs filename:=[COLOR=#ff0000]Path[/COLOR] & [COLOR=#ff0000]filename[/COLOR] & ".xlsm"  [COLOR=#0000cd]<- should that be Path2 and filename2?[/COLOR]

...........

 

[bobsan42]

Try it like this:

Sub saveandsend()
   
Dim Path2 As String
Dim filename2 As String
Path2 = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename2 = Range("P39").Value


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=Path2 & filename2 & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
        
ActiveWorkbook.SaveAs Filename:=Path2 & filename2 & ".xlsm"




' SendEmail Macro


Dim outlookapp As Object
Dim OutlookMail As Object
Set outlookapp = CreateObject("Outlook.Application")
Set OutlookMail = outlookapp.CreateItem(0)
On Error Resume Next
With OutlookMail
    .To = "erikstoeken@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("X22")
    .Body = "Bon zit in de bijlage."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Attachments.Add (Path2 & filename2 & ".PDF")
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing


End Sub

 

[Erik968]

IT FINALLY SAVES AS PDF THANK YOU!

but one more thing, how do I get that pdf with the cellname P39 as an attachment in my email? because the line: .Attachments.Add (Path & filename & ".PDF") doesnt pick the right pdf..

Sub saveandsend()




ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\" & Range("P39") & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
    
Dim Path As String
Dim filename As String
Path = "C:\Users\Erik Stoeken\Documents\van Wijk\Excel test\"
filename = Range("P39")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsm"




' SendEmail Macro
'




Dim outlookapp As Object
Dim OutlookMail As Object
Set outlookapp = CreateObject("Outlook.Application")
Set OutlookMail = outlookapp.CreateItem(0)
On Error Resume Next
With OutlookMail
    .To = "erikstoeken@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = Range("X22")
    .Body = "Bon zit in de bijlage."
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Attachments.Add (Path & filename & ".PDF")
    .Display
End With
Set OutlookMail = Nothing
Set outlookapp = Nothing








End Sub

 

[bobsan42]

I completely corrected your code above - so it all must work if you use it.
The error is that you must use Path2 & Filename2

 

[yky]

but one more thing, how do I get that pdf with the cellname P39 as an attachment in my email? because the line: .Attachments.Add (Path & filename & ".PDF") doesnt pick the right pdf..

Looks to me it should pick up the following PDF file. If not, what file is picked up?

"C:\Users\Erik Stoeken\Documents\van Wijk\Excel test" & Range("P39") & ".pdf",