Hypelink to text String

FFischer

Board Regular
Joined
Oct 10, 2013
Messages
59
Dear colleagues

I have a code snippet embedded in a Form Command Button as follows:

Code:
Private Sub SendMail_Click()
    Dim OutApp As Object, OutMail As Object
    Dim MyTo As String, MyFile As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    MyTo = Me.Address.Value
    MyFile = Me.File1.Value
        With OutMail
            .To = MyTo
            .Subject = "Test Attachment"
            .Body = "Message Body"
            .Attachments.Add (MyFile)
            .Send
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
End Sub

The code works fine if Me.File1.Value is a Text String. However, I have inherited a form in which the file names are hyperlinks, and I do not have the luxury of changing them to text within the form. Can this be accomplished with VBA? I was wondering if InStr was part of the solution.

Thanks for any assitsance.

FF
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Hi FF

Please do not complicate things that much. Consider instead of that code:

Code:
Private Sub SendMail_Click()
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = Address.Value
        .Subject = "Test Attachment"
        .Body = "Message Body"
        .Attachments.Add Cells(1).Hyperlinks(1).Address
        .Send
    End With
End Sub

Here I get the hyperlink address from cell A1 on the active sheet. Please change to suit.
 
Upvote 0
Any feedback on my suggestion please?
 
Upvote 0
Sorry Wigi - I though I submitted a reply when you repsonded, but evidently it didn't take. Your solution worked. Please note that for a Command Button on an Access form the syntax is Me.TextBoxName.Hyperlink.Address

Also I was able to use another code snippet suggestion of yours regarding sending multiple attachments. Thanks for that one as well!

Frank
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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