dgravitt
asked on
Need to prompt to save file from VBA if file exists
I have a function to create and save a PDF file. The file name is the company name + the date. The creates a "request " file. If two requests are amde for the same day, the first file gets overwritten. How do I prompt the user if the file name already exists?
Function email_binder()
Dim db As Database
Dim rs As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim address As String
Dim EmailTo As String
Dim EmailCc As String
Dim EmailSubject As String
Dim EmailMsg As String
Dim EmailAttach As String
Dim DisplayEmail As Boolean
Dim spar1 As String
Dim spar2 As String
Dim spar3 As String
Dim spar4 As String
Dim Cust As String
Dim strAttachment As String
Dim strReport As String
On Error Resume Next
Cust = Form_Client.Text82
address = Form_Client.Text115
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * " & _
"FROM info " & _
"WHERE id = 1")
strAttachment = Application.CurrentProject .Path & "\Requests\Request " & Cust _
& " " & Format(Date, "m-d-yyyy") & ".pdf"
Debug.Print "Attachment file name and path: " & strAttachment
strReport = "rpt_request"
DoCmd.OutputTo objecttype:=acOutputReport , _
objectname:=strReport, _
outputformat:=acFormatPDF, _
outputfile:=strAttachment
End function
There is more code for emailing the file, but this is the creation of the file.
Function email_binder()
Dim db As Database
Dim rs As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim address As String
Dim EmailTo As String
Dim EmailCc As String
Dim EmailSubject As String
Dim EmailMsg As String
Dim EmailAttach As String
Dim DisplayEmail As Boolean
Dim spar1 As String
Dim spar2 As String
Dim spar3 As String
Dim spar4 As String
Dim Cust As String
Dim strAttachment As String
Dim strReport As String
On Error Resume Next
Cust = Form_Client.Text82
address = Form_Client.Text115
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * " & _
"FROM info " & _
"WHERE id = 1")
strAttachment = Application.CurrentProject
& " " & Format(Date, "m-d-yyyy") & ".pdf"
Debug.Print "Attachment file name and path: " & strAttachment
strReport = "rpt_request"
DoCmd.OutputTo objecttype:=acOutputReport
objectname:=strReport, _
outputformat:=acFormatPDF,
outputfile:=strAttachment
End function
There is more code for emailing the file, but this is the creation of the file.
ASKER
So, if file exists, how would you pop up the save as box?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, just what I needed!
Glad to help :)
ASKER
Just encountered an issue. This code works fine in Access 2010, but on an Access 2016 machine, it gives an error "Access has stopped working" and closes the application. Any ideas?
' file exists