Using custom field data in mail body + mailto hyperlink

[VincentM]

I am breaking my head about this already for some time. I have a custom form for eventually creating an email with a mailto hyperlink inside the body.

In the custom form I define a.o. [product], [subject] and the [receivers] for the mailto hyperlink.
All the information is correctly computed corresponding fields.

1) How can I now make sure the [product] is going to be incorporated in the text of the mail:
e.g. bla bla bla [product] bla bla bla ?

2) How can I add the reply hyperlink, using the [subject] and [receiver] information from the custom fields.

The mails are send outside our company, but we want to force the suggested reply format by giving the mailto hyperlink. I would really appreciate any suggestions!

 

[Diane Poremsky]

How about using merge fields or bookmarks? An example using merge fields is here http://www.slipstick.com/outlook/contacts/mail-merge-outlook-using-macro/

 

[VincentM]

Dear Diane and thanks for the quick respons,

I hoped to do everything in the one form; fill in the custom fields and send...
I guess, however this might be a work around for question 1, but how do I do this for question two, where I have to add the field values into a mailto hyperlink?

 

[Diane Poremsky]

In that case, you should be able to use a formula to the fields together - create a new formula field and enter the url and the custom fields. The field won't be clickable though. To make a mailto link, you need to use HTML code to construct it.

 

[VincentM]

Hi Diana,
After some time finding bits and pieces, I have come quite far:

-----------
Sub StampDate()
Dim objOL As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objItem As Object
Dim strStamp As String
On Error Resume Next
Set objOL = Application
Set objItem = objOL.ActiveInspector.CurrentItem
If Not objItem Is Nothing Then
If objItem.BodyFormat = olFormatHTML Then
Set objNS = objOL.Session

strStyle = "'font-family:" & Chr(34) & "Calibri" & Chr(34) & ";color:black'"
strTextEng = "<p>" & "<span style=" & strStyle & ">" & "Please find attached the new order" & "<p>"

strLink = "<p>" & "<span style=" & strStyle & ">" & "<b><a href=" & Chr(34) & "mailto:someone@example.com?Subject=Confirmation" & Chr(34) & "target=" & Chr(34) & "_top" & Chr(34) & ">Hyperlink" & "</span></p></a></b>"


Set olkMsg = Outlook.Application.ActiveInspector.CurrentItem


objItem.HTMLBody = Replace(objItem.HTMLBody, "</body>", _
olkMsg.subject & strTextEng & strLink & "</body>", _
, , vbTextCompare)
End If
End If
Set objOL = Nothing
Set objNS = Nothing
Set objItem = Nothing

End Sub
---------

Al is working well and basically results is a the body of the mail including: 1) the subject line (olkMsg.subject), 2) a cutom text (strTextEng) + 3) the hyperlink (strLink).

I my custom form I have more fields which I can fill in, however in no way I am able to incorporate them in the body.
As with olkMsg.subject, I am able to use the value in To (olkMsg.to) / CC (olkMsg.cc) and BCC (olkMsg.bcc), but not any of my user identified fields e.g. PO number (e.g. olkMsg.TextBox1) or any else.

What am I missing??? Hope you can help me how to call on these custom fields.

PS Also I have been able to use the one of the fields to show correctly in the hyperlink, solving the custom hyperlink question, if I am able to use other user identified fields.

Hope you could help me.

 

[Diane Poremsky]

Refer to custom fields as userproperties.
objItem.UserProperties("customfield")

Also, i see this:
Set olkMsg = Outlook.Application.ActiveInspector.CurrentItem

but you are referring to the fields as objItem:
objItem.HTMLBody = Replace(objItem.HTMLBody, "</body>", _

actually, you set the current item twice.

 

[VincentM]

Hi Diane,

Can you help me a bit further, I have adjusted as follows to add value of Textbox31 in the body as well, however now the macro is not working:

objItem.HTMLBody = Replace(objItem.HTMLBody, "</body>", _
objItem.UserProperties("TextBox31") & olkMsg.subject & strTextEng & strLink & strTextCh & strTextCh2 & "</body>", _
, , vbTextCompare)

Also, It seems I need Set olkMsg = Outlook.Application.ActiveInspector.CurrentItem, as it will not show olkMsg.subject when I delete it.

 

[Diane Poremsky]

objItem and olkMsg are the same message... what you need to do is change the olkmsg to objitem

objItem.UserProperties("TextBox31") & olkMsg.subject & strTextEng & strLink & strTextCh & strTextCh2 & "</body>", _