?
Solved

Excel macro with a hyperlink.

Posted on 2014-04-04
5
Medium Priority
?
346 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 52

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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