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?

• Moved by George.Zhao CHNMicrosoft contingent staff Wednesday, July 01, 2015 6:08 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

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