Need help to copy excel data into outlook body using VBScript. Below is the all code that am using . but it is sending only blank email. excel data is

[souravbansal]

dim aOutlook
dim aEmail
dim strRecipients
dim automail_sub
dim psg_level2
dim names_copy
dim add_body_txt
dim signature
dim i
dim rowcount
dim path
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
path = WScript.ScriptFullName
path = Left(path, InstrRev(path, "\"))

'Open Workbook having Mail Parameter
xlApp.Workbooks.Open (path + "mail_param1.xls")
'set Value for the variables
i= 2
Set fs = Createobject("Scripting.FileSystemObject")
Do until xlApp.Range("A" & i).value = ""
automail_sub = xlApp.Range("A" & i).value
filename = xlApp.Range("B"& i).value
strRecipients = xlApp.Range("C" & i).value
PSG_level2 = xlApp.Range("D" & i).value
add_body_txt = xlApp.Range("E" & i).value
signature = xlApp.Range("F" & i).value


send_mail strRecipients,automail_sub,filename,psg_level2,add_body_txt,signature


i= i+1
Loop
xlApp.ActiveWorkbook.save
xlApp.ActiveWorkbook.Close
xlApp.Quit
'Clear the object variable.
Set xlApp = Nothing

sub send_mail(strRecipients,automail_sub,filename,psg_level2,add_body_txt,signature)
Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)
'set Importance
aEmail.Importance = 1
If (filename = "Book2.xlsx") then
HC_body_ms = Emailbody_pos(filename,signature)
aEmail.HTMLBody = HC_body_ms
aEmail.HTMLBody = aEmail.HTMLBody & signature
mail_subject = automail_sub

' ELSE


' mail_subject = "Level 1 : " + automail_sub

' 'Set Body for mail
' aEmail.Body = "Dear Team," + Chr(13) + "Kindly clear attached cases having " ' +automail_sub + Chr(13) + "" + Chr(13) + add_body_txt + Chr(13) + Chr(13) + signature + Chr(13) + Chr(13)

' filename_comp = path + "Book2.xlsx"

'Attach file
' aEmail.attachments.Add ("D:\background_reports\balu\Book2.xlsx")

END IF
'Set Subject
aEmail.Subject = "test mail"
'Set Recipient
aEmail.To = "test@abcxsxdscd.com"
aEmail.cc = PSG_level2
'Send
aEmail.Send
'aEmail.Displayd
End sub
'------------------------Function for Epos health check Circle wise
Function Emailbody_pos(filename,signature)
Set HC_app = CreateObject("Excel.Application")
HC_app.Visible = False
HC_app.Workbooks.Open (path + "Book2.xlsx")
Emailbody = ""
Emailbody = Emailbody + ""
Emailbody = Emailbody + ""
Emailbody = Emailbody + ""
Emailbody = Emailbody + ""
Emailbody = Emailbody + "HEALTH CHECK : COUNT OF EPOS TASKS IN ERROR"
Emailbody = Emailbody + "

"
Emailbody = Emailbody + "

CIRCLE	LT_1_Day	1-3 Days	3-5 Days	5-7 Days	GT_7 Days	TASK TOTAL
" Emailbody = Emailbody + pos_task_type Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_L1_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_L3_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_L5_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_L7_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_G7_count Emailbody = Emailbody + "	" Emailbody = Emailbody & Task_total_count Emailbody = Emailbody + "
TOTAL COUNT	" Emailbody = Emailbody & pos_L1_total_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_L3_total_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_L5_total_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_L7_total_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_G7_total_count Emailbody = Emailbody + "	" Emailbody = Emailbody & pos_Total_count Emailbody = Emailbody + "

<TBODY>
</TBODY>

"
Emailbody = Emailbody + "

"
Emailbody = Emailbody + "Regards"
Emailbody = Emailbody + "
"
Emailbody = Emailbody + "TEAM-IT"
Emailbody = Emailbody + ""
Emailbody = Emailbody + ""
Emailbody_pos = Emailbody
HC_app.ActiveWorkbook.Close
HC_app.Quit
'Clear the object variable.
Set HC_app = Nothing
End Function

 

[hippiehacker]

check ron de bruins website there are so many examples

Mail from Excel with Outlook (VBA)

 

[souravbansal]

thanks hippiehacker. i tried the code from ronthe bruins..but its not executing in my vbs file. what am doing is -- am only copying the code from ron the bruins and pasting into test.vbs file and trying to execute it. but same is not working. need your help to run this file, please correct me where am wrong.

 

[hippiehacker]

thanks hippiehacker. i tried the code from ronthe bruins..but its not executing in my vbs file. what am doing is -- am only copying the code from ron the bruins and pasting into test.vbs file and trying to execute it. but same is not working. need your help to run this file, please correct me where am wrong.

you know its a vba forum not a vbs all the codes on rons website are for excel to run as vba.