PDA

View Full Version : Reference a person in e-mail from VBA



Rolly_Sefu
09-03-2019, 04:12 AM
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
09-03-2019, 08:22 AM
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 (paulked@mail.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! :p

Rolly_Sefu
09-11-2019, 05:53 AM
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.