Saving two different files at once

Erik968

New Member
Joined
Feb 8, 2019
Messages
12
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...

Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,872
Office Version
  1. 2010
Platform
  1. Windows
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...

Code:
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Try it like this:
Code:
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

New Member
Joined
Feb 8, 2019
Messages
12
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..

Code:
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

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Jun 7, 2011
Messages
1,872
Office Version
  1. 2010
Platform
  1. Windows
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",
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top