Solved

Excel macro with a hyperlink.

Posted on 2014-04-04
5
334 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 48

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now