VBA to create email link and quote cells

Alan Stockenstroom

New Member
Joined
Jul 19, 2010
Messages
13
Hi All,

I need help with something. I have been trying to get it to work with hyperlink, but I don't think its exactly right for what I need.

I have information in a spread sheet, and I need to create a reminded email and quote certain cells in the body of the emails.

Basically what I am after is (I have the sheet with all the info in the cells and also the email addresses in cells) I want to run a VBA code that would generate an email link in a cell of the row where the information is help.

In this I need the hyperlink that was created by the VBA code, to basically contact a predefined email body and also quote Cell A1, A2, A3.

In the subject of this email, I must say "text 1" and quote cell A1.

Can anyone help me with this please?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi,


here's an example of a mailto link for row A that can be used.
However I am having difficulty getting VBA to write this due to the litter of quotes in the string and the addition of a row variable.
Perhaps someone else will be able to crack it before I do.


Code:
=HYPERLINK(CONCATENATE("mailto:",A1,"?subject=predefined text ",B1,"&body=Good Day, %0d%0a%0d%0aThis is a friendly reminder that I 

have a delivery of ",D1,  " for you.%0d%0a%0d%0aOrder Details Below:%0d%0a%0d%0a",B1, "%0d%0a",C1, "%0d%0a", D1))
 
Upvote 0
OK....

Try:
Then there's just the matter of converting the link to show 'reminder'? :eeek:
Code:
Sub create_Hyperlink()

    Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    For Each Cell In Rng
    Rw = Cell.Row
    
    
    If Cell.Value <> "" Then
       Cells(Rw, 6).Formula = "=HYPERLINK(CONCATENATE(""mailto:"",A" & Rw & ",""?subject=predefined text "",B" & Rw & ",""&body=Good Day, %0d%0a%0d%0aThis is a friendly reminder that I have a delivery of "",D" & Rw & ",  "" for you.%0d%0a%0d%0aOrder Details Below:%0d%0a%0d%0a"",B" & Rw & ", ""%0d%0a"",C" & Rw & ", ""%0d%0a"", D" & Rw & "))"
    End If
    
    Next

End Sub
 
Upvote 0
Hi,

As the concatenate function is used to get around the number of parameters limit for the hyperlink function, it looks as though you can't edit it to display your required text.

My solution would be to use 2 columns - say G and F and hide column G.
In G you would create the concatenate string for the mail content.
In F you would hyperlink to G.

That's the best I can do.
Someone else may post something better/cleaner

Code:
    If Cell.Value <> "" Then
        
       Cells(Rw, 7).Formula = "=CONCATENATE(""mailto:"",A" & Rw & ",""?subject=predefined text "",B" & Rw & ",""&body=Good Day, %0d%0a%0d%0aThis is a friendly reminder that I have a delivery of "",D" & Rw & ",  "" for you.%0d%0a%0d%0aOrder Details Below:%0d%0a%0d%0a"",B" & Rw & ", ""%0d%0a"",C" & Rw & ", ""%0d%0a"", D" & Rw & ")"
       Cells(Rw, 6).Formula = "=HYPERLINK(G" & Rw & ",""reminder"")"
       
    End If
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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