Using Outlook to activate opened xl Sheet

[cburdick]

I have what are most likely are very easy questions, however I can’t find anything to adapt to my purpose. I have a user form in Outlook that collects some data from the currently opened email.

Code for that:

[vba]Private Sub btnCollect_Click()
Dim myOlApp As Outlook.Application
Dim myItem As Outlook.MailItem
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector.CurrentItem
txtRequest.Text = myItem.SenderName
txtTime.Text = myItem.ReceivedTime
Set myOlApp = Nothing
Set myItem = Nothing
End Sub[/vba]

Question 1: How can I get outlook to do a vlookup on a certain excel sheet (I:\Directories\Employee Directory.xls) looking up the value of txtRequest in column A and giving me back column E into text field called txtBranch.
I don’t care if the file actually opens (it's read only), I just need the data.

Question 2: I have another button btnUpdate. I need it to update a spreadsheet called “Scan on Demand Merge.xls”. I have seen how to open a sheet and update it, but on this particular one, the spread sheet will already be open. I don’t want it to try and open it, just to activate the sheet and place each field from my user form into excel. The data for example will be txtRequest will go into the next available row in column D. And another issue with that part is that I wont be starting from row one each time. It just needs to append to the end.

This would be so much easier if I could write this part exactly like I write things in excel, that at least makes sense. I am completely confused with the syntax in using outlook for excel.

Any help would be wonderful.
Thanks so much,
Christina

[Ken Puls]

Hi Christina, and welcome to VBAX!

You look like you're somewhat comfortable with VBA, so I'm going to point you to an article on my site about binding to other applications. That article can be found here. The focus of that article is how to bind to an existing instance of an application, or create a new one if one does not already exist. It will hopefully get you started.

What I would do is:
-Bind to (or create if necessary) an instance of excel
-Open your workbook
-Leverage your vlookup
-Close the workbook (an possibly Excel if you don't need it)

For btnUpdate, as similar process
-Bind to (or create if necessary) an instance of Excel
-Check if the workbook is open and open it if necessary
-Update your worksheet and save it.

If you need any help with further coding, please don't hesitate to post back.

[cburdick]

I’m sorry, I'm afraid I’m still lost, I tried imputing Set ojbApp = GetObject(,”Excel.application) and got an error saying “Object does not support this property or method” and when I tried Set ojbApp = GetObject(“Scan on Demand Merge.xls” , ”Excel.application) I got “File name or class name not found during automation operation.”

This is what I have for the update event:

[vba]Private Sub btnUpdate_Click()
Dim myOlApp As Outlook.Application
Dim myItem As Outlook.MailItem
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector.CurrentItem
Set objApp = GetObject(“Scan on Demand Merge.xls” , "Excel.Application")
With objApp.ActiveWorkbook
.Worksheets(1).Range ("A65000")
.Worksheets(1).Selection.End(xlUp).Select
.Worksheets(1).Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
.Worksheets(1).ActiveCell = txtFile.Text
End With
myItem.FlagIcon = olYellowFlagIcon
myItem.Save
myItem.Close olSave
txtRequest.Text = ""
txtBranch.Text = ""
txtTime.Text = ""
txtFile.Text = ""
UserForm2.hide
Set myOlApp = Nothing
Set myItem = Nothing
End Sub[/vba]

I know its going to be something extremely simple but my head is swimming =(

[Ken Puls]

Hi there,

First, just to let you know that I edited your posts above to use VBA tags. I does look like there's a bit of an issue there, as they are splitting your first lines. I've reported that, so hopefully our board coder can fix that.

Now, I don't have outlook at work, so I can't test this completely, but I think it should do the trick (providing that the rest of your code was functional.)

[vba]Sub test()
Dim myOlApp As Outlook.Application
Dim myItem As Outlook.MailItem
Dim xlApp As Object

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.ActiveInspector.CurrentItem
'Bind to an existing or created instance of Microsoft Excel

'Attempt to bind to an open instance
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
'Could not get instance, so create a new one
Err.Clear
On Error GoTo ErrHandler
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Open ("C:\Scan on Demand Merge.xls")
End With
Else
'Bound to instance, activate error handling
On Error GoTo ErrHandler
If Not wbOpen("Scan on Demand Merge.xls", xlApp) Then
xlApp.Workbooks.Open ("C:\Scan on Demand Merge.xls")
End If
End If

'Do whatever to your worksheet
With xlApp
.Worksheets(1).Range ("A65000")
.Worksheets(1).Selection.End(xlUp).Select
.Worksheets(1).Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
.Worksheets(1).ActiveCell = txtFile.Text
End With
myItem.FlagIcon = olYellowFlagIcon
myItem.Save
myItem.Close olSave
txtRequest.Text = ""
txtBranch.Text = ""
txtTime.Text = ""
txtFile.Text = ""
UserForm2.hide

ErrHandler:
'Release the object and resume normal error handling
Set xlApp = Nothing
Set myOlApp = Nothing
Set myItem = Nothing
On Error GoTo 0
End Sub

Function wbOpen(wbName As String, xlApp As Application) As Boolean
Dim wb As Workbook
wbOpen = False
For Each wb In xlApp.Workbooks
If wb.Name = wbName Then
wbOpen = True
Exit Function
End If
Next wb
End Function[/vba]

Both the routine (you can rename it), and the function (don't rename that one) can go in your outlook file.

HTH,

[Zack Barresse]

Untested, but I wonder if you could change this code ..

[vba]Function wbOpen(wbName As String, xlApp As Application) As Boolean
Dim wb As Workbook
wbOpen = False
For Each wb In xlApp.Workbooks
If wb.Name = wbName Then
wbOpen = True
Exit Function
End If
Next wb
End Function[/vba]

.. to this ..

[vba]
Function wbOpen(wbName As String, xlApp As Application) As Boolean
On error resume next
wbOpen = Len(xlApp.Workbooks(wbName).Name)
End Function
[/vba]

[Ken Puls]

Hey Zack,

Indeed, it works. (Of course you knew it would.)

Interesting quirk though... I had to test it for myself, so created a new workbook and tested the code in the immediate window. As habit, I typed:

[vba]? wbopen("Book1.xls",application)[/vba]

It returned False. I neglected to realize that Book1 does not actually become Book1.xls until you save it.

Just thought I'd share.

[Zack Barresse]

Ha! That's funny, I just mentioned that. LOL!

[Ken Puls]

LOL!