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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

763 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