Outlook Macro to Update Sharepoint Excel File

Status
Not open for further replies.

Jeffclanders

Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Hi All,

I have a macros listed under code below that takes basic data from Outlook 2010 and adds it to an excel file in Sharepoint. It sends the data to a "Raw data" tab where I can then use that data to fill in other workbooks. This was intended to take the same data from multiple areas for people located across the U.S. and then send it to their own Sharepoint Workbooks where I would then send that data to a master file for senior managers to look at the same data for all areas at once. I originally tried to use a userform listbox to do this, by having the user for each area select their respective area to decide which workbook the data would be extracted to. This became difficult because choosing a wrong area by mistake filled the data in the wrong workbook. The cojnstraints that I have on this is that this must be a push button macro, which I have added to the ribbon in outlook, and the only requirement for running the macro is to highlight and select the specific emails that the macro will look at. I am not able to use userid to associate with a specific area and in some cases, others may be filling oin for someone while they are on vacation. This second person would be doing the work from another area to help out. All areas have their own email alias such as mine, which begins with CNY for (Central New York), so this may be an option. I reversed the process to eliminate the need to select an area so that a user could simply push the button and the data woulkd then fill in the master file. From the master file, I could then send the specific area data to separate workbooks for users to see their own areas' information, and not others. This solved the notion of sending each areas information to separate workbooks by choosing an area, which is frought with possible errors. The code below gets the data I need to the sharepoint excel file, but it is slow. I dont necessarily need to open the excel file oin sharepoimnt, and actually, it is a master file that area usewrs will not have access to anyway. How can I make this faster or better? Any thoughts would be much appreciated:

Code:

Option Explicit
Sub CopyToExcel()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String

Dim currentExplorer As Explorer
Dim Selection As Selection
Dim olItem As Outlook.MailItem
Dim obj As Object
Dim strColA, strColB, strColC As String

'the path of the workbook
strPath = enviro & "http://share.twcable.com/sites/EntC...er/Aging Weekly Tracking Template Master.xlsx"
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
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Raw Data")
' Process the message record

On Error Resume Next
'Find the next empty line of the worksheet
rCount = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row + 1


' get the values from outlook
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection

Set olItem = obj

'collect the fields
strColA = olItem.SenderName
strColB = olItem.SenderEmailAddress
strColC = olItem.ReceivedTime

'write them in the excel sheet
xlSheet.Range("A" & rCount) = strColA
xlSheet.Range("B" & rCount) = strColB
xlSheet.Range("C" & rCount) = strColC
'Next row
rCount = rCount + 1

Next

xlWB.Close 1
If bXStarted Then
xlApp.Quit
End If

Set olItem = Nothing
Set obj = Nothing
Set currentExplorer = Nothing
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub
 
Based on my own experience, I'm guessing the slow part is accessing it in SharePoint. If the SharePoint file was synced locally, it would/should be faster. But at this point, it only works with the old sync client, which isn't all that robust, and changes may not sync up.

if you aren't using enviro to set the local user folder, you can remove it from the string - but it's not going to have an appreciable effect on the speed.
strPath = "http....

