We help IT Professionals succeed at work.
Get Started

Need to prompt to save file from VBA if file exists

dgravitt
dgravitt asked
on
444 Views
Last Modified: 2017-03-22
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.
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE