Solved

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

Posted on 2013-02-07
5
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

737 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