VBA Help - SaveCopyAs trying to open a workbook instead of save?

[Johnny Thunder]

Hi group,

I am at the last step of my code and I am running into a little issue.

I have an emailscript that drafts an email and attaches a link to the activeworkbook to the email and adds a calculated time stamp. Prior to adding the timestamp the code runs fine and without issue. When I add the calculated time stamp the code gets messed up and I get a Runtime 1004 Error "File can not be found" even though the code is supposed to be saving a copy and not trying to open something. Weird.... Any help is appreciated.

The function rtime seems to be messing up the Savecopyas line of code on the EmailLink code, not sure why?

My code:

Option Explicit


Public Function rTime() As String


Dim szTime As String
      
   szTime = Time
 
    If szTime > #7:15:00 AM# And szTime < #10:00:00 AM# Then


rTime = "8:15am"


     ElseIf szTime > #10:15:00 AM# And szTime < #1:00:00 PM# Then


rTime = "10:15am"


    ElseIf szTime > #1:15:00 PM# And szTime < #4:00:00 PM# Then


rTime = "1:15pm"


     ElseIf szTime > #4:15:00 PM# And szTime < #8:00:00 PM# Then


rTime = "4:15pm"
 
 Else


rTime = Format(CStr(Now), "hh.mmam/pm")


End If


End Function
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Public Sub EmailLink()


    Dim OutApp          As Object
    Dim OutMail         As Object
    Dim strbody         As String, dt As String, msg As String, dt2 As String, tActivity As String, FileTime As String, RelativePath As String
        
    tActivity = Range("activ").Value
    
    dt = Format(CStr(Now), " - mm/dd/yyyy hh:mmam/pm")
    dt2 = Format(CStr(Now), " yyyy_mm_dd hh.mmam/pm")
    
    If ActiveWorkbook.Path <> "" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)


msg = msg & "<font face=""Calibri"" size=""3"" color=""red""><b> " & tActivity & " </b></font>"


        strbody = "<font size=""2"" face=""Calibri"">" & _
                  "March Actual H8 BCS BTL Activity Report is complete " & msg & ".<br><B><Br>" & vbNewLine _
                  & ActiveWorkbook.Name & "</B> <br>" & _
                  "Click on this link to open the file : " & _
                  "<A HREF=""file://" & ActiveWorkbook.FullName & _
                  """>Link to the file</A>" & _
                  "<br><br>Regards," & _
                  "<br><br>Financial Systems Group</font>"


        On Error Resume Next
        With OutMail
            .To = Range("tosend").Value
            .CC = Range("CCsend").Value
            .BCC = ""
            .Subject = "BCS BTL Tie Out Report " & rTime & " Refresh"
            .HTMLBody = strbody
            .Display  'or use .send to send; or .Display to create and not send
        End With
        On Error GoTo 0


        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        MsgBox "The ActiveWorkbook does not have a path, Save the file first."
    End If
    
     If tActivity = "no activity" Then
           
            MsgBox "A copy of the report has been made and saved to the current directory." & vbNewLine & vbNewLine _
            & "An email with the status " & """" & tActivity & """" & " of the report has also been generated."
            
       Else
            
           MsgBox "A copy of the report has been made and saved to the current directory." & vbNewLine & vbNewLine _
            & "An email with the status " & """" & tActivity & """" & " of the report has also been generated." & vbNewLine & vbNewLine _
            & "Please Print out a copy of the report and deliver to Jenn & Lorren"
            
           End If
          
          
RelativePath = ThisWorkbook.Path & "\" & "Previous\" & "BCS BTL Tie Out Report_" & rTime & ".xlsm"   <-------------------Line that Errors
        
       ActiveWorkbook.SaveAs Filename:=RelativePath
                                      
    End Sub

 

[Domenic]

rTime returns a string that contains a colon, but the colon is unacceptable in the file name when saving a file.

 

[Johnny Thunder]

@Domenic I was looking at that about an hour ago and change the returned value to have an underscore and it still errors out. Thanks for your help.

 

[Domenic]

Is it the same error? If not, which one? Also, did you replace the colon with an underscore like this...

RelativePath = ThisWorkbook.Path & "\" & "Previous\" & "BCS BTL Tie Out Report_" & Replace(rTime, ":", "_") & ".xlsm"

Also, probably a good idea to include the file format...

ThisWorkbook.SaveAs RelativePath, 52 'xlOpenXMLWorkbookMacroEnabled