none
Rename File and attach Help RRS feed

  • Question

  • Hello -

    I am needing some assistance with sending a file as an attachment using VB. I am definitely no pro here; but know enough to be dangerous :)  I have everything I need the code to do, except renaming the file. I would like to rename the file prior to attaching the file to the email. I would like to rename the file using info from within the excel file itself.

    Example:

    I would like to file to contain the following text and pull a couple of dates in from the file ( dates in order to prevent duplicate file names). Something like this....

    Proposed Filename:  PropertyName_CAR_6/1/2015_6/30/2015

    Cell B3 = Property Name

    CAR (text only - no cell reference)

    Cell G3 = 6/1/2015

    Cell I3 = 6/30/2015

    Below is the code I have thus far.  Any suggestion on how to accomplish this will be greatly appreciated. Thanks in advance for you assistance :-)

    Private Sub Submit_Form_Click()

            Dim r As Long
            Dim Today
            Dim OutApp As Object
            Dim OutMail As Object
            r = MsgBox("Are you sure you want to submit! If you click Ok, you will not be able to make any additional changes! Click Ok to continue or Cancel to  cancel the submission.", _
                vbQuestion + vbOKCancel, "Error!")
            If r = vbCancel Then
                Exit Sub
            Else
            If r = vbOK Then
                ActiveSheet.Unprotect Password:=""
                ActiveSheet.Range("D2").Value = "Submitted " & Now
                Range("D2,B3,G3,I3,B4,G4,A9:J12,A14:J17,A19:J22,A24:J27,A29:J32,A34:J37,A39:J42").Select
                Selection.Locked = True
                ActiveSheet.Protect Password:=""
            End If
            End If
                ActiveSheet.Submit_Form.Visible = False
                ActiveSheet.Reset.Visible = False
                ActiveSheet.Clear_Instructions.Visible = False
                ActiveSheet.Submit_Warning.Visible = False
                ActiveSheet.GreenButtonExample.Visible = False
                ActiveSheet.GreenButtonInfo.Visible = False
                ActiveSheet.Add2Mon.Visible = False
                ActiveSheet.Add2Tue.Visible = False
                ActiveSheet.Add2Wed.Visible = False
                ActiveSheet.Add2Thu.Visible = False
                ActiveSheet.Add2Fri.Visible = False
                ActiveSheet.Add2Sat.Visible = False
                ActiveSheet.Add2Sun.Visible = False
                ActiveSheet.AddSvc.Visible = False
                ActiveSheet.AddReg.Visible = False
                ActiveSheet.AddProp.Visible = False
                ActiveWorkbook.Save
               
               
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
                On Error Resume Next
            With OutMail
            .to = ThisWorkbook.Sheets("CAR").Range("G4").Value
            .CC = ThisWorkbook.Sheets("CAR").Range("B4").Value
            .BCC = ""
            .Subject = Range("B3") & " - CAR for the week of " & Range("G3") & " through " & Range("I3")
            .Body = "Attached is the CAR for " & Range("B3") & " for the week of " & Range("G3") & " through " & Range("I3") & ". Please let me know should you   have any questions." & vbNewLine & vbNewLine & "Thank you," & vbNewLine & Range("B4")
            .Attachments.Add ActiveWorkbook.FullName
            .Display 'or .Send
            End With
            On Error GoTo 0

            Set OutMail = Nothing
            Set OutApp = Nothing
             With Application
             .EnableEvents = True
             .ScreenUpdating = True
            End With
               
                r = MsgBox("Your form has been submitted.", vbInformation)
                'ActiveSheet.Submit_Form.Visible = True
                ActiveSheet.Reset.Visible = True
                ActiveSheet.Clear_Instructions.Visible = True            
                          
    End Sub

    PS- I also wouldn't mind sending the file as a PDF if that is possible?

    Tuesday, June 30, 2015 9:05 PM

Answers

  • Hi Randy Steet,

    >>I have everything I need the code to do, except renaming the file. I would like to rename the file prior to attaching the file to the email.... I would like to rename the file using info from within the excel file itself.<<

    We can get the data from the sepcific cell using Range as the code you provied by above then we can save the workbook using Workbook.SaveAs with the name you wanted.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 2, 2015 5:14 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel for Developer Forum.

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs. Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, July 1, 2015 5:23 AM
  • Thank you Emi for the suggestion. My apologies for posting here. I was lead here by another user from another Microsoft forum. I will check out the forum you have suggested.

    Thanks again,

    Randy Street

    Wednesday, July 1, 2015 3:37 PM
  • Hi Randy Steet,

    >>I have everything I need the code to do, except renaming the file. I would like to rename the file prior to attaching the file to the email.... I would like to rename the file using info from within the excel file itself.<<

    We can get the data from the sepcific cell using Range as the code you provied by above then we can save the workbook using Workbook.SaveAs with the name you wanted.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 2, 2015 5:14 AM
    Moderator
  • Hello. Just thought I would inform you that I attempted to complete your survey but the survey failed 50% through and would not allow me to continue. Failed at the part where one is supposed to enter their email address. Entered email address(s) clicked "Next" and nada...nothing happens.
    Tuesday, July 7, 2015 5:05 PM
  • Hi rstreets,

    Thanks for the feedback about this issue. I am trying report this issue.

    In addtion, have you fixed the orignal issue? If you have any problem, please feel free to let me know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, July 8, 2015 5:35 AM
    Moderator