Hi,
I am using VBA to edit all of my Hyperlink Screen Tips, which works really well on my master worksheet. I am looking to edit all the other Worksheet Hyperlinks as well. This is what I have run on my Master Sheet;
Sub HLSCREENTIP()
Dim hl As Hyperlink
Dim rng As Range
Dim hlTip As String
'// Refine range if needed
Set rng = Sheets("SoCal Master").UsedRange
'// To apply to the active sheet
'// Set rng = ActiveSheet.UsedRange
'// Tip to replace current tips
hlTip = "Press For BoM"
For Each hl In rng.Hyperlinks
hl.ScreenTip = hlTip
Next hl
End Sub
I need to change the Screen Tip for the individual sheets, and would like to use code to run through all of them (over 200). I can edit the second Tip, however, i cant seem to fingure out how to get it to run on all sheets (Except Sheet 1, which is the Master Sheet). Can someone help me please?
Hey All,
Any chance there is an easy way to edit all Hyper link Screen Tips on a Worksheet @ once? I have a sheet with a 100 or so links, that i want a screen tip displayed. Is there an easy way?
thanks
I'm trying to generate an email from excel, so that the CC'c will contain a list of people from vlookup function.
Any suggestion?
Hi again
I've managed to get the following macro working except for one small thing - the link to the folder where the spreadsheet (that will be attached to the email) resides (which I got to work fine in a macro to send out tasks) just ends up as plain text and is not clickable
Can anyone help me to make this link clickable in the email so that when the recipient receives the email they can just click on the link and go straight to the relevant folder please?
(I've highlighted the line with what I would like to work as a link in red)
Code:
Sub Send_Schedule()
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Email Schedule")
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
'Enter the file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("C1:C100")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = cell.Value
.Subject = "Testfile"
.Body = "Please find attached a copy of this month's reporting schedule (pictorial calendar format)." & _
Chr(10) & "" & _
Chr(10) & "The hyperlink to the actual folder is embedded below for your convenience:" & _
Chr(10) & " " & _
Chr(10) & " I:\Files\More Files\Meeting Data\00. Current Month Meeting Data "
'& cell.Offset(0, -1).Value
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Many thanks in advance
Rae
I am trying to insert (embed) an email link within an excel document that
could be clicked so the excel document would automatically be send to a
distribution list of predefined users. For example this is a Warehouse
Requisition that an enduser would fill out and within the excel document I
would like to include an Icon that would send that sheet to a list of users
who manage the requisitions. Does anyone know if that is possible.
Thanks,
Natalie
I have an .xls spreadsheet that has a column which contains email links that appears like this: Email
When you click the cell or hover it shows the email address. I would like to convert the entire column to actually display the email address. I need to export these email adddresses once I get them. The only way I have found that I can get the email address is to create a new column, then click the Email link and it opens up an outlook email. Then I have to copy and paste the email address into the new column. I have over 2000 cells in this column. Is there any way to convert this column to the actual email addresses that are embedded in it? Any help would be appreaciated!
After searching for answers i found rons code for sending html email messages using ranged data, but i have an issue with the signature itself. The code works when a file is actually referenced, but what i need it to do is pull a users signature using a wild card. Since users will name their signature to different names, i need it to pull the default signature.
Code:
SigString = "C:\Documents and Settings\" & Environ("username") & _
"\Application Data\Microsoft\Signatures\" & "*.htm"
but using this seems to give a error 53, object not found. Its seem i am missing something or going about it in the wrong fasion. Any assistance will be appreciated.
What is the easy way to create forms in different sheets.......
In Main Sheet (Sheet1) each row contains some informations about a particular item.
I want to create Forms in separate Sheets and datas for this Form 1 (Sheet2) should be datas of Row#1 of Sheet1 & datas for this Form 2 (Sheet2) should be datas of Row#2 of the Sheet1.......
Hi Guys,
Does anyone know of a web site which offers Excel tips and tricks.
Is there a free book which I can download that will give me tips and tricks.
Thanking you in advance,
Regards
J-O
Hy guys,
I still work with excel 2007, so, can someone tell me please if the 2010 version shows the formula tips, when we are tipping a formula in the "name manager box", and in the "conditional formating box".(when you use a formula to do conditional formating), like they are shown when we tipe a formula in a spreadsheet cell??
I think that would be a great help
