Excel Sheet Link -> Outlook Email Template

[randomreflex]

Hi all,

I am not sure if what I am trying to do it possible or not. I am not sure if maybe there is a way for me to set up and outlook template to work with excel?

I want to be able to have a user click a link in a spreadsheet that will open up and auto fill an email with some of the fields from the sheet. I was able to kind of get something going with the hyperlink formula, but that allow me to place the data in any format except one word after the order. Also it seems that there is a character limit, if i include more than a certain amount of words, the hyperlink does not work right or not at all.

I've attached a sample of sheet of the kind of data I am trying to do this with and a picture of how I am trying to get it to look.

This is current formula i am using.

=HYPERLINK("mailto:"&[@Contact]&"?subject="&A1&"&body="&"WO#"&[@WO]&"    ADDRESS:  "&[@Address]&"   At Fault"&[@[At Fault]]&"    Reason:  "&[@Reason]&"   Invoice Date:  "&[@[Invoice Date]]&"   Original Invoice Amount:  "&[@[Original Invoice Amount]]&"  Adjusted to:  "&[@[Adjusted to]]&"  Profit Loss:  "&[@[Profit Loss]]&"     Department Statement:  "&[@Statement],"Prepare")

https://imgur.com/PYgSkDY

https://imgur.com/r4PhgYL

 

[Worf]

Thanks Peter for formatting the first post.
Using the code below produces the following outcome:

o When a hyperlink is clicked, a table is generated within the spreadsheet.
o This table is transferred to an Outlook mail message.
o The message can be sent manually or automatically

' sheet module
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
rn = Target.Range.Row
Mail_Range_Outlook
End Sub

Public rn%

' *********** standard module
Sub Mail_Range_Outlook()
Dim rng As Range, OutApp As Object, OutMail As Object
[n:o].ClearContents
Range("a109:i109").Copy                 ' header
Range("n109").PasteSpecial xlPasteAll, -4142, False, True
Range("a" & rn & ":i" & rn).Copy          ' data
Range("o109").PasteSpecial xlPasteAll, -4142, False, True
Set rng = Range("n109").CurrentRegion   ' final table
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .To = CStr(Cells(rn, "j"))
    .cc = ""
    .BCC = ""
    .Subject = "Details"
    .HTMLBody = RangetoHTML(rng)
    .Display    ' or Send
End With
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Function RangetoHTML(rng As Range)
Dim fso As Object, ts As Object, TempFile As String, TempWB As Workbook
TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .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=")
TempWB.Close savechanges:=False
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

 

[randomreflex]

Hi!,

Thank you for the help on this.
I added the first section of code to Sheet 1 (sheet 1) and the second code in a module under Modules.
However, I do not know how to activate it. Do I modify my existing hyperlink in column J?

 

[Worf]

• Just click a hyperlink and the mail message will be automatically generated using information from that row.
• Note that my example hardcodes some information: the table header is A109:I109, the generated table is at N: O and I am retrieving the recipient email from column J.

 

[randomreflex]

Ah i got it.

I was using the Hyperlink with the excel formula method =Hyperlink. Looks like the code doesn't recognize that.
I deleted that and created a hyperlink with the excel right click hyperlink menu and that worked.

This is fantastic. Thank you so much !!

 

[randomreflex]

Sorry one more question, is there anyway to make it so that it copies the result of the formula instead of the formula to the table generate at N: O.
Only because in the Profit Loss column i have a formula that is
=N5-M5
or
=[@[Our Original Invoice]]-[@[Our Invoice Adjusted to:]]

but when it generate the table at N:0, the formula re calculate by looking at the values of that specific row (16)


Update: Sorry! I figured it was the xlPasteAll part and googled to see if there was something else i could put there and this worked xlPasteValues

thanks again!