Script to fetch data from mails in restricted collection and sending them to excel

Status
Not open for further replies.
A

AJBoink

Hi Could someone review the below given code and help me to sort out the error?
The script is meant to fetch data from mails in a particular date range using pattern matching with regex, and send those data to an excel file. But I have been getting errors while running this script. The script should run against a folder called 'arjun' and then look for mails which matches the required pattern.

Sub mailsInRestrcitedCollectionP1S3()
Dim objOutlook As Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Dim objSourceFolder As Outlook.Folder
Dim drDate, StartDate, EndDate, StarD, EndD As Date
Dim mailCount As Integer
Dim objVariant, Row, Priority, i As Variant
Dim olItem
Dim itms As Outlook.Items
Dim filteredItms As Outlook.Items
Dim N As Integer
Dim strRestrict, subjct, mailBody, strtTime, resoTime, incTickt, Category, Location As String
Dim ExcelSheet As Object
Dim regexpSubjectCheck, regexpLocation, regexpCategory, regexpdataFromBody As RegExp
Dim M, P, D As MatchCollection
'Dim Category, Location As Object
Dim oMatch, oMatches

Set objOutlook = Application
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objSourceFolder = objNameSpace.Folders("Arjun.SL@xe04.ey.com").Folders("Inbox").Folders("arjun")

Set ExcelSheet = CreateObject("Excel.Sheet")

StartDate = InputBox("the date in format m/d/yyyy", "Start Date")
EndDate = InputBox("the date in format m/d/yyyy", "End Date")

StarD = Format(StartDate, "m/d/yyyy")
EndD = Format(EndDate, "m/d/yyyy")

strRestrict = "[ReceivedTime] > '" & StarD & "' AND [ReceivedTime] < '" & EndD + 1 & "' "

Set itms = objSourceFolder.Items
Set filteredItms = itms.Restrict(strRestrict)

Set regexpSubjectCheck = New RegExp
With regexpSubjectCheck
.Pattern = "(\s*FINAL:\s*INCIDENT NOTICE:\s*OneNet\s*\(MPLS\))|(\s*FINAL:\s*INCIDENT NOTICE:\s*US WAN connectivity)|(FINAL:\s*INCIDENT NOTICE:\s*OneNet\s*\(iVPN\))|(\s*FINAL:\s*INCIDENT NOTICE:\s*WAN/MAN\s*Connectivity)|(\s*FINAL:\s*INCIDENT NOTICE:\s*VPN Connectivity)|(\s*FINAL:\s*INCIDENT NOTICE:\s*GWAN\s*II)"
.Global = True
End With

Set regexpLocation = New RegExp
With regexpLocation
.Pattern = ("-\s*\w*\,*\s*\w*")
.Global = True
End With

Set regexpCategory = New RegExp
With regexpCategory
.Pattern = "(\s*OneNet\s*\(MPLS\))|(\s*US WAN connectivity)|(\s*OneNet\s*\(iVPN\))|(\s*WAN/MAN Connectivity)|(\s*VPN Connectivity)|(\s*GWAN\s*II)|(\s*Internet VPN\s*\(I-VPN\))"
.Global = False
End With

Set regexpdataFromBody = New RegExp
With regexpdataFromBody
.Pattern = "(START TIME:\s*\d{2}-\w{3}-\d{4}\s*\d{2}:\d{2}\s*GMT)|(RESOLUTION TIME:\s*\d{2}-\w{3}-\d{4}\s*\d{2}:\d{2}\s*GMT)|(SERVICENOW TICKET: INC\d{7})"
.Global = True
End With

Row = 1

For i = filteredItms.count To 1 Step -1

Set objVariant = filteredItms.item(i)
subjct = objVariant.subject
mailBody = objVariant.Body

If regexpSubjectCheck.Test(subjct) Then
Priority = "P1S3"
End If

Set M = regexpLocation.Execute(subjct)
Location = M(0).Value


Set P = regexpCategory.Execute(subjct)
Category = P(0).Value

Set D = regexpdataFromBody.Execute(mailBody)
strtTime = D(0).Value
resoTime = D(1).Value
incTickt = D(2).Value

ExcelSheet.Application.Cells(Row, 1) = incTickt
ExcelSheet.Application.Cells(Row, 2) = oMatch
ExcelSheet.Application.Cells(Row, 3) = Category
ExcelSheet.Application.Cells(Row, 4) = Priority
ExcelSheet.Application.Cells(Row, 5) = strtTime
ExcelSheet.Application.Cells(Row, 6) = resoTime

