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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Any feedback on my suggestion please?
 

FFischer

Board Regular
Joined
Oct 10, 2013
Messages
59
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,819
Messages
5,598,293
Members
414,223
Latest member
Accountant2B

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
Top