Solved

Excel 2010: How to output a hyperlink from macro to email message

Posted on 2013-02-07
5
240 Views
Last Modified: 2013-02-11
Hello,

I have an excel sheet with a macro button to send a welcome email to staff. When run, the maco takes data filled in the spreadsheet (name, email address, line manager, position etc) and opens an email to be sent to the user. Part of the email body is a hyperlink. I cannot change this hyperlink as it is another department but it has white spaces in the link. I cannot seem to get the link into the email body as a fully formed link - the white spaces break the link so it is malformed and does not link to the page.

Does this make sense? I have tried chr(34) & ... and I have tried %20% and also tried """" but I cannot get these to work.

The link is
http://intranet/People/CorporateServices/IT/New to Us/Home.aspx

The link appears in the email body as
http://intranet/People/CorporateServices/IT/New
(breaks at the first white space).

This is the cheap & cheerful & butchered code;

Sub EmailWelcome()
    Dim FirstName As String, Subj As String, LineManager As String
    Dim GPAccess As String, POlevel As String, Email As String
    Dim Msg As String, URL As String
    Dim myHelpLink As String
    Dim r As Integer, x As Double
    

'       Get the email address
        Email = Sheets(1).Range("C14")
        FirstName = Sheets(1).Range("C5")
        LineManager = Sheets(1).Range("C10")
        GPAccess = Sheets(1).Range("C37")
        POlevel = Sheets(1).Range("C38")
        
'       Message subject
        Subj = "Welcome - ICT Information "

'       Compose the message
   
        Msg = ""
        Msg = Msg & "Dear " & FirstName & "," & vbCrLf & vbCrLf

        Msg = Msg & "Hello and welcome to the team!" & vbCrLf & vbCrLf

        Msg = Msg & "We have put some important information about our IT systems on the Intranet for you" & "." & vbCrLf & vbCrLf

        'Msg = Msg & "" & "http://intranet/People/CorporateServices/IT/New to Us/Home.aspx" & "" & vbCrLf & vbCrLf
        
        Msg = Msg & "Here you will find important information about passwords (which password for which system), working from home and backing up your data correctly. For example, it is your responsibility to back up files to the server/Intranet, and we will look after them from there. " & vbCrLf & vbCrLf
        Msg = Msg & "It will only take about 10 minutes to read all the pages on the link above, and it will clear up some of the questions you have." & vbCrLf & vbCrLf

        Msg = Msg & "Just email us if there is anything that we can help you with (related to ICT or Facilities) after checking the links above." & vbCrLf & vbCrLf

        Msg = Msg & "We hope that this is the start of a long and positive time working together. " & vbCrLf & vbCrLf

        'Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
        Msg = Msg & "Kind Regards" & vbCrLf
        Msg = Msg & "The IT and Facilities Team"
        
'       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
                
'       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")         '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
       ' Application.SendKeys "%s"
End Sub

Open in new window



I know this code is very basic, but any guidance appreciated.
Thanks
0
Comment
Question by:concern_support
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:TazDevil1674
ID: 38867514
I use .HTMLbody instead of .body

    .HTMLBody = "<HTML>" & _
        "<p style='font-family:calibri;font-size:14'>" & _
        "Duty Manager Rota for this week is outlined below.<BR><BR>" & _
        "<B><a href=chr(34)mailto:email@domain.com?Subject=Email via DM Rotachr(34)>NT Team</a></B> Mailbox - " & _
        "The DM is the first point of contact for any issues; e-mail should be sent to the " & _
        "<B><a href=chr(34)mailto:email@domain.com?Subject=Email via DM Rotachr(34)>NT Team</a></B> (email@domain.com)" & _
        "</p><BR>" & _
        RangetoHTML(Rng) & _
        "</HTML>"

Open in new window


I am also adding some cells converted to HTML to this...
0
 

Author Comment

by:concern_support
ID: 38867963
How do I specify the .body as .HTMLbody when I don't use .body?

I am using

Dim Msg as String, URL as String

Msg = Msg & "....."
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 

Open in new window

Do I need to rewrite the complete approach to the macro? (I want the email to open on screen before sending, that the user has to manually click send on it).

thanks
0
 
LVL 9

Accepted Solution

by:
TazDevil1674 earned 500 total points
ID: 38868025
You can do that...check this link - its where i got most of the source to do it myself


http://msdn.microsoft.com/en-us/library/office/ff458119(v=office.11).aspx
0
 

Author Comment

by:concern_support
ID: 38868052
Ok, thanks, looking at it now.
0
 

Author Closing Comment

by:concern_support
ID: 38875151
I had to re-write the complete macro, using the URL samples submitted
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now