Solved

Excel macro with a hyperlink.

Posted on 2014-04-04
5
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

734 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