VB Code Explanation

JohnnyBravo

Does anyone know what these four lines mean

If Not TypeName(myInspector) = "Nothing" Then
If TypeName(myInspector.CurrentItem) = "Mail Item" Then
Set myItem = myInspector.CurrentItem
Set myAttachments = myItem.Attachments


Particularly the first two

RGDS
John

stevenmu

TypeName returns the type of an object say Int, String, or some custom written type, if it returns "Nothing" it means the object hasn't been created yet.

-If Not TypeName(myInspector) = "Nothing" Then
Checks that myInspector is a valid object (i.e. it has been created and not just defined)

-If TypeName(myInspector.CurrentItem) = "Mail Item" Then
Checks that the CurrentItem property of myInspector is a mail item

-Set myItem = myInspector.CurrentItem
-Set myAttachments = myItem.Attachments
Gets references/handles/pointer to the mail item and it's attachments

Basically the code is trying to access the attachments of the currently selected email. It first has to check that myInspector exists and that it currently has an email selected. myInspector would appear to be some type of object that can read through all the objects in some type of mail store and inspect them. It's presumbly part of some virus/spam/attachment filtering software ?

JohnnyBravo

You see the idea is to automatically download an attachment from within Access using a form button so it goes something like this

Email Attachment ---> Desktop---->imported into access ---> queries are run

Now i have everything done Except importing from the email the code is like this

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


However i am unsure where it specifies what mail to use and what What attachment if there are multiple attachments on a single email thus my problem with the lines

If Not TypeName(myInspector) = "Nothing" Then
If TypeName(myInspector.CurrentItem) = "MailItem" Then
Set myItem = myInspector.CurrentItem
Set myAttachments = myItem.Attachments


I have set it up with an email file called temp with an attachment called temp.xls
but i get huge errors do you think you could help

stevenmu

I'm not very familiar with the outlook or access object models so I'm not sure how much help I can be. There's a good reference for it at MSDN that's worth a look. The one time I did use it I found emails by using the Items collection in the default folder for email. In you example I think you'd have to manually select one first

Dim myOlApp As Outlook.Application
Dim myOlFolder As Outlook.Folder 'Or maybe Outlook.MAPIFolder
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments

Set myOlApp = CreateObject("Outlook.Application")
myOlFolder= myOlApp.getDefaultFolder(olFolderInbox)
myItem = myOlFolder.Items.Find [subject] = "test" 'Finds first mail with test as _ subject
Set myAttachments = myItem.Attachments
'Do your save here

'For multiple attachments you can probably do something like
For each myAttachment in myItem.Attachments
myAttachment.Save....
.......
Next


If you can post up more detailed error message I might be able to help more, I'd guess some of them were to do with not having a mail selected though




Steve

JohnnyBravo

Thanks il give that a shot

JohnnyBravo

User defined type not defined on the line


Dim myOlFolder As Outlook.Folder

stevenmu

Must be Outlook.MAPIFolder
sorry I'm trying this from memory, I don't know what I did with the code I had before

JohnnyBravo

Dim myOlApp As Outlook.Application
Dim myOlFolder As Outlook.MAPIFolder
Dim myInspector As Outlook.Inspector
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Set myOlApp = CreateObject("Outlook.Application")
myOlFolder = myOlApp.GetDefaultFolder(olFolderInbox)
Set myAttachments = myItem.Attachments
Set myInspector = myOlApp.ActiveInspector
myItem = myOlFolder.Items.Find = "temp" 'Finds first mail with test as _ subject
If Not TypeName(myInspector) = "temp" Then
If TypeName(myInspector.CurrentItem) = "temp.xls" 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:\Documents and Settings\Owner\Desktop" & _
myAttachments.Item(1).DisplayName
End If
Else
MsgBox "The item is of the wrong type."
End If
End If
End Function


The problem is here

myItem = myOlFolder.Items.Find = "temp" 'Finds first mail with test as _ subject

it tells me the temp argument is not optional

stevenmu

You need to name the property to find against

myItem = myOlFolder.Items.Find [subject] = "temp"

You can also use other the other properties, you can find them all under the MailItem object at the link above or in the visual basic help with outlook


