Excel to Outlook VBScript

[damienenglish]

Morning All

I have a spreadsheet that contains multiple sets of customer data on the one worksheet. This data is emailed to the customer each week.

I have create a VBScript in a text document, which searches the spreadsheet for the customer data, and then exports the data into a new email window in Outlook. The problem I am having, is that the selected code I am copying is not populating into the Outlook window.

Here is what I have so far:
Dim rng
Dim OutApp
Dim OutMail
Dim MailItem
Set OutlookApp = CreateObject("Outlook.Application")
Set MailItem = OutlookApp.CreateItem(oMailItem)
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Users\damien.english\desktop\CLW.xls")

IntStart = 2
IntRow = 2

Do until introw=500
IntRow = IntRow + 1
var2 = introw
FindRange
CopyRange
'SendMail
Loop



Sub FindRange
Do Until objExcel.Cells(IntRow, 2).Value = "***SEND"
IntRow = IntRow + 1
Loop
wscript.echo rng
IntStart = introw
Wscript.echo "Start : " & var2
wscript.echo "End : " & IntStart - 1


End Sub

Sub CopyRange



On Error Resume Next
objXl.Range("B" & var2, "M" & IntStart - 1).Select
objXl.Range("B" & var2, "M" & IntStart - 1).Copy

On Error GoTo 0

Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0) 'olMailItem

With MailItem
.To = "jdoe@anywhere.com"
.Subject = "Contracted Weekly Prices"
.Body = objXl
.Display
End With

End Sub

Any help would be very much appreciated!

Many Thanks

 

[markdmac]

I think this reference will help you:

http://www.rondebruin.nl/mail/folder1/mail4.htm

I hope that helps.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.