VBA Outlook Mail Body

Question

In VBA scripting ,I am trying to write a Sub Function which has the following signature

Sub(taskName As String , myGroup As String, myFile As String ,myPer As String, RelatedTasks() As String    )

 Dim olApp As Outlook.Application
 Dim m As Outlook.MailItem

 Set olApp = New Outlook.Application
 Set m = olApp.CreateItem(olMailItem)

 With m
   .display
   .To = "[email protected]"
   .Subject = "Test Events"
   .HTMLBody/.body = ...    
End Sub

Email Body is as follows:

Hello All,

Please find the following information.

TASK: taskName

RELATED TASK:RelatedTasks()

FILE : myFile

PERSON : myPer

In the Sub function , the pattern to the left of colon is always constant.And the right side will change based on the inputs to the function.

For that I am reading the Template.htm which contains the required signature.

Template.htm contains:

Hello All,

Please find the following information.

TASK: {{mytask}}

RELATED TASK:{{myRelatedTasks}}

FILE : {{myFile}}

PERSON : {{myPerson}}

In VBA code,I am replacing all the fields.

The issue that I am facing is {{mytask}} and {{related tasks}} also should have a HTML reference. I have succeeded in adding the link to mytask .Clicking on the mytask in the mail will jump to the respective weblink.

<a href = "www.something.com&amp;id ={{taskID}}>
{{mytask}}.....<a href = "www.xxx.com&amp;id={{}}>{{myRelatedTasks}}

but having trouble in adding the same to Related tasks since it is an array.

My VBA code :

Option Explicit

Sub CreateNewMail()

 Dim olApp As Outlook.Application
 Dim m As Outlook.MailItem
 Dim sigPath As String, sigText As String
 Dim fso As Scripting.FileSystemObject
 Dim ts As Scripting.TextStream

 Dim t As String
 Dim r(5) As Variant

 t = "233444:dshfjhdjfdhjfhjdhfjdhfjd"


 r(0) = "122343:dsjdhfjhfjdh"
 r(1) = "323243:jfjfghfjhjddj"
 r(2) = "834783:gffghjkjkgjkj"

 Set olApp = New Outlook.Application
 Set m = olApp.CreateItem(olMailItem)

 sigPath = "C:\Users\Pavan-Kumar\Desktop\vbs\TestEvents.htm"

 Set fso = New Scripting.FileSystemObject
 Set ts = fso.OpenTextFile(sigPath)

 sigText = ts.ReadAll

 ts.Close

 Set fso = Nothing

 sigText = Replace(sigText, "{{mytask}}", t)
 sigText = Replace(sigText, "{{myRelatedTasks}}", Join(r, "<br>"))

 With m
   .display
   .To = "[email protected]"
   .Subject = "Test Events"
   .HTMLBody = sigText

 End With 
End Sub

And also when I am joining the related tasks , I want them to come one below another with indentation. I tried it with giving "\t" as the delimiter with no success.

My current O/P in outlook mail:

Please read Why is “Can someone help me?” not an actual question? — Mathieu Guindon
@MathieuGuindon Thanks for mentioning.I have updated the question .Please have a look. — Gopal Krishna
@GopalKrishna even though I gave you an answer, I concur with Mat that this is not a "question", but instead sounds like you are asking someone to write code for you. I have taken the middle road in my answer to give you a "plan" that you could follow. However it is not what this site is designed for. You would be much better off asking/researching each question individually. — Blackhawk
@Blackhawk Thanks for your answer. I will try to do what you recommended. Actually I wanted my related tasks on different lines.How to achieve this — Gopal Krishna

Blackhawk Blackhawk · Accepted Answer · 2018-04-24T17:47:16

Here is what I did to solve the same issue:

Write an email with exactly the format you want
In the email, use something unique for the fields, like {{recipient name}}
Save the email as HTML. This is now your template for the email body. You might keep several different templates for different situations.
In VBA, open the appropriate template file and read the whole thing into a string.
Using the VBA command Replace, fill in your fields. For example strHTMLTemplate = Replace(strHTMLTemplate, "{{recipient name}}", "Jane Doe")
Assign the final string to .htmlBody

For the RelatedTasks, it looks like you just want them to be on a single row. In that case, just make a "field" in your template, {{RelatedTasks}} and then do a replace like so strHTMLTemplate = Replace(strHTMLTemplate, "{{RelatedTasks}}", Join(RelatedTasks, ", ")).

If you want to get fancy, you can write functions that converts arrays of strings into html lists or tables

VBA Outlook Mail Body

1 Answers