Visual Basic - code question

Posted on 2015-02-11
Last Modified: 2015-02-11
Hi there,
I have created a macro in Excel 2007 using Visual Basic..
When I run it, it will take an excel sheet, turn it into a .pdf, ask you where you want to save it and then save it there as a pdf with the sheet name, and then it will open up an email (Outlook) automatically with the .pdf attached.
My question is, I want to automatically save the .pdf to c:/temp without being prompted where to save all that I will see is the email with the attached .pdf pop-up and not a prompt of where to save the file.
I have tried changing the code, but I cannot for the life of me seem to get this to work...any VBE experts out there that might be able to give me a hand? This is my code:

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 = ""
 ' *****************************************************
 ' ***** You Can Change These Variables *********
 EmailSubject = "Please see attached pdf " 'Change this to change the subject of the email. The current month is added to end of subj line
 OpenPDFAfterCreating = True 'Change this if you want to open the PDF after creating it : TRUE or FALSE
 AlwaysOverwritePDF = False 'Change this if you always want to overwrite a PDF that already exists :TRUE or FALSE
 DisplayEmail = True 'Change this if you don't want to display the email before sending. Note, you must have a TO email address specified for this to work
 Email_To = "" 'Change this if you want to specify To email e.g. ActiveSheet.Range("H1") to get email from cell H1
 Email_CC = ""
 Email_BCC = ""
 ' ******************************************************
 'Prompt for file destination
 With Application.FileDialog(msoFileDialogFolderPicker)
 If .Show = True Then
 DestFolder = .SelectedItems(1)
 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
 'Current month/year stored in H6 (this is a merged cell)
 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 you want to overwrite the file then delete the current one
 If OverwritePDF = vbYes Then
 Kill PDFFile
 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
 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
 'Create the PDF
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
 :=False, OpenAfterPublish:=OpenPDFAfterCreating
 'Create an Outlook object and new mail message
 Set OutlookApp = CreateObject("Outlook.Application")
 Set OutlookMail = OutlookApp.CreateItem(0)
 'Display email and specify To, Subject, etc
 With OutlookMail
 .To = Email_To
 .CC = Email_CC
 .BCC = Email_BCC
 .Subject = EmailSubject & CurrentMonth
 .Attachments.Add PDFFile
 If DisplayEmail = False Then
 End If
 End With
 End Sub
Thanks in Advance:)
Question by:katarina11
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 47

Accepted Solution

Martin Liss earned 500 total points
ID: 40603549
Change this

With Application.FileDialog(msoFileDialogFolderPicker)
 If .Show = True Then
 DestFolder = .SelectedItems(1)
 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

Open in new window

to this

DestFolder = "C:\temp"

Open in new window


Author Comment

ID: 40603644
Thanks for replying!  I tried changing that, but it still keeps defaulting to opening my Documents to choose a folder to save the .pdf does not automatically save it to c:\temp...

Author Comment

ID: 40603760
It works now...THANK YOU:)
LVL 47

Expert Comment

by:Martin Liss
ID: 40604283
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access Search and Replace Using VBA 6 104
Put text in a picture ASP.NET C# 2 73
Zip Folders Using Chilkat Routines 1 85
Sub or Function is not defined 6 59
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question