writing to excel from outlook

Status
Not open for further replies.
P

pete the greek

hi im trying to us excel to log whick folders i access within my email and
> am
> struggling with the automation between outloook and excel

> i appear to be able to do this by creating a new excel object and then
> opening the excel file writing the info and then closing it again see code
> below.

> what i would like to do is leave the escel file open as i assume this
> would
> be quicker but if i do this creatobject opens the file as read only in the
> background

> i think i need to use getobject to pick up the open file but cant get it
> to
> work any pointers

> code:

> Sub log()

> Set MyApp = CreateObject("excel.application")

> Set objNS = Application.GetNamespace("MAPI")
> Set thisfolder = Application.ActiveExplorer.CurrentFolder

> MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
> Documents\test.xls", ReadOnly:="false"
> MyApp.workbooks("test.xls").worksheets(1).Range("A1").Select
> MyApp.Selection.entirerow.Insert
> MyApp.workbooks("test.xls").worksheets(1).Range("A1").Value =
> thisfolder.Name

> MyApp.workbooks("test.xls").Close (True)

> End Sub
 
Before calling CreateObject check if GetObject returns an object. If it

does, see whether the file is already in the Workbooks collection before

calling Workbooks.Open.

Best regards

Michael Bauer

Am Sun, 3 Jan 2010 08:03:01 -0800 schrieb pete the greek:


> hi im trying to us excel to log whick folders i access within my email

and
> > am
> > struggling with the automation between outloook and excel
>

>> i appear to be able to do this by creating a new excel object and then
> > opening the excel file writing the info and then closing it again see

code
> > below.
>

>> what i would like to do is leave the escel file open as i assume this
> > would
> > be quicker but if i do this creatobject opens the file as read only in

the
> > background
>

>> i think i need to use getobject to pick up the open file but cant get it
> > to
> > work any pointers
>

>> code:
>

>> Sub log()
>

>> Set MyApp = CreateObject("excel.application")
>

>
>
>> Set objNS = Application.GetNamespace("MAPI")
> > Set thisfolder = Application.ActiveExplorer.CurrentFolder
>

>> MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
> > Documents\test.xls", ReadOnly:="false"
> > MyApp.workbooks("test.xls").worksheets(1).Range("A1").Select
> > MyApp.Selection.entirerow.Insert
> > MyApp.workbooks("test.xls").worksheets(1).Range("A1").Value =
> > thisfolder.Name
>

>
>
>> MyApp.workbooks("test.xls").Close (True)
>

>
>
>> End Sub
 
thanks Michael

getting the info into the spreadsheet now

the code runs great from outlook and is much quicker with the spreadsheet open

one thing i would like but cant get is to bring the excel spreadsheet to the

front after inseting the data as there is a field i need to manual update.

i have tried all the variations i can think of on MyApp and MyApp.workbook

ie select,show,visible.activate but cant get the spreadsheet to the front

regards

pete

"Michael Bauer " wrote:



> Before calling CreateObject check if GetObject returns an object. If it
> does, see whether the file is already in the Workbooks collection before
> calling Workbooks.Open.

> > Best regards
> Michael Bauer
>

>

> Am Sun, 3 Jan 2010 08:03:01 -0800 schrieb pete the greek:
>
> > hi im trying to us excel to log whick folders i access within my email
> and
> >> am
> >> struggling with the automation between outloook and excel
> >
> >> i appear to be able to do this by creating a new excel object and then
> >> opening the excel file writing the info and then closing it again see
> code
> >> below.
> >
> >> what i would like to do is leave the escel file open as i assume this
> >> would
> >> be quicker but if i do this creatobject opens the file as read only in
> the
> >> background
> >
> >> i think i need to use getobject to pick up the open file but cant get it
> >> to
> >> work any pointers
> >
> >> code:
> >
> >> Sub log()
> >
> >> Set MyApp = CreateObject("excel.application")
> >
> >
> >
> >> Set objNS = Application.GetNamespace("MAPI")
> >> Set thisfolder = Application.ActiveExplorer.CurrentFolder
> >
> >> MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
> >> Documents\test.xls", ReadOnly:="false"
> >> MyApp.workbooks("test.xls").worksheets(1).Range("A1").Select
> >> MyApp.Selection.entirerow.Insert
> >> MyApp.workbooks("test.xls").worksheets(1).Range("A1").Value =
> >> thisfolder.Name
> >
> >
> >
> >> MyApp.workbooks("test.xls").Close (True)
> >
> >
> >
> >> End Sub
> .
>
 
That's how it works as Excel is another process, and the last line of code

executed is always in Outlook, which brings Outlook up to the front. You

could minimize the current Outlook window, or use some Win32 API functions

in order to find the Excel window.

Best regards

Michael Bauer

Am Mon, 4 Jan 2010 07:51:01 -0800 schrieb pete the greek:


> thanks Michael

> getting the info into the spreadsheet now

> the code runs great from outlook and is much quicker with the spreadsheet

open

> one thing i would like but cant get is to bring the excel spreadsheet to

the
> front after inseting the data as there is a field i need to manual update.

> i have tried all the variations i can think of on MyApp and MyApp.workbook
> ie select,show,visible.activate but cant get the spreadsheet to the front

> regards

> pete

> "Michael Bauer " wrote:
>
>

>
>> Before calling CreateObject check if GetObject returns an object. If it
> > does, see whether the file is already in the Workbooks collection before
> > calling Workbooks.Open.
>

>> > > Best regards
> > Michael Bauer
> >

> >