If regexpCategory.Test(subjct) Then
Row = Row + 1
End If

Next

ExcelSheet.SaveAs "C:\Script\p1s3.xls"

Set ExcelSheet = Nothing

End Sub
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
D.Moore VBA script fail after Office 365 update Using Outlook 8
M Outlook 2013 Script Assistance - Save Opened Link with Subject Added Outlook VBA and Custom Forms 1
F Script for zip file attachment Outlook VBA and Custom Forms 1
S Change VBA script to send HTML email instead of text Outlook VBA and Custom Forms 3
Y Outlook 2013 Run A Script Outlook VBA and Custom Forms 4
Z Script to set account? Using Outlook 0
dweller Outlook 2010 Rule Ignores VBA Script Outlook VBA and Custom Forms 2
N VBA Script to Open highlighted e-mail and Edit Message Outlook VBA and Custom Forms 5
B Outlook rule run a Script doesn't work Outlook VBA and Custom Forms 1
J Calling a Public sub-routine from the script editor via VB script Outlook VBA and Custom Forms 4
K Outlook Archive to PST Files by Date Range VBA Script? Outlook VBA and Custom Forms 1
Peter H Williams Enable script containing VBA Outlook VBA and Custom Forms 12
H VB script in outlook form doesn't work anymore Outlook VBA and Custom Forms 2
B Wanting to run a script that will filter any body that has a russian link in it. Outlook VBA and Custom Forms 5
Bri the Tech Guy Registry Tweak to make "Run a Script" Action Available Outlook VBA and Custom Forms 2
V VB script code to save a specific email attachment from a given email Outlook VBA and Custom Forms 14
Bri the Tech Guy Run Script rule not running for newly arriving messages Outlook VBA and Custom Forms 25
M Subject Line Automation - Trigger Script Delayed Outlook VBA and Custom Forms 2
Q Script to create a pst file for Archiving Using Outlook 1
Vijay Error in rule- Run a script Using Outlook 1
R VBA Script Quick Parts Using Outlook 1
Vijay Run script doesn't work in outlook Using Outlook 1
Q VBA Script to move item in secondary mailbox Outlook VBA and Custom Forms 2
Diane Poremsky Run a Script Rule: Send a New Message when a Message Arrives New Slipstick.com Articles 2
F Avoid sending duplicate using Outlook script Outlook VBA and Custom Forms 2
oliv- How to Run a Script IN AN ADDIN with Outlook's Rules and Alerts Outlook VBA and Custom Forms 2
L Run a Script Rule doesn't work Using Outlook 5
N Outlook script to forward emails based on senders' address Outlook VBA and Custom Forms 2
S using script rule to save attachments on arrival Outlook 2010 Outlook VBA and Custom Forms 9
X Outlook script to run excel data Outlook VBA and Custom Forms 1
N VBA Script to Send Automatic Emails from Outlook 2010 Outlook VBA and Custom Forms 1
Davzell Change default pop3 account with script, prf or registery ? Outlook VBA and Custom Forms 0
D RUN SCRIPT WHEN OUTLOOK IS CLOSE Outlook VBA and Custom Forms 1
L Cannot run script from rule Outlook VBA and Custom Forms 7
O modify vba to run it as script rule Outlook VBA and Custom Forms 8
D Script to parse email and set a task Outlook VBA and Custom Forms 1
L Moving Message Class email via script and Rule Outlook VBA and Custom Forms 3
P How many subs can run in one outlook VBA script Using Outlook 5
H Customizing "Send to" Attachment Script Outlook VBA and Custom Forms 5
G Script in rule to send to multiple emails found in message bo Outlook VBA and Custom Forms 11
Xueying run a script in rules, first time succeed, failed afterwards Outlook VBA and Custom Forms 3
A Creating archive rule on the clients by script/ Outlook VBA and Custom Forms 3
J VBS Script (macro) for word to open Outlook template. Outlook VBA and Custom Forms 2
J Email Parsing VBA Script for Outlook - NEEDED Outlook VBA and Custom Forms 7
P Vba script including macro appears in rules but wont run Outlook VBA and Custom Forms 6
R Adding vba to script list Outlook VBA and Custom Forms 4
O Script to move emails to a folder based on various possible keywords Outlook VBA and Custom Forms 11
Jeff Rott Diane Question on "Use in a Run a Script Rule" Outlook VBA and Custom Forms 1
Stig Arvidsson Need a Script for handling moved mail from inbox Using Outlook 3
F VBA script to highlight specific words Outlook VBA and Custom Forms 1
Similar threads


















































Top