VBA code for naming with current date, lock capability, and email.

[JaesunB]

Hello,

I am running Excel for Mac 2011 version 14.6.6

I am trying to create a vba based system to be able to copy a template, rename the copy with a specific naming convention with current date, and to apply several macros and functions to the newly copied worksheet all with the click of a button on a separate worksheet in the same workbook. My knowledge of vba is limited at this point. So thank you in advance for any help you can provide.

1. I have some vba code that will copy a template sheet and name it with the current date:

               Sub Test()
                  Dim wb As Workbook: Set wb = ThisWorkBook
                  Dim ws As Worksheet: Set ws = wb.Sheets("Template")
                  Dim newws As Worksheet, sh As Worksheet, newname
                  Dim query As Long, xst As Boolean, info As String

                retry:
                  xst = False
                  newname = Format(Date, "mmm-dd-yyyy")
                  If newname = "False" Then Exit Sub
                  For Each sh In wb.sheets
                     If sh.Name = newname Then
                        xst = True: Exit For
                     End If
                  Next
                  If Len(newname) = 0 Or xst = True Then
                     info = "Sheet name is invalid. Please retry."
                     Go To retry
                  End If
                  ws.Copy after:=ws: Set newws = ActiveSheet: newws.Name = newname
               End Sub

So this vba code allows me to make a copy from my template sheet and will rename it with the current date. Not entirely what I am going for though. I would like to have the naming convention as follows: "EC Mon-Thru (then the current date)". I am not sure how to apply both a static naming convention with a current date.

2. I am trying to code these new copies so that after a specified time and date they will lock and can no longer be edited. The problem I am running into is that this lock code needs to be changing with every day. All of the codes I have found only let me place a static date and time. Example of what I am looking for: worksheet copied from template was created on Aug 12, 2016 at 0200. I am trying to get the worksheet to lock on Aug 13, 2016 at 0300. So it would give me capability to enter data for 25 or so hours then lock. But then the next time I create the 13th's worksheet it would kick the lock timer to the 14th at 0300 and so on.

3. The last thing I am trying to do is to be able to place a button on the newly create worksheet that when clicked will automatically email that worksheet to a set of email addresses. I am using office 365 for the email platform. I was able to find some code for this online, but I am not quite sure what I need to change to actually make it work. Here is the code:

           Sub EmailWithOutlook()
                Dim oApp As Object
                Dim oMail As Object
                Dim WB As Workbook
                Dim FileName As String
                Dim wSht As Worksheet
                Dim shtName As String

                Application.ScreenUpdating = False

                    ' Make a copy of the active worksheet
                    ' and save it to a temporary file
                ActiveSheet.Copy
                Set WB = ActiveWorkbook

                FileName = WB.Worksheets(1).Name
                On Error Resume Next
                Kill "C:\" & FileName
                On Error GoTo 0
                WB.SaveAs FileName:="C:\" & FileName

                'Create and show the Outlook mail item
                Set oApp = CreateObject("Outlook.Application")
                Set oMail = oApp.CreateItem(0)
                With oMail
                     'Uncomment the line below to hard code a recipient
                     '.To = "testuser@test.com"
                     'Uncomment the line below to hard code a subject
                     '.Subject = "Subject Line"
                     'Uncomment the lines below to hard code a body
                     '.body = "Dear John" & vbCrLf & vbCrLf & _
                     '"Here is the file you asked for"
                     .Attachments.Add WB.FullName
                     .Display
                End With

               'Delete the temporary file
                WB.ChangeFileAccess Mode:=xlReadOnly
                Kill WB.FullName
                WB.Close SaveChanges:=False

                'Restore screen updating and release Outlook
                 Application.ScreenUpdating = True
                 Set oMail = Nothing
                 Set oApp = Nothing
           End Sub

 

[dchaney]

So this vba code allows me to make a copy from my template sheet and will rename it with the current date. Not entirely what I am going for though. I would like to have the naming convention as follows: "EC Mon-Thru (then the current date)". I am not sure how to apply both a static naming convention with a current date.

change the following:
From:

newname = Format(Date, "mmm-dd-yyyy")

To:

newname = "EC Mon-Thru " & Format(Date, "mmm-dd-yyyy")

2. I am trying to code these new copies so that after a specified time and date they will lock and can no longer be edited. The problem I am running into is that this lock code needs to be changing with every day. All of the codes I have found only let me place a static date and time. Example of what I am looking for: worksheet copied from template was created on Aug 12, 2016 at 0200. I am trying to get the worksheet to lock on Aug 13, 2016 at 0300. So it would give me capability to enter data for 25 or so hours then lock. But then the next time I create the 13th's worksheet it would kick the lock timer to the 14th at 0300 and so on.

do you have code started for this?

3. The last thing I am trying to do is to be able to place a button on the newly create worksheet that when clicked will automatically email that worksheet to a set of email addresses. I am using office 365 for the email platform. I was able to find some code for this online, but I am not quite sure what I need to change to actually make it work. Here is the code:

Try to add the .Display right under With oMail and where .Display is add .Send (Not sure if it will work, I am not sure how 365 handles VBA)