hyperlinks

kevin lazell

Well-known Member
Joined
Jun 2, 2004
Messages
513
hi guys
i have a column of hyperlinks that when clicked it will send an email (at the moment there are 120 hyperlinks
in column c2 downwards ) my question is is it possible to have a commandbutton that when clicked
it will activate each hyperlink in column c and send an email if so how can i do this please tia
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
What does your hyperlink formula look like? Is the Link_Location part plain text between two quotes, or does it have cell references? Does your Friendly_Name have any commas in it?

For a simple, no cell reference, straight text hyperlink formula like the following:
Excel Formula:
=HYPERLINK("mailto:test@nowhere.com?subject=Test&body=Testing hyperlink.","Hyperlink test")
I can get the following VBA to successfully create a new email (clicking on the link also works):
VBA Code:
Sub HyperlinkClick()
    ActiveWorkbook.FollowHyperlink Mid(ActiveCell.Formula, 13, InStrRev(ActiveCell.Formula, ",") - 14)
End Sub
(The 13 is to start with the text at the m of mailto, and the 14 is to end with the period before the ending quote of the Link_Location. Including the opening and closing quotes doesn't work with the FollowHyperlink function.)

However, as soon as I put cell references into the text, the VBA fails, where clicking on the hyperlink with the mouse works fine. Tying the code to a button and creating all of the emails in column C is simple, but the code needs to work first.

So if the hyperlink formula is simple, this is a start. Otherwise, there might need to be some parsing of the code and additional code to create the new mail messages in VBA based on the parsed text.
 
Upvote 0
hello shknbk2
thank you for taking the time to help me on this, below is the hyperlink i am using
=HYPERLINK("mailto:"&B31&"?SUBJECT="&$D$2&"&BODY="&$E$2,"SEND EMAIL")
when i click on the hyperlink outlook opens i then click on the email account i wish to use and then click send
this all works fine. i am open to changing the hyperlink to what you suggested if it makes life easier
with 120 emails (and growing) to send i thought it would be lovely to just click a command button
and have it send an email to each person in the range("c2:c120")
 
Upvote 0
That hyperlink formula is easy enough. The following code will create email messages for all cells in C2 on down (assuming there is nothing else in column C after the list of hyperlinks). The emails will be displayed as if you clicked on the links. There will be a lot of them. If you wanted, you could pause after each one by displaying a message box that you would need to click on before it creates the next one, but that is just a further enhancement option.
VBA Code:
Sub HyperlinkClick()
    Dim c As Range
    Dim sBody As String
    Dim sSubject As String
    Dim sTo As String
   
    sSubject = Range("D2").Value
    sBody = Range("E2").Value
    For Each c In Range(Range("C2"), Cells(Rows.Count, 3).End(xlUp))
        sTo = Cells(c.Row, 2).Value
        ActiveWorkbook.FollowHyperlink "mailto:" & sTo & "?SUBJECT=" & sSubject & "&BODY=" & sBody
    Next
End Sub
I suppose the good thing about this code is that by using the hyperlink, whatever program that is set up to receive the mailto hyperlink would be used. You wouldn't have to use Outlook to have the emails generated.

If you did want to use Outlook, the following formula will also work. It would give you more freedom to tweak the emails. For example, you could automatically send the email rather than have them displayed (using .Send instead of .Display) if you trusted that everything would be correct. Additional changes to the body section could provide formatting options as well. Etc.
VBA Code:
Sub HyperlinkToMailViaOutlook()
    Dim objApp As Object
    Dim objMail As Object
    Dim c As Range

    Set objApp = CreateObject("Outlook.Application")
    For Each c In Range(Range("C2"), Cells(Rows.Count, 3).End(xlUp))
        On Error Resume Next
        Set objMail = objApp.CreateItem(0)
        With objMail
            .To = Cells(c.Row, 2).Value
            .Subject = Range("D2").Value
            .Body = Range("E2").Value
            .Display
        End With
        On Error GoTo 0
        Set objMail = Nothing
    Next
    Set objApp = Nothing
End Sub
 
Upvote 0
hi shknbk2
not having any joy with either code the first code you offered up i thought it was going to work but the body of email came up as chineese characters
the second one (for outlook) outlook opened up it asked me which account i wanted to use when i selected the account a box opened up asking me to open an account or something
like that ( which is odd because i have sent emails through outlook when manually clicking on the hyperlinks) so i am at a complete loss with this
thank you once again for your help it is very much appreciated
 
Upvote 0
Are the To and Subject lines at least working properly? Is the body cell $E$2 straight text or is it also based on cell references?
 
Upvote 0
Does it change anything if you change
VBA Code:
sBody = Range("E2").Value
to
VBA Code:
sBody = Range("E2").Text
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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