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 1 hour 21 minutes ago

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 Su