Forum

Notifications
Clear all

BACKUP LINK

10 Posts
2 Users
0 Reactions
87 Views
(@josros60)
Posts: 47
Trusted Member
Topic starter
 

have the code below which works good, this one add the link for the spreadsheet on body of the email to click to open, how can I modify it that it can also add the link of the backup that is pdf format located in the same folder.

 

Hi,

have the code below which works good, this one add the link for the EFT spreadsheet on body of the email to click to open, how can I modify it that it can also add the link of the backup that is pdf format located in the same folder.

Here the code:

VBA Code:
Sub Make_Outlook_Mail_With_File_Link()
'Working in Excel 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "" & _
"Hi Afia,

" & _
"
" & _
ActiveWorkbook.Name & " is created.
" & _
"Please click on this link to open the file : " & _
" & ActiveWorkbook.FullName & _
""">Link to the file" & _

"

Note: The EFT backup link is on the spreadsheet...." & _
"

Thank you," & _
"

"

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "RE: " & Range("SUBJECT").Value 'ActiveWorkbook.Name
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub

Thank you

 

 
Posted : 07/11/2021 5:45 pm
(@catalinb)
Posts: 1937
Member Admin
 

Hi Jose,

You have to use the href tag:

"<a href=""" & LinkHere & """>" & ActiveWorkbook.FullName & "</a>"

Are you sure you want to send a local file path to another user? On that computer, the user might not have the same path.

 
Posted : 10/11/2021 12:06 am
(@josros60)
Posts: 47
Trusted Member
Topic starter
 

Hi Catalina,

Yes, spreadsheet working fine when sending I am not using letter mapping instead pointing only to the  network path shared folder and they're able to open it.  Just want to add the link to the backup for the EFT in the same email as well.

 

Is there another way to do it instead of href tag?

 

Thank you for your response.

 
Posted : 10/11/2021 12:05 pm
(@catalinb)
Posts: 1937
Member Admin
 

According to HTML language, that's the way to create a hyperlink:

https://www.w3schools.com/html/html_links.asp

It's the only way.

 
Posted : 10/11/2021 3:25 pm
(@josros60)
Posts: 47
Trusted Member
Topic starter
 

thank you Catalin.

If I have pdf hyperlink (complete address path)backup in cell C6 adding this line to the VBA code above

 

"<A href=" & Sht.Range("C6") & ">" & Sht.Range("C6") & "</A>" &

 

Will this work?

 
Posted : 11/11/2021 2:26 pm
(@catalinb)
Posts: 1937
Member Admin
 

No, will not work.

Why don't you use the example I sent? your double quotes are not properly added, already gave you the correct string:

"<a href=""" & Sht.Range("C6") & """>" & Sht.Range("C6") & "</a>"

The href link must be enclosed between double quotes, if you print your string in immediate window you will see that it has no double quotes.

 
Posted : 11/11/2021 11:58 pm
(@josros60)
Posts: 47
Trusted Member
Topic starter
 

Thank you so much.

 
Posted : 12/11/2021 12:26 pm
(@josros60)
Posts: 47
Trusted Member
Topic starter
 

Hi Catalin,

I did as you suggested and here is the code modified, the only problem the pdf backup will no pointo the folder path,  the Cell "E3" is the hyperlink to the pdf file, no sure how can make it to work:

 

here is the code:

 

Sub Make_Outlook_Mail_With_File_Link()
'Working in Excel 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "<font size=""3"" face=""Calibri"">" & _
"Hi ,<br><br>" & _
"<br><B>" & _
ActiveWorkbook.Name & "</B> is created.<br>" & _
"Please click on this link to open the file : " & _
"<A HREF=""file://" & ActiveWorkbook.FullName & _
""">Link to the file</A>" & "</B> <br> <br>" & _
"PDF BACKUP" & "</B> is saved.<br>" & _
"<A HREF=""file://" & ActiveWorkbook.ActiveSheet.Range("E3") & _
""">Link to the file</A>" & _
"<br><br>Note: The EFT backup link is on the spreadsheet as well...." & _
"<br><br>Thank you," & _
"<br><br></font>"

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "RE: " & Range("SUBJECT").Value 'ActiveWorkbook.Name
.HTMLBody = strbody
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If

End Sub

When click works good and shows this in outlook:

Hi ,

Primus Batch EFT...Nov 162.xlsm is created.
Please click on this link to open the file : Link to the file

PDF BACKUP is saved.
Link to the file  ...............But this will not work when click to follow shows this:  file://325 1-10 primus ra-jr.pdf no the complete network shared folder.

 

Thank you,

 

thank you,

 
Posted : 18/11/2021 5:09 pm
(@catalinb)
Posts: 1937
Member Admin
 

Put the full path in E3, not just the file name.

Or, put the network folder address in code before E3 value:

NetworkPath & ActiveWorkbook.ActiveSheet.Range("E3")

 
Posted : 19/11/2021 12:29 am
(@josros60)
Posts: 47
Trusted Member
Topic starter
 

Thank you very much for your help, worked perfectly your suggestion.

 
Posted : 19/11/2021 7:44 pm
Share: