Insert a hyperlink in an email sent by VBA
creator3_1969
Posted messages
13
Registration date
Status
Member
Last intervention
-
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
yg_be Posted messages 23437 Registration date Status Contributor Last intervention -
Bonjour,
I've done several searches and a lot of trials, but unfortunately, I can't manage to insert a hyperlink in the body of my email that would be sent automatically.
I either get the file path, or the name but not both at once, or I get the path and the name but not as a link.
I'm sure it's a "mess up" on my part, but I'm stuck...
Can you help me?
Thank you very much
I've done several searches and a lot of trials, but unfortunately, I can't manage to insert a hyperlink in the body of my email that would be sent automatically.
I either get the file path, or the name but not both at once, or I get the path and the name but not as a link.
I'm sure it's a "mess up" on my part, but I'm stuck...
Can you help me?
Thank you very much
Sub SendMailhyperlink() Dim lien As String Dim csPath1 As String Dim Myname As String Dim MonOutlook As Object Dim MonMessage As Object csPath1 = "P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\" Myname = ("ANNEXE_BPOST_MINI_PACK_SCAN") & "_" & Sheets("MAIN MINI PACK SCAN").Range("C4") & "_" & Sheets("Annexe Transport").Range("L5") & "_" & Sheets("Annexe Transport").Range("M5") ' & "_" & Format(Date, "mmmm_yyyy") lien = csPath1 & Myname Set MonOutlook = CreateObject("Outlook.Application") Set MonMessage = MonOutlook.CreateItem(0) MonMessage.BodyFormat = 2 corps = "<HTML><BODY>" MonMessage.To = "xxx@xxx.com" MonMessage.cc = "" MonMessage.Subject = "Annexe facture" corps = corps & "Bonjour," corps = corps & "<p>" corps = corps & "<p> Ci-dessous le lien vers l'annexe qui a été crée : "".</p>" corps = corps & "<a href="" & lien & "">lien</a></p>" corps = corps & "</BODY></HTML>" MonMessage.HTMLBody = corps MonMessage.display "true" End Sub<code basic></code>
5 answers
yg_be
Posted messages
23437
Registration date
Status
Contributor
Last intervention
Ambassadeur
1 588
Hello, it seems that you are not using the variable link.
Have you examined the content of the variable body?
Have you examined the content of the variable body?
Bonjour,
Thank you for the response.
I modified the code so that the body of the message correctly displays the created file, but I cannot manage to make the word "link" appear as a hyperlink reflecting the file indicated by csPath1 & Myname.
Thank you
Laurent
Sub SendMailhyperlink()
Dim lien As String
Dim csPath1 As String
Dim Myname As String
Dim MonOutlook As Object
Dim MonMessage As Object
csPath1 = "P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\"
Myname = ("ANNEXE_BPOST_MINI_PACK_SCAN") & "_" & Sheets("MAIN MINI PACK SCAN").Range("C4") & "_" & Sheets("Annexe Transport").Range("L5") & "_" & Sheets("Annexe Transport").Range("M5") ' & "_" & Format(Date, "mmmm_yyyy")
lien = csPath1 & Myname
Set MonOutlook = CreateObject("Outlook.Application")
Set MonMessage = MonOutlook.CreateItem(0)
MonMessage.BodyFormat = 2
corps = "<HTML><BODY>"
MonMessage.To = "xxx@xxx.com"
MonMessage.cc = ""
MonMessage.Subject = "Invoice Annex"
corps = corps & "Hello,"
corps = corps & "<p>"
corps = corps & "<p> Below is the link to the annex that has been created: "".</p>"
corps = corps & csPath1
corps = corps & Myname
lien = csPath1 & Myname
corps = corps & "<a href=""" & lien & "">link</a></p>"
corps = corps & "</BODY></HTML>"
MonMessage.HTMLBody = corps
MonMessage.display "true"
End Sub
Thank you for the response.
I modified the code so that the body of the message correctly displays the created file, but I cannot manage to make the word "link" appear as a hyperlink reflecting the file indicated by csPath1 & Myname.
Thank you
Laurent
Sub SendMailhyperlink()
Dim lien As String
Dim csPath1 As String
Dim Myname As String
Dim MonOutlook As Object
Dim MonMessage As Object
csPath1 = "P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\"
Myname = ("ANNEXE_BPOST_MINI_PACK_SCAN") & "_" & Sheets("MAIN MINI PACK SCAN").Range("C4") & "_" & Sheets("Annexe Transport").Range("L5") & "_" & Sheets("Annexe Transport").Range("M5") ' & "_" & Format(Date, "mmmm_yyyy")
lien = csPath1 & Myname
Set MonOutlook = CreateObject("Outlook.Application")
Set MonMessage = MonOutlook.CreateItem(0)
MonMessage.BodyFormat = 2
corps = "<HTML><BODY>"
MonMessage.To = "xxx@xxx.com"
MonMessage.cc = ""
MonMessage.Subject = "Invoice Annex"
corps = corps & "Hello,"
corps = corps & "<p>"
corps = corps & "<p> Below is the link to the annex that has been created: "".</p>"
corps = corps & csPath1
corps = corps & Myname
lien = csPath1 & Myname
corps = corps & "<a href=""" & lien & "">link</a></p>"
corps = corps & "</BODY></HTML>"
MonMessage.HTMLBody = corps
MonMessage.display "true"
End Sub
Can you use code tags when sharing code: https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code
What text do you receive in the email?
I suggest that you start with simpler VBA programs.
What text do you receive in the email?
I suggest that you start with simpler VBA programs.
I'm sorry, I was convinced I had used the code tab....
below is what is displayed:
Hello,
Below is the link to the annex that was created: ".
P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\ANNEXE_BPOST_MINI_PACK_SCAN_195103931_12_2019link
below is what is displayed:
Hello,
Below is the link to the annex that was created: ".
P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\ANNEXE_BPOST_MINI_PACK_SCAN_195103931_12_2019link
Sub SendMailhyperlink() Dim lien As String Dim csPath1 As String Dim Myname As String Dim MonOutlook As Object Dim MonMessage As Object csPath1 = "P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\" Myname = ("ANNEXE_BPOST_MINI_PACK_SCAN") & "_" & Sheets("MAIN MINI PACK SCAN").Range("C4") & "_" & Sheets("Annexe Transport").Range("L5") & "_" & Sheets("Annexe Transport").Range("M5") ' & "_" & Format(Date, "mmmm_yyyy") lien = csPath1 & Myname Set MonOutlook = CreateObject("Outlook.Application") Set MonMessage = MonOutlook.CreateItem(0) MonMessage.BodyFormat = 2 corps = "<HTML><BODY>" MonMessage.To = "xxx@xxx.com" MonMessage.cc = "" MonMessage.Subject = "Annexe facture" corps = corps & "Hello," corps = corps & "<p>" corps = corps & "<p> Below is the link to the annex that was created: "".</p>" corps = corps & csPath1 corps = corps & Myname lien = csPath1 & Myname corps = corps & "<a href=""csPath1 & Myname"">link</a></p>" corps = corps & "</BODY></HTML>" MonMessage.HTMLBody = corps MonMessage.display "true" End Sub
And when you click on the link, what do you see?
To help you understand what you are doing, I suggest replacing
with
What do you see when you execute this?
To help you understand what you are doing, I suggest replacing
corps = corps & "<a href=""csPath1 & Myname"">lien</a></p>"
with
dim url as string url= "<a href=""csPath1 & Myname"">lien</a>" corps = corps & url & "</p>" msgbox url
What do you see when you execute this?
Good evening,
Here is what appears when I replaced the part of the code.
Here is what appears when I replaced the part of the code.
Sub SendMailhyperlink() Dim lien As String Dim csPath1 As String Dim Myname As String Dim MonOutlook As Object Dim MonMessage As Object Dim url As String csPath1 = "P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\" Myname = ("ANNEXE_BPOST_MINI_PACK_SCAN") & "_" & Sheets("MAIN MINI PACK SCAN").Range("C4") & "_" & Sheets("Annexe Transport").Range("L5") & "_" & Sheets("Annexe Transport").Range("M5") ' & "_" & Format(Date, "mmmm_yyyy") 'lien = csPath1 & Myname Set MonOutlook = CreateObject("Outlook.Application") Set MonMessage = MonOutlook.CreateItem(0) MonMessage.BodyFormat = 2 corps = "<HTML><BODY>" MonMessage.To = "xxx@xxx.com" MonMessage.cc = "" MonMessage.Subject = "Annexe facture" corps = corps & "Bonjour," corps = corps & "<p>" corps = corps & "<p> Below is the link to the annex that was created: "".</p>" corps = corps & csPath1 corps = corps & Myname 'lien = csPath1 & Myname 'corps = corps & "<a href= lien & csPath1>""click here""</a></p>" url = "<a href=""csPath1 & Myname"">link</a>" corps = corps & url & "</p>" corps = corps & "</BODY></HTML>" MsgBox url MonMessage.HTMLBody = corps MonMessage.Display "true" End Sub
Good evening,
I finally figured out how to make everything work :-)
Here's what it looks like before I finish modifying the code so that the email sends automatically.
Below is the code; it might be useful.
Thanks for your help!
Laurent
I finally figured out how to make everything work :-)
Here's what it looks like before I finish modifying the code so that the email sends automatically.
Below is the code; it might be useful.
Thanks for your help!
Laurent
Sub SendMailhyperlink() Dim lien As String Dim csPath1 As String Dim Myname As String Dim MonOutlook As Object Dim MonMessage As Object Dim url As String csPath1 = "P:\Finance Department\Finance_Transport\Test_PAS_TOUCHE_LAURENT\" Myname = ("ANNEXE_BPOST_MINI_PACK_SCAN") & "_" & Sheets("MAIN MINI PACK SCAN").Range("C4") & "_" & Sheets("Annexe Transport").Range("L5") & "_" & Sheets("Annexe Transport").Range("M5") & ".xlsm" ' & "_" & Format(Date, "mmmm_yyyy") Set MonOutlook = CreateObject("Outlook.Application") Set MonMessage = MonOutlook.CreateItem(0) MonMessage.BodyFormat = 2 corps = "<HTML><BODY>" MonMessage.To = "xxx@xxx.com" MonMessage.Subject = "Annexe facture " & Myname corps = corps & "Bonjour," corps = corps & "<p>" corps = corps & "L'annexe ci-apr?s a ?t? cr?? : " & Myname corps = corps & "<p>" corps = corps & "Ci-dessous vous trouverez le lien pour y avoir directement acc?s." corps = corps & "<p>" lien = csPath1 & Myname corps = corps & "<p>" corps = corps & "<b><A HREF=""" & lien & """> lien </A></b>" corps = corps & "<p>" corps = corps & "Le fichier s'ouvrira sur votre page Annexe Finance." corps = corps & "<p>" corps = corps & "Bien ? vous," corps = corps & "<p>" corps = corps & "Laurent," corps = corps & "</BODY></HTML>" MonMessage.HTMLBody = corps MonMessage.Display "true"