• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set Hyperlinks for a given list to prepare E-mails with attachment

M

Mahir Yagimli

Member
Hello Everyone,

I have posted a question before, which Narrayan answered masterfully.

Here I am again trying to solve a problem which exceeds my simple vba knowledge.

I tried to create Hyperlinks for a given list of E-mails with prepared Body, CC, Subject and Attachment. I thought to put this in a do while loop, but somehow I got stucked.

The Idea here is to put a hyperlink beside every e-mail adress with the text E-mail

Here is a link, which is explaining a formular:

http://datapigtechnologies.com/blog/index.php/emailing-from-excel-using-the-hyperlink-function/

I recorded a macro to see how it is done in vba, but the problem is to make it dynamic.

Code=vb:

ActiveSheet.Hyperlinks.Add anchor:=Selection, Address:= _
"mailto:solveproblem@chandooforum.com?cc:bigbrotheris@watchingyou&subject=a%20b%20c", _
TextToDisplay:="send e-mail"

This is how VBA record it. If I try to write the cellname or activecell it is not working even as formula.

I tried to write a public function, but it is........ just not working.

Can someone please give me some hints, what I can do? Where I can get more informations.

I'd be very thankful for your help.

My best regards
 

Attachments

  • Hyperlinks example.xlsm
    15 KB · Views: 4
Hi Mahir,

Please see the attached. Use the built-in HYPERLINKS function, and you concatentate strings and cell references to make it dynamic. Note that there is a limit to how long your hyperlink can be, and with the current body message you have, the link will be too long. :(
 

Attachments

  • Hyperlinks solution.xlsm
    16.3 KB · Views: 13
Luke M said:
Hi Mahir,

Please see the attached. Use the built-in HYPERLINKS function, and you concatentate strings and cell references to make it dynamic. Note that there is a limit to how long your hyperlink can be, and with the current body message you have, the link will be too long. :(
Click to expand...

@Luke: Thank you for your fast response. You are great, last time you were the first to answer again. Thank you for the information.
 
@Debraj: Thank you for your information. That will definetly help me, when we shif to Excel 2013.

Still I have a question left. I see that you can use a hyperlink function instead of VBA. What if I try to put an attachment to the mail is that also possible with the hyperlink function or is there a possibility to create a macro?

The other question is about the magical 255 characters per cell. I know that we can adjust the cell value to something about 32.000 characters. Would that be a help to enlarge the text body?

Thank you all for your patience with me.

My best regards
 
I would like to suggest you to use VBA.
try to google ShowEnvelope Option. It has a lots of customizable feature.

Code: 
Sub RoundedRectangle1_Click()
    ActiveWorkbook.EnvelopeVisible = Not (ActiveWorkbook.EnvelopeVisible)
    With ActiveSheet.Shapes("Rounded Rectangle 1").TextFrame.Characters
        .Text = IIf(.Text = "Show Envelope", "Hide Envelope", "Show Encvelope")
    End With
End Sub
 

Attachments

  • ShowEnvelope.xlsm
    14.1 KB · Views: 7
You must log in or register to reply here.
Back
Top