Using VBA in Excel to send link for sharepoint file through Outlook appointment

[petersfa]

I currently have a macro that sends appointments with attachment if required using VBA. The macro works very smoothly but now I have been asked to modify the macro and instead of attaching the file, send a link to the sharepoint file instead.

I am able to modify the program to put the link name, but I think because the sharepoint site and filenames have spaces the link does not work. If the user copies the link to browser then the file can be retrieved.

Ultimately I'd like the link name to appear to the user within the appointment as simply the filename and not the entire path.

The piece of code that creates the message body is this:

myBody = line1 & vbCrLf & vbCrLf & line2 & vbCrLf & vbCrLf & line3 & vbCrLf & line4 & shtSource.Cells(currentrow, 32) _
                & vbCrLf & vbCrLf & line5 & vbCrLf & vbCrLf & line6 & vbCrLf & vbCrLf & line7 & vbCrLf & vbCrLf & line8 & vbCrLf & vbCrLf _
                & "http:\\collaborate\sites\LPSS\Dockets\" & myAttachment

The result in the appointment are the lines of text with spacing and this text displaying what should be a link to the file.

http:\\collaborate\sites\LPSS\Dockets\R9 - AMD - FH 445362 - NOV 15, 2011.pdf</SPAN></SPAN>


The filename is defined by capturing a cell in excel and is in this example R9 - AMD - FH 445362 - NOV 15, 2011.pdf</SPAN></SPAN> I've tried to use the .htmlbody statement with no luck.

Thank you in advance for any assistance

 

[JP2112]

The protocol should be finished with forward, not back slashes.

i.e.

http://

not

http:\\

Outlook should automatically convert URLs to hyperlinks (unless you have turned that feature off).

However, if your hyperlink has spaces, you need to enclose it in < and > signs.

 

[petersfa]

so simple and so useful! thank you JP.. worked like a charm