yarondavid
Programmer
I use the ShellExecute function from shell32.dll lib and I don't know how can I insert a Hyperlink into Email
In my part of macro I want that Path string (in line 14) will display as Hyperlink in the Email
==================
1. Private Declare Function ShellExecute Lib "shell32.dll" _
2. Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
3. ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
4. ByVal nShowCmd As Long) As Long
5. Email = "yarond@marvell.com"
6. ' Message subject
7. Subj = "PSS/PSS over CPSS/CPSS Acceptance Tests Results"
8. ' Path to summary results
9. Path = summaryBasePath + CStr(.Cells(3, "B")) + "\" + CStr(.Cells(3, "B")) + "_" + CStr(.Cells(4, "B")) + "\Acceptance_Tests_Summary.xls"
10. ' Compose the message
11. Msg = "Hi All" & vbCrLf & vbCrLf
12. Msg = Msg & "The Acceptance summary results report for PSS/PSS over CPSS version " + CStr(.Cells(3, "B")) + "_" + CStr(.Cells(4, "B")) & vbCrLf
13. Msg = Msg & " and CPSS version " + CStr(.Cells(3, "C")) + "_" + CStr(.Cells(4, "C"))
14. Msg = Msg & " locat in: " + Path & vbCrLf & vbCrLf
15. Msg = Msg & "Regards," & vbCrLf
16. Msg = Msg & "Yaron"
17. ' Replace spaces with %20 (hex)
18.Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
19.Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
20. ' Replace Carriage-Return with %0D%0A (hex)
21. Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
22. ' Create the URL
23. URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
24. ' Execute the URL (start the email client)
25. ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
26. ' Wait two seconds before sending keystrokes
27. ActiveWorkbook.FollowHyperlink (Path)
28. Application.Wait (Now + TimeValue("0:00:02"))
29. Application.SendKeys "%s
In my part of macro I want that Path string (in line 14) will display as Hyperlink in the Email
==================
1. Private Declare Function ShellExecute Lib "shell32.dll" _
2. Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
3. ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
4. ByVal nShowCmd As Long) As Long
5. Email = "yarond@marvell.com"
6. ' Message subject
7. Subj = "PSS/PSS over CPSS/CPSS Acceptance Tests Results"
8. ' Path to summary results
9. Path = summaryBasePath + CStr(.Cells(3, "B")) + "\" + CStr(.Cells(3, "B")) + "_" + CStr(.Cells(4, "B")) + "\Acceptance_Tests_Summary.xls"
10. ' Compose the message
11. Msg = "Hi All" & vbCrLf & vbCrLf
12. Msg = Msg & "The Acceptance summary results report for PSS/PSS over CPSS version " + CStr(.Cells(3, "B")) + "_" + CStr(.Cells(4, "B")) & vbCrLf
13. Msg = Msg & " and CPSS version " + CStr(.Cells(3, "C")) + "_" + CStr(.Cells(4, "C"))
14. Msg = Msg & " locat in: " + Path & vbCrLf & vbCrLf
15. Msg = Msg & "Regards," & vbCrLf
16. Msg = Msg & "Yaron"
17. ' Replace spaces with %20 (hex)
18.Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
19.Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
20. ' Replace Carriage-Return with %0D%0A (hex)
21. Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
22. ' Create the URL
23. URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
24. ' Execute the URL (start the email client)
25. ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
26. ' Wait two seconds before sending keystrokes
27. ActiveWorkbook.FollowHyperlink (Path)
28. Application.Wait (Now + TimeValue("0:00:02"))
29. Application.SendKeys "%s