Where in this code it is specifying a filepath?

sanantonio

Board Regular
Joined
Oct 26, 2021
Messages
124
Office Version
  1. 365
Platform
  1. Windows
Whereabouts in the below code is this macro deciding where it's saving this PDF? I ask because I've used this macro is numerous reports over the years and it's always functioned without question. But in it's latest iteration it seems to save the pdf in some weird and wonderful places (seemingly at random) and then debugs at the " .Attachments.Add PdfFile" line.

S2 contains only the filename, in this case: "Report_Wk1"

VBA Code:
Sub email()
'
' email Macro
'
If MsgBox("This will automatically generate and send the PDF tab via email, are you sure you wish to proceed?", vbYesNo) = vbNo Then Exit Sub


  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object

 
  ' Not sure for what the Title is
  Title = Range("S2")
 
  ' Define PDF filename
  PdfFile = Range("S2")
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ".pdf"

  ' Export activesheet as PDF
  With Worksheets("PDF")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = "xxx@xxx.com" ' <-- Put email of the recipient here
    .CC = "xxx@xxx.com" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Please find attached latest  Report." & vbLf & vbLf _
          & "Regards eLWIS Team" & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
 On Error Resume Next
 
  ' Delete PDF file
  Kill PdfFile
  
  On Error Resume Next
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
'
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
If you don't specify the full path, the file is saved in the current folder. I suspect the "current" folder is the same as whatever the active workbook is when you run the code. A better method would be to put the folder path in the cell or use a file dialog to choose the correct folder and pass the choice to the procedure.
 
Upvote 0
In addition, since your file gets deleted in the end, save it in your temporary folder. So you would use the Environ function to add the path like this...

VBA Code:
PdfFile = Environ("temp") & "\" & PdfFile & "_" & ".pdf"

Note that Environ("temp") resolves to something like this...

VBA Code:
C:\Users\username\AppData\Local\Temp

For additional information regarding the Environ function, have a look at the following link...


Hope this helps!
 
Upvote 0
Solution
Great thanks, I'll give that a go!

Which line exactly should I replace out with the environ code?

The second line here?

VBA Code:
  ' Define PDF filename
  PdfFile = Range("S2")
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ".pdf"
 
Upvote 0
It's the last line. So replace...

VBA Code:
PdfFile = PdfFile & "_" & ".pdf"


with

VBA Code:
PdfFile = Environ("temp") & "\" & PdfFile & "_" & ".pdf"

Cheers!
 
Upvote 0

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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
Back
Top