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

petersfa

New Member
Joined
Jan 26, 2012
Messages
5
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:


HTML:
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
:)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
The protocol should be finished with forward, not back slashes.

i.e.

Code:
http://

not

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,351
Messages
6,178,058
Members
452,822
Latest member
MtC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top