?
Solved

Excel macro with a hyperlink.

Posted on 2014-04-04
5
Medium Priority
?
350 Views
Last Modified: 2014-04-04
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

0
Comment
Question by:RWayneH
  • 4
5 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39977895
Hi,

pls try in the body of the email

"<file:" & FName2 & ">"

Open in new window

Regards
0
 

Author Comment

by:RWayneH
ID: 39977904
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
 

Author Comment

by:RWayneH
ID: 39978806
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
 

Author Comment

by:RWayneH
ID: 39978950
Nevermind, I finally got it to work.  Thanks for the help.
0
 

Author Closing Comment

by:RWayneH
ID: 39978953
Thanks for the help.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question