Reference a person in e-mail from VBA

[Rolly_Sefu]

Hello,

I have created an automated e-mail macro, but i am not able to reference a person: "@Rolly_Sefu"

I have tryed with the hyperlink but it's not the same, the text is blue and underlined.

With the "@" the whole text is highlighted, with a light gray color and it's easier to see who the messages is referring to.

Any ideas ?
Thank you.

[paulked]

Highlighting only seems to work in Outlook, and then when you are typing directly into a new mail. When you type the @ charater, Outlook looks at your contacts to pick one out. If that person doesn't exist in your contacts it doesn't highlight them.

Similarly, if you paste the code into a new message then the contact isn't highlighted eg typing:

Can you invite @paul@topmail.com to the party?

would highlight @paul@topmail.com but only if paul@topmail.com existed in your contacts, pasting the same phrase into an Outlook new message would not highlight anything, which is probably why it is not working.

I can't find a way to highlight specific text in Excel, you may have to transfer the text to Word and send it from there, sorry

Please don't try and mail me, that is not my address!

[Rolly_Sefu]

Ok, thank you for trying

I also did not find anything

A small side question.

I run a function to import the signature after the body

FindFirstFile = Dir$(Environ("appdata") & "\Microsoft\Signatures\" & "*.htm")
If (FindFirstFile <> "") Then
    SigString = Environ("appdata") & "\Microsoft\Signatures\" & FindFirstFile
End If
If Dir(SigString) <> "" Then
    Signature = GetBoiler(SigString)
Else
    Signature = ""
End If

OutMail:

With OutMail    
    .display
    .To = ..
    .Cc = ...
    .Subject = ....
    .HTMLBody = "Hello,<br><br>" _
    & RangetoHTML(rng) & "<br>" _
    & "Thank you.<br><br>" & Signature
    .Attachments.Add ActiveWorkbook.FullName
End With

RangetoHTML(rng):

Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook


TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial Paste:=1
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
End With


'Publish the sheet to a htm file
With TempWB.PublishObjects.Add(SourceType:=xlSourceRange, fileName:=TempFile, Sheet:=TempWB.Sheets(1).Name, Source:=TempWB.Sheets(1).UsedRange.Address, HtmlType:=xlHtmlStatic)
    .Publish (True)
End With


'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")


'Close TempWB
RangetoHTML = Replace(RangetoHTML, "<!--[if !excel]>&nbsp;&nbsp;<![endif]-->", "")
'removes a space before the range
TempWB.Close SaveChanges:=False


'Delete the htm file we used in this function
Kill TempFile


Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Function GetBoiler(ByVal sFile As String) As StringDim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

I have recently added a picture into the signature, but after the script runs the picture gives an error "This picture can't be displayed"

Any ideas how to fix it ?

Thanks.