Excel macro with a hyperlink.

I have a macro saving a file that has a date and time stamp suffix.  Because that folder had spaces in the folder path it was screwing up the link that I was trying to place in an automated email.  How do I edit the code in the email to directly point to the filename that was saved with the date and time stamp suffix?


'Save file to FName2
    Application.DisplayAlerts = False 'use if overwritting file to nix popup
    Dim FName2 As String
'   L-learned do not use lettered drives
    FName2 = "\\na\Global-Groups\Holland\Operations\Logistics\DSC\Go to Work Signals\RePlan Report\AM PM RePlan Report "
    FName2 = FName2 & Format(Date, "yyyy.mm.dd") & " " & Format(Time, "hhmm") & ".xlsx"

    ActiveWorkbook.SaveAs Filename:= _
        FName2, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

Open in new window



            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail

                .To = strto
                .CC = ccto
                .BCC = bccto
'                .Subject = Format(Now, "dd-mmm-yy h:mm:ss")
                .Subject = Worksheets("EmailForReplanRpt").Range("J1") & " " & Format(Now, "mmm-dd-yyyy h:mm:ss") 'of listed s-sheet use J1 as Subject
                .Body = "DSC Replan Report is complete and ready for review. No issues. " & vbNewLine & _
                        "Report can be found: \\na\Global-Groups\Holland\Operations\Logistics\DSC\Go_to_Work_Signals\Replan_Report\ " & vbCrLf & vbNewLine & _
                        "For adds and deletes to this email, please advise.  Thanks." & vbCrLf & _
                        "Runtime: " & Format(DateDiff("s", dtStart1, dtEnd1) / 86400, "HH:MM:SS")

Open in new window

RWayneHAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try in the body of the email

"<file:" & FName2 & ">"

Open in new window

Regards
0
 
RWayneHAuthor Commented:
Could you assist in fitting this into the existing .Body?  When I do this it is returning red text and not like the replaced line. -R-
0
 
RWayneHAuthor Commented:
I would like to replace the:
 & _
                        "Report can be found: \\na\Global-Groups\Holland\Operations\Logistics\DSC\Go_to_Work_Signals\Replan_Report\ " & vbCrLf & vbNewLine & _

With
"Link to file: "<file:" & FName2 & ">" & vbCrLf & vbNewLine & _


How do I rewrite this to include your suggested edit to pull in the file  link?

This is not working for me.  Please advise and thanks.
0
 
RWayneHAuthor Commented:
Nevermind, I finally got it to work.  Thanks for the help.
0
 
RWayneHAuthor Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.