Beyond that, I don't have any ways to speed it up. Someone else might have some ideas that will help though.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
B Looking for Outlook 2013 update for Send Drafts Macro Using Outlook 4
X Custom icon (not from Office 365) for a macro in Outlook Outlook VBA and Custom Forms 1
C Outlook - Macro to block senders domain - Macro Fix Outlook VBA and Custom Forms 1
J Outlook 365 Outlook Macro to Sort emails by column "Received" to view the latest email received Outlook VBA and Custom Forms 0
M Outlook Macro to save as Email with a file name format : Date_Timestamp_Sender initial_Email subject Outlook VBA and Custom Forms 0
D Outlook 2016 Creating an outlook Macro to select and approve Outlook VBA and Custom Forms 0
S Outlook Macro for [Date][Subject] Using Outlook 1
E Outlook - Macro - send list of Tasks which are not finished Outlook VBA and Custom Forms 3
S Macro for Loop through outlook unread emails Outlook VBA and Custom Forms 2
Witzker Macro to move @domain.xx of a Spammail to Blacklist in Outlook 2019 Outlook VBA and Custom Forms 7
S Macro for other actions - Outlook 2007 Outlook VBA and Custom Forms 23
S Macro to move “Re:” & “FWD:” email recieved the shared inbox to a subfolder in outlook Outlook VBA and Custom Forms 0
S Outlook Macro to send auto acknowledge mail only to new mails received to a specific shared inbox Outlook VBA and Custom Forms 0
S Outlook Macro to move reply mail based on the key word in the subjectline Outlook VBA and Custom Forms 0
M Outlook macro to automate search and forward process Outlook VBA and Custom Forms 6
R Macro Schedule every day in Outlook Using Outlook 0
L Moving emails with similar subject and find the timings between the emails using outlook VBA macro Outlook VBA and Custom Forms 1
N How can I increase/faster outlook VBA Macro Speed ? Using Outlook 2
A Outlook macro to create search folder with mail categories as criteria Outlook VBA and Custom Forms 3
V Outlook Macro to show Flagged messages Outlook VBA and Custom Forms 2
J Help Please!!! Outlook 2016 - VBA Macro for replying with attachment in meeting invite Outlook VBA and Custom Forms 9
S Macro using .SendUsingAccount only works the first time, after starting Outlook Outlook VBA and Custom Forms 4
M Slow VBA macro in Outlook Outlook VBA and Custom Forms 5
A Forward Outlook Email by Filtering using Macro Rule Outlook VBA and Custom Forms 44
Tanja Östrand Outlook 2016 - Create Macro button to add text in Subject Outlook VBA and Custom Forms 1
D Outlook macro with today's date in subject and paste clipboard in body Outlook VBA and Custom Forms 1
C Outlook Subject Line Macro Outlook VBA and Custom Forms 0
D Macro sending outlook template from Excel list Outlook VBA and Custom Forms 6
P Macro to attach a file in a shared Outlook draft folder Outlook VBA and Custom Forms 2
R Macro to check file name with outlook address book Outlook VBA and Custom Forms 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 0
W macro to export outlook emails to excel Outlook VBA and Custom Forms 6
Patrick van Berkel Best way to share (and keep up-to-date) Macro's in Outlook 2010 Outlook VBA and Custom Forms 6
C Newbie needs help with Outlook Macro Outlook VBA and Custom Forms 3
L Outlook 2007 - Macro Re Search Using Outlook 16
L Outlook 2007 Macro to Contact From a Field Using Outlook 3
A newb outlook macro help Outlook VBA and Custom Forms 1
Diane Poremsky Macro to Bulk Import Contacts and vCards into Outlook Using Outlook 0
S Outlook 7 VBA macro for multiple filing Outlook VBA and Custom Forms 1
S Macro in an excel for outlook. Outlook VBA and Custom Forms 2
A Outlook Macro Causing Excel Error Outlook VBA and Custom Forms 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 0
S Outlook Macro Reply to a message Outlook VBA and Custom Forms 1
Diane Poremsky Macro to Bulk Import vCards into Outlook Using Outlook 0
M Outlook VBA Macro that could retrieve/display the (From, Subject, Date Received) Outlook VBA and Custom Forms 2
Philip Rose Recreating a WORD Editing Macro to use in Outlook VBA Outlook VBA and Custom Forms 4
A Macro to Copy Data from Outlook to Excel Spreadsheet Using Outlook 2
P Outlook Macro sends information to Excel Using Outlook 1
P Outlook Macro keeps running for the same messagage Using Outlook 2
J VBS Script (macro) for word to open Outlook template. Outlook VBA and Custom Forms 2

Similar threads

Back
Top