VBA to create email link and quote cells

[Alan Stockenstroom]

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?

 

[Alan Stockenstroom]

https://ibb.co/kOCbAn

This is a small example of the sheet I have

 

[daverunt]

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.

=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]

OK....

Try:
Then there's just the matter of converting the link to show 'reminder'?

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]

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

    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