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?
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,855
Office Version
  1. 2013
Platform
  1. Windows
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))
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,855
Office Version
  1. 2013
Platform
  1. Windows
OK....

Try:
Then there's just the matter of converting the link to show 'reminder'? :eek:
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
 

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,855
Office Version
  1. 2013
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,114,607
Messages
5,548,948
Members
410,884
Latest member
melifreeman
Top