Copy email to excel runtime error 5020

Status
Not open for further replies.

CindyT

Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server
Hi there,
I pulled code added by Diane Poremsky from slipstick systems and made what I thought were the necessary changes(still learning vba) to copy an email body to excel. I get the runtime 5020 Application-defined or object-defined error. The code runs through opens the excel file and errors at 'If Reg1.Test(sText) Then'. It does seem to detect the email body text, but not sure where to go from here. If I need to add the folder object and/or what my issue is? Any help is greatly appreciated. Best, Cindy
Here is the code:


[ Sub CopyToExcel()
Dim olItem As Outlook.MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim vText As Variant
Dim sText As String
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String
Dim Reg1 As Object
Dim M1 As Object
Dim M As Object



Set olItem = Application.ActiveExplorer().Selection(1)
enviro = CStr(Environ("USERPROFILE"))

strPath = "P:\Operations\Votes Log\TESTSchedule.xlsm"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0

Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Testing")


rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(xlUp).Row
rCount = rCount + 1

sText = olItem.Body

Set Reg1 = CreateObject("VBScript.RegExp")


With Reg1
.Pattern = "((Vote With\s*)\w*\)\s*(\w*)\s*(\w*)\s*"
End With
If Reg1.Test(sText) Then


Set M1 = Reg1.Execute(sText)
For Each M In M1
vText = Trim(M.SubMatches(1))



Next
End If

xlSheet.Range("B" & rCount) = vText



xlWB.Close 1
If bXStarted Then
xlApp.Quit
End If
Set M = Nothing
Set M1 = Nothing
Set Reg1 = Nothing
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub]
 

CindyT

Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server
Did you set a reference to the Regular Expressions library?

If this is what you mean, yes. Does it need a higher priority? Sorry, I am not skilled enough in vba figure it out.
upload_2016-4-29_14-45-18.png
 

Attachments

  • upload_2016-4-29_14-43-48.png
    upload_2016-4-29_14-43-48.png
    35.6 KB · Views: 431

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Yes, that is what I meant. No, i don't think it needs to be higher priority. I'll test it later (I have a meeting starting soon.)
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
It looks like the problem is with the pattern - you have an extra \
.Pattern = "((Vote With\s*)\w*\)\s*(\w*)\s*(\w*)\s*"
 

CindyT

Member
Outlook version
Outlook 2013 32 bit
Email Account
Exchange Server
Thanks for your reply. It is definitely the pattern and I made your change and no more error, but nothing populated. I was hoping you could help me with the pattern matching. I want to put the entire line into one cell and pull all the emails from my folder. The emails are always going to begin "((Vote With\s*)) but there are other words after that string. I pulled up Use a macro to copy data in Outlook email to Excel workbook but can't get the pattern I entered to work or all the messages from the folder. I think I need to change the pattern to something like .Pattern = "((Vote With\s*[\w*-\s*]*))" and to add a Dim FolderItem As Object. Definitely willing to research more or look at more solved cases but I definitely (greatly) appreciate your replies on this.
.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook New Slipstick.com Articles 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook New Slipstick.com Articles 0
L Copy email body fields to excel Using Outlook 0
C Copy Cell value from Excel and paste into current email Outlook VBA and Custom Forms 10
A Copying cc email addresses to Excel - does not copy the <joe.bloggs@isp.com> Using Outlook 1
B VBA to convert email to task, insert text of email in task notes, and attach copy of original email Outlook VBA and Custom Forms 4
R Sending email copy (*.msg file) of sent email if subject line contains specific string. Outlook VBA and Custom Forms 1
K ind specific Subject line from outlook and copy the content of the email body to exce Outlook VBA and Custom Forms 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
G VBA Copy draft email to a new email - attachments not copided Using Outlook 7
I Copy email from folder to folder - FAILS Using Outlook 5
Q Why can't I copy image with embedded hyperlink from email to Word Using Outlook 0
D How to copy yahoo email folders to hard drive Using Outlook 2
J Copy email from preview pane with headers Outlook VBA and Custom Forms 4
Mary B VBscript: Need to copy every email to a folder & mark that copy as read Outlook VBA and Custom Forms 5
Mary B Outlook 2013: Rule for copying new email to folder & marking that copy as read Using Outlook 1
S VBA to identify a specific email and copy the attachment from it to the network Outlook VBA and Custom Forms 4
L Outlook 2007 Copy Email Address in To Field Using Outlook 11
Aussie Looking for Outlook macro to Copy Recipient Names into Email Body Outlook VBA and Custom Forms 3
A Macro to copy email body to new email Outlook VBA and Custom Forms 5
P Copy email address from outlook 2013/2010 Using Outlook 1
M Copy "To" Address Value from a Newly Created Email to the "To" Address Field of Custom Form Using Outlook 4
O Copy email content and paste into new Word Document using a different font Using Outlook 1
R Macro to copy email address Using Outlook 6
F Need to copy an email folder to another computer. How can i do this? Outlook 2010 Using Outlook 2
A Copy email from external server, rather than totall download it Using Outlook 1
I Email NOT deleted on server - even with leave copy UNchecked Using Outlook 1
B Would like to copy a logo in my signature of my email in outlook Using Outlook 1
T Receipt of copy of every email sent out. Using Outlook 1
R how to copy a list of email contacts and paste them only as names (not names + email address) Using Outlook 12
B Mark copy of sent email as read - VBA? Outlook VBA and Custom Forms 3
R Sending a copy of an incoming email to a network folder Outlook VBA and Custom Forms 1
K Copy Entire Email Content - Paste into new Task Outlook VBA and Custom Forms 2
C Copy email to different folder, works in 2003 but not 2007 Outlook VBA and Custom Forms 3
S Using copy paste to grab email addresses from the TO: address fiel Using Outlook 14
A Cannot copy this folder because it may contain private items Using Outlook 0
C Copy Move item won't work Outlook VBA and Custom Forms 2
Commodore Move turns into "copy" Using Outlook 3
C Copy Outlook contact field value to another field Outlook VBA and Custom Forms 1
J Copy to calendar function no longer working in outlook 365 Using Outlook 5
F Copy and replace not update contact in another pst Using Outlook 0
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
Commodore Folders always closed in move/copy items dialog box Using Outlook 3
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
geofferyh Outlook 2010 How to Copy Outlook Attachment to a Specific Folder? Outlook VBA and Custom Forms 3
S Custom Form, copy user field data to message body Outlook VBA and Custom Forms 12
R Copy Outlook Public Folders to a File Server Shared Folder Using Outlook 0
K Outlook Rules: Move a Copy Using Outlook 4
oliv- HOW TO COPY /USE FOLDERS ICONS Outlook VBA and Custom Forms 2

Similar threads

Top