Populating Email Body with Variables

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Populating Email Body with Variables

Logged in as: Guest

arrSession:exec spGetSession 2,2,60405

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Populating Email Body with Variables
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Populating Email Body with Variables - 5/19/2008 5:08:00 AM

rzarector

Posts: 2
Score: 0
Joined: 5/19/2008
Status: offline

Good Morning

Hopefully this doesnt sound to complicated I got most of what I wanna do in the script down. I am trying to write a script that takes an excel file populates the body of an email and mails it off. The catch is I want the body of the email in a text file so that it is easily edited when needed. So far I have this that works 100%. I want to move the body from the vbscript to its own text file.

Set WSHShell = WScript.CreateObject("WScript.Shell")
Set appOutl = Wscript.CreateObject("Outlook.Application")
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("c:\newstudent.xls")

Dim UserName, Advisor, UserEmail, AdvisorEmail

Do Until objExcel.Cells(intRow,1).Value = ""
Username = objExcel.Cells(intRow, 1).Value
Advisor = objExcel.Cells(intRow, 2).Value
UserEmail = objExcel.Cells(intRow, 3).Value
AdvisorEmail = objExcel.Cells(intRow, 4).Value

Set fso = CreateObject("Scripting.FileSystemObject")
Set theFile=fso.CreateTextFile ("C:\Email.htm", vbTrue)
theFile.WriteLine "<HTML>"
theFile.WriteLine "<BODY>"
theFile.WriteLine "Hi " &Username& ","
theFile.WriteLine "<br><br>"
theFile.WriteLine "This message is to confirm that your 2009 Advisor will be " &Advisor& " blah blah blah is part of the computer science department within xyz university. As a Counselee, you will be responsible for scheduling some time to work with your Counselor on future goal planning after reviewing blah blah blah."
theFile.WriteLine "</BODY>"
theFile.WriteLine "</HTML>"
theFile.Close

Set f = fso.OpenTextFile("C:\Email.htm",1)
MyHTML = f.ReadAll
f.Close

Set maiMail = appOutl.CreateItem(0)
maiMail.To = (objExcel.Cells(intRow, 2).Value)
maiMail.Cc = (objExcel.Cells(intRow, 4).Value)

maiMail.Subject = "Counselor Assignment"
maiMail.HTMLBody = MyHTML
maiMail.Display

intRow = intRow + 1
Loop
objExcel.Quit
Set appOutl = Nothing
Set maiMail = Nothing
Set recMessage = Nothing

< Message edited by rzarector -- 5/19/2008 5:33:24 AM >

Post #: 1

RE: Populating Email Body with Variables - 5/19/2008 7:11:43 AM

dm_4ever

Posts: 2664
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

Create your template HTML file and use something like this for those places you will be inserting data

�USERNAME�
�ADVISOR�

Then open the template from within your vbscript and use the Replace function to replace these place holders with your values....

P.S. � is a character ehvbs has used in other examples and works well, but you could make your place holders anything you want as long as they are unique enough to be able to replace without any adverse side effects.

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to rzarector)

Post #: 2

RE: Populating Email Body with Variables - 5/19/2008 11:13:58 PM

rzarector

Posts: 2
Score: 0
Joined: 5/19/2008
Status: offline

Got it Thanks for the help! :)

< Message edited by rzarector -- 5/20/2008 12:58:55 AM >

(in reply to dm_4ever)

Post #: 3

If you found our site useful please link to us <a href="http://www.visualbasicscript.com">VisualBasicScript.com</a>.

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Populating Email Body with Variables

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts