?
Solved

Need help with VBScript

Posted on 2016-09-03
6
Medium Priority
?
20 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 56

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 56

Accepted Solution

by:
Bill Prew earned 2000 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 56

Expert Comment

by:Bill Prew
ID: 41787053
Welcome.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
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 …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 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