Results 1 to 7 of 7

pre select folder location

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-22-2016
    Location
    london
    MS-Off Ver
    2013
    Posts
    121

    pre select folder location

    Hi

    I have some VBA code that allows me to save a document as a PDF and send it via email

    how can i pre define the folder location to save the PDF, currently a popup box appears asking to choose where to save it,


    Private Sub CommandButton2_Click()
    Dim xSht As Worksheet
    Dim xFileDlg As FileDialog
    Dim xFolder As String
    Dim xYesorNo As Integer
    Dim xOutlookObj As Object
    Dim xEmailObj As Object
    Dim xUsedRng As Range
     
    Set xSht = ActiveSheet
    Set xFileDlg = Application.FileDialog(msoFileDialogFolderPicker)
     
    If xFileDlg.Show = True Then
       xFolder = xFileDlg.SelectedItems(1)
    Else
       MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
       Exit Sub
    End If
    xFolder = xFolder & "\" & xSht.Name & "_" & Range("R3").Text & "_" & "Shift" & "_" & Format(Date, "ddmmyy") & ".pdf"
     
    'Check if file already exist
    If Len(Dir(xFolder)) > 0 Then
        xYesorNo = MsgBox(xFolder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", _
                          vbYesNo + vbQuestion, "File Exists")
        On Error Resume Next
        If xYesorNo = vbYes Then
            Kill xFolder
        Else
            MsgBox "if you don't overwrite the existing PDF, I can't continue." _
                        & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Exiting Macro"
            Exit Sub
        End If
        If Err.Number <> 0 Then
            MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _
                        & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
            Exit Sub
        End If
    End If
     
    Set xUsedRng = xSht.UsedRange
    If Application.WorksheetFunction.CountA(xUsedRng.Cells) <> 0 Then
        'Save as PDF file
        xSht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=xFolder, Quality:=xlQualityStandard
         
        'Create Outlook email
        Set xOutlookObj = CreateObject("Outlook.Application")
        Set xEmailObj = xOutlookObj.CreateItem(0)
        With xEmailObj
            .Display
            .To = "[email protected]"
            .CC = ""
            .Subject = xSht.Name + ".pdf"
            .Body = "Hi All," & vbLf & vbLf _
                   & "Please Find Attached the KPI for" & " " & "the" & " " & Range("R3").Text & " " & "Shift" & " " & "Of the" & " " & Range("P3").Value & vbLf & vbLf _
                   & "Available Vehicles For Service is" & " " & Range("E3").Value & vbLf & vbLf _
                   & "Regards" & vbLf & vbLf _
                   & Application.UserName
            .Attachments.Add xFolder
                   
            If DisplayEmail = False Then
                '.Send
            End If
        End With
    Else
      MsgBox "The active worksheet cannot be blank"
      Exit Sub
    End If
    End Sub
    Last edited by kobiashi; 02-21-2018 at 03:07 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Linking Two Workbooks in the Same Folder and Excluding the Folder Location
    By 96Mustang460cid in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-29-2017, 02:27 AM
  2. [SOLVED] Looking for Macro to pull folder name and folder size of a specific location.
    By TheDirtyDrunk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-07-2016, 09:19 AM
  3. Open Folder (Prompt User for final folder name in location path)
    By synergeticink in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2014, 03:33 PM
  4. Replies: 0
    Last Post: 10-22-2014, 12:07 PM
  5. create new folder at one location and copy photos in that folder from other location
    By meer_ali in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2014, 05:20 AM
  6. [SOLVED] [Help]macro to open file browser, to select a location, and save the location to a cell
    By zhuleijia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2013, 09:56 AM
  7. Replies: 2
    Last Post: 05-07-2006, 03:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1