Solved

Visual Basic - code question

Posted on 2015-02-11
4
92 Views
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 it....so 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)
 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
 '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
 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
 '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
 .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
 
Thanks in Advance:)
0
Comment
Question by:katarina11
  • 2
  • 2
4 Comments
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40603549
Change this

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

Open in new window


to this

DestFolder = "C:\temp"

Open in new window

0
 
LVL 1

Author Comment

by:katarina11
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 in....it does not automatically save it to c:\temp...
0
 
LVL 1

Author Comment

by:katarina11
ID: 40603760
It works now...THANK YOU:)
0
 
LVL 46

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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now