Email an excel link 1

[larrykathy]

Sure could use some help.

I have a xls file on the network and I want other people to make additions to the file if I email them. I can sucessfully complete this by using the copy range/cells--and then--paste special link in outlook. No problem.
Any ideas how to do that in VBA? This is very slick but it sends the whole file.."Application.Dialogs(xlDialogSendMail).Show".
I just want to email them the link via email using VBA.... Only want one xls file on the network.
Thanks in advance!

 

[PBAPaul]

larrykathy

Sorry if this sounds stupid, but why not just send an e-mail with a hyperlink to the file in it?

Paul

 

[larrykathy]

PBAPaul

Thanks for the reply.
That process would be fine, I'm just trying to do this in VBA, so that a user just clicks a button, up comes the new message box, with the hyperlink already inserted. The user thens selects who recieves the file and clicks send. Can this be done....send link instead of the whole file as above.

Thanks again
Larry

 

[PBAPaul]

Larry

Yes it can be done.

I have written a program where a list of people, generated in Excel, is used to e-mail merge a document in Word that contains various details, many of which are variables created in Excel.

The problem in Excel is that the e-mail facility is, as I understand it, purely to send a workbook. If you want to send "proper" e-mails then you need to do this through Word via Excel. No problem, the coding is quite straightforward. But.....


I have an idea.

When you have finished your main workbook, create a new "Notifier" workbook with just one sheet. Put some text in the workbook like "The file is now ready for edit. Double click on the Hyperlink below". Insert the Hyperlink, then send that workbook to everyone.

This would mean that you never leave Excel. You could set up the "Notifier" workbook with the names of all the people who need to be notified and record who received the notification and when etc.

As you can see, there are various options.

Hope that this helps

Paul

Paul@PBA-Associates.com

 

[larrykathy]

I was able to get this running, what ya think
Sub WhatHyperLink()
Dim WhatIsName As String
Dim Link As String
Dim msg As String
Dim msg1 As String
Dim msg2 As String
Dim msg3 As String

WhatIsName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

With Worksheets(1)
.Hyperlinks.Add .Range("B60&quot, WhatIsName
End With

msg = Range("b60&quot.Hyperlinks(1).Name
msg1 = "This is a link to a XXXXXXX: "
msg2 = &quot; <File:&quot; & msg & &quot;> &quot;
msg3 = &quot; Please use this XXXXXXX to update. Thanks!&quot;

Link = &quot;mailto:&quot; & &quot;&quot; & &quot;?subject=&quot; & &quot; Link&quot; & &quot;&body=&quot; & msg1 & msg2 & msg3
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
Range(&quot;b60&quot.Select
Range(&quot;b60&quot.ClearContents
End Sub
Thanks
Larry