>
>
>> Am Sun, 3 Jan 2010 08:03:01 -0800 schrieb pete the greek:
> >
> >> hi im trying to us excel to log whick folders i access within my email
> > and
> >>> am
> >>> struggling with the automation between outloook and excel
> >>
>>>> i appear to be able to do this by creating a new excel object and then
> >>> opening the excel file writing the info and then closing it again see
> > code
> >>> below.
> >>
>>>> what i would like to do is leave the escel file open as i assume this
> >>> would
> >>> be quicker but if i do this creatobject opens the file as read only in
> > the
> >>> background
> >>
>>>> i think i need to use getobject to pick up the open file but cant get

it
> >>> to
> >>> work any pointers
> >>
>>>> code:
> >>
>>>> Sub log()
> >>
>>>> Set MyApp = CreateObject("excel.application")
> >>
>>>
>>>
>>>> Set objNS = Application.GetNamespace("MAPI")
> >>> Set thisfolder = Application.ActiveExplorer.CurrentFolder
> >>
>>>> MyApp.workbooks.Open "C:\Documents and Settings\Peter\My
> >>> Documents\test.xls", ReadOnly:="false"
> >>> MyApp.workbooks("test.xls").worksheets(1).Range("A1").Select
> >>> MyApp.Selection.entirerow.Insert
> >>> MyApp.workbooks("test.xls").worksheets(1).Range("A1").Value =
> >>> thisfolder.Name
> >>
>>>
>>>
>>>> MyApp.workbooks("test.xls").Close (True)
> >>
>>>
>>>
>>>> End Sub
> > .
> >
 
Status
Not open for further replies.
< Outlook-Exchange bug??? | change mail format >
Similar threads
Thread starter Title Forum Replies Date
S Outlook 365 Can I change the possible range of highlighting colours when writing an Outlook email? Using Outlook 1
R writing "Instant Search" queries to find User-Defined fields Using Outlook 0
D Writing to BCM Database from VBA BCM (Business Contact Manager) 0
L Help for writing an Outlook 2007 macro Outlook VBA and Custom Forms 7
H Better way of writing Macro? Identify multiple types of attachmen Outlook VBA and Custom Forms 5
L Error when exporting Sent Mail to Excel Outlook VBA and Custom Forms 6
P OT: Need website like this one, but for Excel Using Outlook 0
kburrows Outlook Email Body Text Disappears/Overlaps, Folders Switch Around when You Hover, Excel Opens Randomly and Runs in the Background - Profile Corrupt? Using Outlook 0
M using excel to sort outlook appointment items Outlook VBA and Custom Forms 4
W Outlook 365 I am getting the "Either there is no default mail client" error when I try to send an email on excel Office 365 Using Outlook 1
D ISOmacro to extract active mail senders name and email, CC, Subject line, and filename of attachments and import them into premade excel spread sheet Outlook VBA and Custom Forms 2
F VBA to move email from Non Default folder to Sub folders as per details given in excel file Outlook VBA and Custom Forms 11
D Cannot populate certain UserProperties in Outlook from Excel Outlook VBA and Custom Forms 2
F Excel VBA to move mails for outlook 365 on secondary mail account Outlook VBA and Custom Forms 1
B vBA for exporting excel file from outlook 2016 Outlook VBA and Custom Forms 3
S Excel vba code to manage outlook web app Using Outlook 10
H Information from user defined field into Excel Outlook VBA and Custom Forms 7
S Excel VBA and shared calendar issue Outlook VBA and Custom Forms 3
N Extract Outlook emails to excel Outlook VBA and Custom Forms 2
M Extract all links from Outlook email, send to Excel Using Outlook 2
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
C Pull Outlook shared calendars items from Excel Outlook VBA and Custom Forms 4
S How to export urls from email to excel and check the status of the url ? Using Outlook 5
S save email from excel Outlook VBA and Custom Forms 1
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
C Trying to populate an appointment ComboBox from Excel Outlook VBA and Custom Forms 2
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
E Accessing shared outlook folder doesn't work since switch to new outlook/excel Outlook VBA and Custom Forms 11
E Copying the whole e-mail body into excel Outlook VBA and Custom Forms 0
E Copy e-mail body from outlook and insert into excel Outlook VBA and Custom Forms 3
E Copying data from e-mail attachement to EXCEL file via macro Outlook VBA and Custom Forms 38
S Find a cell value in excel using outlook vba Using Outlook 1
K Update Appointment category when changed in Excel Using Outlook 3
A Script to fetch data from mails in restricted collection and sending them to excel Using Outlook 1
D Paste Excel table into Outlook message Outlook VBA and Custom Forms 6
J Outlook 2013 Extract Flag Completed dates to Excel Macro Outlook VBA and Custom Forms 16
D Retrieving Tables from outlook to excel Using Outlook 14
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
nathandavies Email Details to Excel & Save as .MSG on one macro - combination of 2 macros Outlook VBA and Custom Forms 3
N Export details to a excel spreadsheet using macros Using Outlook 0
P How to copy and append data from Outlook 2016 message into Excel 2016 workbook Using Outlook 0
T Outlook Calendar 2016 import Excel Using Outlook 1
D Macro sending outlook template from Excel list Outlook VBA and Custom Forms 6
N Lookup Value From Excel and Reply With Matching Value Using Outlook 0
D send email from Excel using outlook template Outlook VBA and Custom Forms 3
Diane Poremsky Importing Lists from Excel to Outlook Using Outlook 0
N Paste content to Excel when .txt file (attachment) is opened Outlook VBA and Custom Forms 1
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
M Creating Outlook Appointments from Excel Cells Outlook VBA and Custom Forms 1
M Linking Excel Cell to Shared Contacts Outlook VBA and Custom Forms 7

Similar threads

Back
Top