Activating a renamed workbook

[Mats Samson]

My colleagues and I exchange workbooks with order/invoice records that are
installed (copied) in to each local database. We send them via email and it’s
very convenient to just open the excel-file from Outlook and install the
records. But there is an obstacle: If we put the installation macro in the
routing workbook, we get the trouble with security when running procedures
without digital certificates.
We have now tried to move the procedure to the main, local spreadsheet but
we then face another problem. When opening workbooks with the same name
several times from Outlook, every workbook gets a serial number within
parenthesis added to the name as all of them belongs to the same temporary
directory in C:\documents and settings\....OLKE. When running the procedure
from the main workbook, the program does not know which other workbook to
activate as the name changes every time. Can anyone find a solution to
activate the (only) workbook starting with the name TradeRoute, skipping the
end of the name _(12) or _(13), etc.

 

[Joel]

The right solution would be to write a macro in Outlook that saved the
attached file to a predetermine location (Not the temporary OLKE) directory.
The macro can open the file using an Excel Application.

You could also write the macro in Excel which opens an Outlook application
but that would require opening an excel workbook with the macro.

 

[Mats Samson]

I don't believe that will work! At least not without jumping back and forth
between different programs (Outlook and Excel).
What was/is so convenient now is that when registering an order in the Excel
application/procedure a record is automatically saved to a workbook that is
passed as an email from Excel to Outlook and sent without the users
interaction.
The recipient gets the email, double clicks the Excel attachement and the
workbook opens in Excel. Previously we had a button with a procedure in this
workbook but it caused security issues as the procedure has not got any
digital certificate.
We tried instead to move the procedure to the main workbook but then we
faced the problems that I described previously.
I need to find a way of: Activate the workbook starting with the name
"TradeRoute"

 

[Joel]

I often write macros that work with multiple Office applications and there is
no problems. I have written macros that work between excel and Power Point,
Outlook, Internet Explorer, Word and Access.

There wouldn't be any security issues because the macro will be load in
Outlook not the excel file that was e-mailed. You could open the e-mail and
then run a macro which will save the attached file in the open e-mail to a
fixed location and then open the file. You could also write a second macro
that will automatically attached the file to an e-mail. You could also have
the macro so version control which will automatcially save the file under a
differ version number everytime the file is recieved and send the latest
version of the file.

 

[Mats Samson]

I could be a solution to have a procedure in Outlook that
1: saves the attachement in the current marked email at a specific location
2: overwrites any previous existence of a file with the same name
3: opens the file from this location into Excel
4: Then I can have a procedure in the main Excel application installing the
record
because it activates the workbook always having the same name!

A procedure sending the workbook from Excel as an email I have already, with
Workbooks("abc.xls").SendMail "recipient"

Can you give me a proposal for such an Outlook procedure?

 

[Joel]

I got this example from the Outlook VBA help menu "SaveAsFile Method"

Sub SaveAttachment()
Dim myOlApp As Outlook.Application
Dim myInspector As Outlook.Inspector
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Set myOlApp = CreateObject("Outlook.Application")
Set myInspector = myOlApp.ActiveInspector
If Not TypeName(myInspector) = "Nothing" Then
If TypeName(myInspector.CurrentItem) = "MailItem" Then
Set myItem = myInspector.CurrentItem
Set myAttachments = myItem.Attachments
'Prompt the user for confirmation
Dim strPrompt As String
strPrompt = "Are you sure you want to save the first attachment in the
current item to the C:\ folder? If a file with the same name already exists
in the destination folder, it will be overwritten with this copy of the file."
If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then
myAttachments.Item(1).SaveAsFile "C:\" & _
myAttachments.Item(1).DisplayName
End If
Else
MsgBox "The item is of the wrong type."
End If
End If
End Sub

If you use Microsoft Visual Basic Scripting Edition (VBScript) in a
Microsoft Outlook form, you do not create the Application object. This
example shows how to use VBScript code to save the first attachment in the
current item.

Sub CommandButton1_Click()
If TypeName(Item) = "MailItem" Then
Set myAttachments = Item.attachments
'Prompt the user for confirmation
Dim strPrompt
strPrompt = "Are you sure you want to save the first attachment to the
C:\ folder? If a file with the same name already exists in the destination
folder, it will be overwritten with this copy of the file."
If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then
myAttachments.Item(1).SaveAsFile "C:\" & _
myAttachments.Item(1).DisplayName
End If
Else
MsgBox "The item is of the wrong type."
End If
End Sub

 

[Mats Samson]

Joel,
I can't get this to work.
It stops already when identifying the TypeName. Saying it's of the wrong type!
It's a standard email so I have no idea what's wrong.
Well, my programming experience in Outlook is very poor so please help me out.
Secondly, how do you attach a procedure to a button you insert in the toolbar?
Cheers
Mats

 

[Joel]

The code is working in Office 2003 inside Outlook VBA. You need to have the
e-mail opened with an attachment in the e-mail.

To Add a button after you write a macro do the following

On the right side of Tool Bars there are black down arrows. Press the down
button and a pop up will sya Add or Remove Buttons. Move the mouse to the
left arrow Select customize. The go to tab Commands and select Macro. You
macro should appear under Macro. Take the ICON with your macro name and
slide it to one of the tool bars and the top of the outlokk window.

 

[Mats Samson]

Thank you Joel, it worked fine!!
The "Customize" section is available directly when right-clicking in the
toolbar area!
2 additional questions: Is it possible to do this operation without opening
the email, just having it marked, and secondly, can the Excel-file be opened
as the last action in the Outlook procedure?
Best regards
Mats

 

[Joel]

I'm not an Outlook VBA expert but finally got it to work. I had problems
because I need to add Item(1) as the selected item. I also included a 2nd
version of the code to open and save all selected items.

Sub SaveAttachment()
Dim myOlApp As Outlook.Application
Dim myInspector As Outlook.Inspector
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Set myOlApp = CreateObject("Outlook.Application")
Set myOlExp = myOlApp.ActiveExplorer
Set myOlSel = myOlExp.Selection

Set myAttachments = myOlSel.Item(1).Attachments

'Prompt the user for confirmation
Dim strPrompt As String
strPrompt = "Are you sure you want to save the first attachment in the "
& _
"current item to the C:\ folder?" & _
"If a file with the same name already exists in the destination
folder," & _
"it will be overwritten with this copy of the file."
If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then
myAttachments.Item(1).SaveAsFile "C:\" & _
myAttachments.Item(1).DisplayName
End If

Set myInspector = myOlSel.Item(1).GetInspector
myInspector.Display

End Sub


2nd method

Sub SaveAttachment2()
Dim myOlApp As Outlook.Application
Dim myInspector As Outlook.Inspector
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Set myOlApp = CreateObject("Outlook.Application")
Set myOlExp = myOlApp.ActiveExplorer
Set myOlSel = myOlExp.Selection

For Each itm In myOlSel

Set myAttachments = itm.Attachments

'Prompt the user for confirmation
Dim strPrompt As String
strPrompt = "Are you sure you want to save the first attachment in
the " & _
"current item to the C:\ folder?" & _
"If a file with the same name already exists in the destination
folder," & _
"it will be overwritten with this copy of the file."
If MsgBox(strPrompt, vbYesNo + vbQuestion) = vbYes Then
myAttachments.Item(1).SaveAsFile "C:\" & _
myAttachments.Item(1).DisplayName
End If
Set myInspector = itm.GetInspector
myInspector.Display
Next itm
End Sub

 

[Mats Samson]

Thank you, Joel,
this solved the whole issue.
Thanks for good support!
Mats