Solved

Need help with VBScript

Posted on 2016-09-03
6
16 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
  • 3
  • 2
6 Comments
 
LVL 53

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 53

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 53

Expert Comment

by:Bill Prew
ID: 41787053
Welcome.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 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