Solved

Need help with VBScript

Posted on 2016-09-03
6
19 Views
Last Modified: 2016-09-28
Have the following script but I'm struggling to get the file  Pcomb Error Report.
The file, for example, is saved as  Pcomb Error Report DDMMYY and is emailed the following morning.

Dim myDateString, myStart, myMessage, myDatestring2, myattachment

myDateString = DateAdd("d", -1, Date)

strDatestring1 = Replace(mydatestring,"/","")
strdatestring2 = Mid(strDatestring1,1,4)
strdatestring3 = Mid(strDatestring1,7)

strDayofMonth1 = Datepart("d",mydatestring)
strMonth1 = Datepart("m",mydatestring)
strYear1 = Datepart("yyyy",mydatestring)

strDayOfMonth = right("0" & day(date()),2) - 1
strMonth = right("0" & month(date()),2)
strYear = right (year(date()),2)


mydatestring2 = strDayOfMonth & strMonth & strYear

myattachment = "N:\Users\Production\ops\Ops Documents\PCOMB Errors\TCQ Pcomb Error Report " & strdatestring2 & strdatestring3 &".doc"
myMessage = "PCOMB Timings & Error Report " & myDateString 
Set outlookApp = CreateObject("Outlook.Application")

olMailItem = 0
Set newMessage = outlookApp.CreateItem(olMailItem)

newMessage.To = "test@hotmail.com"
newMessage.Subject = myMessage
newMessage.Attachments.Add "N:\Users\Production\ops\Ops Documents\Spreadsheets and templates\PCOMB_Timings.xls"
newMessage.Attachments.Add "N:\Users\Production\ops\Ops Documents\PCOMB Errors\Pcomb Error Report " & strdatestring2 & strdatestring3 &".doc"
newMessage.Subject = myMessage

newMessage.Display

Open in new window

0
Comment
Question by:Ferhat Er
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 55

Expert Comment

by:Bill Prew
ID: 41783313
Give this a try.

Dim datYesterday, strDDMMYY, myStart, myMessage, myattachment

datYesterday = DateAdd("d", -1, Now())
strDDMMYY = Right("0" & Day(datYesterday), 2) & Right("0" & Month(datYesterday), 2) & Right(Year(datYesterday), 2)

myattachment = "N:\Users\Production\ops\Ops Documents\PCOMB Errors\TCQ Pcomb Error Report " & strDDMMYY &".doc"
myMessage = "PCOMB Timings & Error Report " & strDDMMYY

Set outlookApp = CreateObject("Outlook.Application")

olMailItem = 0
Set newMessage = outlookApp.CreateItem(olMailItem)

newMessage.To = "test@hotmail.com"
newMessage.Subject = myMessage
newMessage.Attachments.Add "N:\Users\Production\ops\Ops Documents\Spreadsheets and templates\PCOMB_Timings.xls"
newMessage.Attachments.Add "N:\Users\Production\ops\Ops Documents\PCOMB Errors\Pcomb Error Report " & strDDMMYY &".doc"
newMessage.Subject = myMessage

newMessage.Display

Open in new window

~bp
1
 

Author Comment

by:Ferhat Er
ID: 41783563
Much appreciated Bill.
This works perfectly.
Another question. When I launch this script it opens it in outlook with my signature, but i also would like to add to the body the following:

Good Morning,

Please find attached the PCOMB Timings and Error Report for DD/MM/YY. (<- This date would be previous days date like the file attachment)

Can you assist with this as well pretty please?

Best Regards,
Ferhat
0
 
LVL 55

Accepted Solution

by:
Bill Prew earned 500 total points (awarded by participants)
ID: 41784127
Give this a try.

Dim datYesterday, strDDMMYY, myStart, myMessage, myattachment, strEmail

datYesterday = DateAdd("d", -1, Now())
strDDMMYY = Right("0" & Day(datYesterday), 2) & Right("0" & Month(datYesterday), 2) & Right(Year(datYesterday), 2)
strEmail = Right("0" & Day(datYesterday), 2) & "/" & Right("0" & Month(datYesterday), 2) & "/" & Right(Year(datYesterday), 2)

myattachment = "N:\Users\Production\ops\Ops Documents\PCOMB Errors\TCQ Pcomb Error Report " & strDDMMYY &".doc"
myMessage = "PCOMB Timings & Error Report " & strDDMMYY

Set outlookApp = CreateObject("Outlook.Application")

olMailItem = 0
Set newMessage = outlookApp.CreateItem(olMailItem)

newMessage.To = "test@hotmail.com"
newMessage.Subject = myMessage
newMessage.Attachments.Add "N:\Users\Production\ops\Ops Documents\Spreadsheets and templates\PCOMB_Timings.xls"
newMessage.Attachments.Add "N:\Users\Production\ops\Ops Documents\PCOMB Errors\Pcomb Error Report " & strDDMMYY &".doc"
newMessage.Body = "Good Morning," & vbCrLf & vbCrLf & "Please find attached the PCOMB Timings and Error Report for " & strEmail & "." & vbCrLf

newMessage.Display

Open in new window

~bp
1
 

Author Comment

by:Ferhat Er
ID: 41787008
Thank you Bill. That has worked. Much appreciated.
0
 
LVL 55

Expert Comment

by:Bill Prew
ID: 41787053
Welcome.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question