<edit> again the syntax may be slightly off, you can probably find some definelty working examples through google, I don't have VB to hand to try it out</edit>

JohnnyBravo

I had that initially but it kept expecting and end of statement on the line


myItem = myOlFolder.Items.Find [Subject] = "temp"

stevenmu

OK, I think I found the right syntax
myItem = myOlFolder.Items.Find ("[Subject] = ""temp"" ")

If that works you'll also have to modify your code some, you're still using the Inspector which shouldn't be needed now and you're assign myItem and myAttachments in a few places. What you should be doing is assigning myItem, then use TypeName to check that it's not "Nothing", then assign myAttachments, check myAttachments is not "Nothing" and then save.

JohnnyBravo

Cheers steve il gove it a go
Never have i been so infuriated by one function

New Problem

myOlFolder = myOlApp.GetDefaultFolder(olFolderInbox)

the object claims not to support this line

JohnnyBravo

i have also tried this

myOlFolder = myOlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

Object variable or with Block Not Set

stevenmu

Ok, I had a bright idea at last and fired up access vba

This should find a mail in your inbox with "test" as the subject and display it's body as a popup just replace the code to popup the body with your code to check that the item exists, set myAttachments, check it exists and then save


Dim myOlApp As Outlook.Application
Dim myOlFolder As Outlook.MAPIFolder
Dim myInspector As Outlook.Inspector
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Dim myNameSpace As Outlook.NameSpace

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myOlFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myFoldersItems = myOlFolder.Items
Set myItem = myOlFolder.Items.Find("[subject] = ""test""")
MsgBox myItem.Body

JohnnyBravo

i have tried that code how ever it crashes on the following line

If TypeName(myInspector.CurrentItem) = "temp.xls" Then


with the error

object variable or withblock variable not set

stevenmu

The only thing I can think of is that it's not finding your inbox for some reason which means that myOlFolder is not valid
Check that TypeName(myOlFolder) is not equal to "Nothing" before that line and maybe pop up a msgbox if it is

JohnnyBravo

If Not TypeName(myInspector) = "Nothing" Then
If TypeName(myInspector.CurrentItem) = "temp" Then


If Not TypeName(myInspector) = "Nothing" Then

This line does = Nothing

How ever i took the code you previously gave me and made a function out of it
And it gives me the pop up box i am even prompted by outlook to tell me an external application is trying to get in to the mail
I am at a loss

stevenmu

No offence meant if I'm wrong but I'm guessing that you haven't really learnt VBA,VB or any other type of programming. It seems like you're trying to teach yourself by downloading examples other people have put up and bodging them to suit your needs. I've been able to learn VBA that way but only because I've learnt other languages properly first. Without a grounding in the basics of programming (variables, objects, control structures etc) you're never really going to be able to grasp what you're doing. It's tedious but you really have to start out doing "Hello World" programs and simple calculators and things to get to grips with how programming works. That makes it much easier to pick up things like the object models for access and outlook. There's plenty of free tutorials around the web for any language and finding a good book is invaluable. If you want to carry on the way you're going you should at least find some tutorials or books on VBA (Visual Basic for Applications which is built into access, outlook etc as opposed to VB which is for stand alone apps). I'd reccommend though that you try out some "proper" languages first like Java, C# or VB.net (just for windows apps at first, stay away from stuff like J2EE, web services, ASP etc).

That being said, since we were nearly there and I'm kind of interested in doing something similar now too put the following in a function on it's own, it will find a mail with subject "test" in you inbox and if it's there it will take the first attachment if it has one.

