PDA

View Full Version : Solved: Send Email with a link to the Excel file



GuageGlass
11-26-2010, 12:26 AM
I need to write a code to enter a clickable link to the open file in the body of an email that is produced by a Macro. The problem seems to be that the HTML code doesn't like the VBA code. I have been trying to use the "ThisWorkbook.FullName" code becuase this code is used on many files saved in multiple locations on our network. When I try to make the "ThisWorkbook.FullName" an HTML link it comes across in the email as "ThisWorkbook.FullName" and not the file path. My solution so far has been to display the path in normal text and then on the next line provide a HTML link to the network folder.


With OutMail
.To = ""
.CC = ThisWorkbook.Sheets("Summary").Range("PME").Value & ";" & ThisWorkbook.Sheets("Summary").Range("PEE").Value & ";" & ThisWorkbook.Sheets("Summary").Range("PME").Value & ";" & ThisWorkbook.Sheets("Summary").Range("PROJE").Value
.BCC = ""
.Subject = ThisWorkbook.Sheets("Summary").Range("B4") & " - " & "part number " & ThisWorkbook.Sheets("Summary").Range("F11") & " - released for build"
.HTMLBody = "This BOM has been reviewed and released for entry, purchase & build. You can find the released file in the job folder.<br><br>" & _
ThisWorkbook.FullName & _
"<br><a href = \\Bwakrdc2.bwrogers.net\vas$\Jobs>V:\VAS\Jobs\</a>" & _
RangetoHTML(rng) & _
"<br><br>If you have any questions, please call.<br>" & _
"This message has been sent using the VAS BOM costsheet.<br><br><br>" & _
ThisWorkbook.Sheets("Summary").Range("BOMR").Value & _
"<br>B.W. Rogers Company<br>"


The current outcome looks like this.

This BOM has been reviewed and released for entry, purchase & build. You can find the released file in the job folder.

V:\VAS\Jobs\30000\30000 - VasFr730106 BomCostSheet.xls
V:\VAS\Jobs\

Bob Phillips
11-26-2010, 04:10 AM
Try this



.HTMLBody = "This BOM has been reviewed and released for entry, purchase & build. " _
& "You can find the released file in the job folder.<br><br>" _
& "<br><a href = """ & ThisWorkbook.FullName & """>" & ThisWorkbook.Name & "</a>" _
& RangetoHTML(rng) & "<br><br>If you have any questions, please call.<br>" _
& "This message has been sent using the VAS BOM costsheet.<br><br><br>" _
& ThisWorkbook.Sheets("Summary").Range("BOMR").Value _
& "<br>B.W. Rogers Company<br>"

GuageGlass
11-26-2010, 09:22 AM
That works perfect! Thanks for the help.