Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I insert a Hyperlink into Email via ShellExecute function in V

Status
Not open for further replies.

yarondavid

Programmer
May 8, 2006
20
IL
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top