Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-02-07
5
Medium Priority
?
268 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 1500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

721 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