Results 1 to 3 of 3

Specifying a folder destination

Threaded View

  1. #1
    Registered User
    Join Date
    05-05-2015
    Location
    lomita
    MS-Off Ver
    office enterprise 2007
    Posts
    34

    Specifying a folder destination

    Can anyone please tell me how i can make thew following code automatically save the PDF file to a specific file folder so the only thing that appears is the blank outlook email with the attached PDF file instead of having to manually save it to a specific location before emailing it....

    Option Explicit
     
    Sub create_and_email_pdf()
    
     
    Dim EmailSubject As String, EmailSignature As String
    Dim CurrentMonth As String, DestFolder As String, PDFFile As String
    Dim Email_To As String, Email_CC As String, Email_BCC As String
    Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
    Dim OverwritePDF As VbMsgBoxResult
    Dim OutlookApp As Object, OutlookMail As Object
    CurrentMonth = ""
     
    ' *****************************************************
    
     
        EmailSubject = "Invoice Attached for "
        OpenPDFAfterCreating = False
        AlwaysOverwritePDF = False
        DisplayEmail = True
        Email_To = ""
        Email_CC = ""
        Email_BCC = ""
                
    ' ******************************************************
         
        'Prompt for file destination
        With Application.FileDialog(msoFileDialogFolderPicker)
             
            If .Show = True Then
             
                DestFolder = .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
             
        End With
     
        
        CurrentMonth = Mid(ActiveSheet.Range("H6").Value, InStr(1, ActiveSheet.Range("H6").Value, " ") + 1)
         
        'Create new PDF file name including path and file extension
        PDFFile = DestFolder & Application.PathSeparator & ActiveSheet.Name _
                    & "_" & CurrentMonth & ".pdf"
     
        'If the PDF already exists
        If Len(Dir(PDFFile)) > 0 Then
         
            If AlwaysOverwritePDF = False Then
             
                OverwritePDF = MsgBox(PDFFile & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it?", vbYesNo + vbQuestion, "File Exists")
             
                On Error Resume Next
                '
                If OverwritePDF = vbYes Then
         
                    Kill PDFFile
             
                Else
         
                    MsgBox "OK then, 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
     
            Else
             
                On Error Resume Next
                Kill PDFFile
                 
            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
        
     
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=OpenPDFAfterCreating
     
        '
        Set OutlookApp = CreateObject("Outlook.Application")
        Set OutlookMail = OutlookApp.CreateItem(0)
             
     
        With OutlookMail
             
            .Display
            .To = Email_To
            .CC = Email_CC
            .BCC = Email_BCC
            .Subject = EmailSubject & CurrentMonth
            .Attachments.Add PDFFile
                     
            If DisplayEmail = False Then
                 
                .Send
                 
            End If
             
        End With
         
      
    End Sub
    Last edited by JBeaucaire; 07-21-2016 at 11:17 AM. Reason: Added missing CODE /CODE tags, please read and follow the forum rules. Link above in the menu bar.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy certain files from Source folder to Destination folder
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2015, 08:56 PM
  2. SAVECOPYAS Code for destination folder
    By Big_Ash in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-31-2014, 06:30 AM
  3. Tracking what files are in a apecific destination folder
    By winwall in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-26-2014, 05:28 PM
  4. When i Copy Folder to new destination i lose customised folder icon/picture
    By sternboy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-29-2012, 09:30 PM
  5. name existing on the destination folder
    By LeFootie in forum Excel General
    Replies: 3
    Last Post: 07-05-2012, 11:09 AM
  6. macro save as to a destination folder
    By poitachi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2010, 03:06 PM
  7. [SOLVED] Change Hyperlink Destination Folder
    By Ron in forum Excel General
    Replies: 2
    Last Post: 07-18-2005, 08:05 AM

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