Dim myOlApp As Outlook.Application
Dim myOlFolder As Outlook.MAPIFolder
Dim myItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Dim myNameSpace As Outlook.NameSpace

Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myOlFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
Set myItem = myOlFolder.Items.Find("[subject] = ""test""")
If Not TypeName(myItem) = "Nothing" Then
Set myAttachments = myItem.Attachments
If myAttachments.Count > 0 Then
If MsgBox("Save attachment to c:\", vbYesNo + vbQuestion) = vbYes Then
myAttachments.Item(1).SaveAsFile "C:\" & myAttachments.Item(1).DisplayName
End If
Else
MsgBox ("No Attachments In Mail")
End If
Else
MsgBox ("No Mail With Subject ""Test""")
End If

JohnnyBravo

il level with you i have a degree in C C++ and a diploma in java for web based development But nothing in VB or anything related to microsoft applications
I have only been working on this since monday
The majority of the time its not related to access of which i have little or no experience
The application my work has me working on is done apart from this little extra bit i thought i try do myself to add transparency

This is infuriating but not the hardest part as all this has to be ported to the web if it even works

That works perfectly cheers lad your a legend and it has been a very good learning experience on how to interact with different MS applications
I was wondering if i could ask you a question on the object
Say we have multiple emails of the same name
Say they only appear once a month so i have twelve months worth of emails i.e. 12 emails
Is it possible to download the most recent email only of the current month do you think and delete the previous elevan months worth
Bare in mind this is only a conceptual plan

stevenmu

Well you'll have a lot of trouble getting this to work across the web, you'll have to implement it with client side VB script or javascript which you might be more familiar with using automation objects. Googling "javascript excel automation ole" and similar will help but the main problem will be security. Because of various attacks, default security settings will block web pages from interacting with outlook or access, if it's only for a few local users you can probably change some of their settings but for the general public and home users you can forget about it. There may be other possible approaches to what you're trying but I'm not sure.

JohnnyBravo

Well i was thinking of something like this

The reports are sent from an automated system in excel format to all the managers
I was going to get an old pc, set up an email account say "level 2 managers"
So there might be about 60-70 reports sent out daily to thi semail account
The managers would load up their respective web pages and use the import function
As only one manager will be using his/her web page mutual exclusion is not really an issue yet
The import function would import the 3-4.xls files out of the 70-80 to the desktop based on current month from there they would be imported into the relevant Access database on the server
The Queries,graphing and all that has already been programmed
I thought alright that it would be a bit optimistic to just run a wizard and turn the switchboard and associated forms into a web page but thats all in the fun of it
Since it will only be accessed from within an intranet secuirity wouldnt really be an issue

And i reckon once i export one of the switchboards to the net that tailer making other managers web pages based on the same princibal would be easyish

What do you think

stevenmu

Hmmm... I don't think you really realise what you've got yourself into with this. The way you're thinking of going about it is way too complicated. Trusting the managers to upload the files properly is a big risk (there's a reason why they're managers, as the old saying goes "If you can't do, teach, if you can't teach, manage"). Keeping track of the different files and databases would also be a huge problem. Your best bet might be to try and get the automated system to also put the reports on to a folder on the web server. I'm pretty sure there's ASP/Java examples around the web somewere that will let you read from an excel file and then display it as HTML (you may need to get some custom components) or else import it into a database. You'll be better off importing it all into the same database that way you can just use the one set of pages for each manager but have some kind of login, or even a drop down list of managers, and just display the data for that manager. ASP.Net may be able to do a lot of this for you, I'm not sure but it could be worth looking into.

Best of luck (you're going to need it)

Steve

JohnnyBravo

i was talking to the lads there in the web based department
And they said there should be code out there to execute the program off the server
So i could have a link on a web page to the address on the server which if i could get working would uncomplicate a lot of stuff
They told me to post it to find out
If it works it would be the job as it would allow very fast development of these projects and just reference the execution of them from the web page

As for the managers

I might just up load the files to the project and put the date at the to as to when they were last uploaded


i wanted to ask you one question on the Function if i wanted to make it dynamic
i want to change the line

Set myItem = myOlFolder.Items.Find("[subject] = ""temp""")
so that it is something like this
Set myItem = myOlFolder.Items.Find("[subject] = "'+ temp +' "")
where + temp+ is a variable similiar to SQL
Can it be done something like that do you know

stevenmu

Yeah, you can have
Set myItem = myOlFolder.Items.Find("[subject] = """ & temp & """")

where temp is the name of your variable

Good luck

RicardoSmith

Seems like a complicated way of doing things. I assume that all the managers are in remotes sites and that each one has a difference access db to update?

Why email the excel file in the first place? Why not just put all the Excel files in a sercure website for downloading and have the various access databases download the files and import the excel file automatically. Then when finished generate a report email if the import was a success or not to each manager?