Log In
Register
Home
October 25, 2017
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
October 25, 2017
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
According to HTML language, that's the way to create a hyperlink:
https://www.w3schools.com/html....._links.asp
It's the only way.
October 25, 2017
Offline
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?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
October 25, 2017
Offline
Thank you so much.
October 25, 2017
Offline
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,
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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")
