-
Knowledge Base Approver
The King of Overkill!
VBAX Master
Hi,
Sorry for the delayed reply, havent signed on in a few days.
Regarding the button_click code, you can put the call code in the class which handles the _click for each button (easier and shorter than tons of individual events). The way I originally gave you the _Click code in the class was:[vba]Private Sub vButton_Click()
'reference vSaveFolder variable to retrieve folder to save to
MsgBox vSaveFolder
End Sub[/vba]If you put the code in there it should trigger on every button's click.
As I was away for a few days and I'm sure you've made big changes and advances, so let me know if theres anything left you need help with
Matt
-
Thanks for the above Matt. I did try to make it work but with no sucess. In the end to stop wasting more time I just made 100 statements one for each button. I may have another try later as I can't see why it wouldn't work.
I've basically finished everything now just updateing the Word help file after fixing my last error catcher tonight.
However (isn't there always) I decided to put another button on my toolbar to open the spreadsheet for easier editing, but I just can't make it work. The code I have for the Help Word doc functions correctly but using this as a basis, the code for the Excel doc just keeps jumping to the error statement. Could you advise where I'm going wrong. The two codes are below.
[VBA]
Sub Help() 'this opens the "Help" word.doc in the Mailfile folder (path below)
Dim wd As Object
Dim HelpPath As String
Dim AppOpen As Boolean
Dim ErrorMessage As Long
HelpPath = "C:\MailFile\MailFileHelp.doc"
On Error GoTo Message
Set wd = GetObject(, "word.application") 'Checks to see if the file is already open
wd.Documents.Open HelpPath 'and opens the file
wd.Visible = True
wd.Application.Activate 'Makes the application have the focus
GoTo NoMessage
Message:
ErrorMessage = MsgBox(" The help file could not be opened." & vbCr & vbCr & _
"Check that the file MailFileHelp.doc is in the folder " & vbCr & vbCr & _
" C:\MailFile\", vbOKOnly + vbExclamation, "Help file not found")
NoMessage:
Set wd = Nothing
End Sub
Sub Excel() 'this opens the MailFile.xls settings file in the Mailfile folder (path below)
Dim myXL As Object
Dim SettingPath As String
Dim AppOpen As Boolean
Dim ErrorMessage As Long
SettingPath = "C:\MailFile\MailFile.xls"
On Error GoTo Message
Set myXL = GetObject("C:\MailFile\MailFile.xls") 'Checks to see if the file is already open
'Set myXL = GetObject(, "Excel.application") 'Checks to see if the file is already open
myXL.Workbooks.Open SettingPath 'and opens the file
myXL.Visible = True
myXL.Application.Activate 'Makes the application have the focus
GoTo NoMessage
Message:
ErrorMessage = MsgBox(" The settings file could not be opened." & vbCr & vbCr & _
" Check that the file MailFile.xls is in the folder " & vbCr & vbCr & _
" C:\MailFile\", vbOKOnly + vbExclamation, "Excel file not found")
NoMessage:
Set myXL = Nothing
End Sub
[/VBA]
(I hate asking this as it must be something very basic but I've tried lots of things and nothing seems to work)
I'll post the completed programme as soon as I've done the help
Thanks
Jeff
-
Knowledge Base Approver
The King of Overkill!
VBAX Master
Hi Jeff,
The commented version of the GetObject in the excel one is the correct one (comma first, then excel.application). However, the logic in what you're doing might need to be a little changed, as far as the error checking goes.
Typically, I will check to see that a file exists first. If it does, then I'll see if the application is already open. If it isn't, I open it. Then I open the desired file. I've changed your word one a little bit, give it a try:[vba]Sub Help() 'this opens the "Help" word.doc in the Mailfile folder (path below)
Dim wd As Object
Dim HelpPath As String
HelpPath = "C:\MailFile\MailFileHelp.doc"
'check to see the file exists
If Len(Dir(HelpPath)) = 0 Then
MsgBox " The help file could not be opened." & vbCrLf & _
"Check that the file MailFileHelp.doc is in the folder " & vbCrLf & _
" C:\MailFile\", vbOKOnly + vbExclamation, _
"Help file not found"
Exit Sub
End If
'check to see if word is already open
On Error Resume Next
Set wd = GetObject(, "word.application")
On Error GoTo 0
If wd Is Nothing Then
Set wd = CreateObject("word.application")
End If
'load the file
wd.Visible = True
wd.Documents.Open HelpPath 'and opens the file
wd.Application.Activate 'Makes the application have the focus
'release memory
Set wd = Nothing
End Sub[/vba]
Along the same lines..[vba]Sub Excel() 'this opens the MailFile.xls settings file in the Mailfile folder (path below)
Dim myXL As Object
Dim SettingPath As String
SettingPath = "C:\MailFile\MailFile.xls"
If Len(Dir(SettingPath)) = 0 Then
MsgBox " The settings file could not be opened." & vbCrLf & _
" Check that the file MailFile.xls is in the folder " & vbCrLf & _
" C:\MailFile\", vbOKOnly + vbExclamation, _
"Excel file not found"
Exit Sub
End If
On Error Resume Next
Set myXL = GetObject(, "Excel.application")
On Error GoTo 0
If myXL Is Nothing Then
Set myXL = CreateObject("excel.application")
End If
myXL.Workbooks.Open SettingPath
myXL.Visible = True
myXL.ActiveWindow.Activate 'Makes the application have the focus
Set myXL = Nothing
End Sub[/vba]
Matt
-
Thanks for the code Matt it still didn't work completely but I moved one line so it now works. I can see your method is better rather than using the error to go to the message.
What appears to happen with your code and explains the problem with mine is with the wd code wd is never nothing whether word is open or not. it is always "Microsoft Word". In Excel it is nothing if it isn't open but "microsoft Excel" if it is. I wonder if is to do with Outlook using word as its e-mail editor. perhaps word is always open in the background.
Anyway I've finished the project and attach my results here. I'll probably play with it a bit more (just remembered one more error in that the mailfile toolbar opens if you open a word attachment must fix that as well did in my last version anyway). I've given you and the forum some recognition in the Help file. please advise if you want that removed. Its been an interesting journey and I've learn't a lot.
I'll post 3 zip files separately as they exceed the forum limit
Regards
Jeff
-
This is the code I forgot in the first zip
-
Part 1 of the help file. I hyperlinked it so it may not work when recombined
-
Part 2 of the help file.
I apologise again for my coding ability. I've learn't a lot and will try to be better next time
Jeff
-
Knowledge Base Approver
The King of Overkill!
VBAX Master
Thats still quite impressive!
FWIW, you can change the modified date of a file (based on "When E-mails are saved in Windows, the “Date Modified” shown in Windows Explorer is the date saved, not the date of the E-mail" from your help file)
[vba]Sub AnExample()
Dim vFile As String, vDate As Date
vFile = "C:\jefft\MailFile.xls"
vDate = Now - 20 '20 days ago
UpdateModifiedTime vFile, vDate
End Sub
Sub UpdateModifiedTime(ByVal FilePath As String, ByVal NewModifiedTime As Date)
Dim sh As Shell, sf As Shell32.Folder, fi As Object
Dim pos As Long, vPath As String, vFile As String
pos = InStrRev(FilePath, "\")
vPath = Left(FilePath, pos)
vFile = Mid(FilePath, pos + 1)
Set sh = CreateObject("Shell.Application")
Set sf = sh.NameSpace(vPath)
Set fi = sf.ParseName(vFile)
fi.ModifyDate = NewModifiedTime
Set sh = Nothing
Set sf = Nothing
Set fi = Nothing
End Sub[/vba]
As for the mention in the help file, you can feel free to keep it, or you can feel free to remove it. My suggestion says that you should remove it and get all the kudos for it (your idea, all I did was help to guide you, and you still did most of the work).
Very nice work though!
Matt
-
Thanks again Matt. I'll have a play with that last code. If it does what I think it can then I may rewrite that portion of the code in Mailfile so the reverse date isn't needed. I'll have to get it passed the QA department. But if it does enable me to change the saved date to the received / sent date, you may have just changed how the company operates.
Just one other thing, is there a book / website with all these wonderful bits of code in, for instance "UpdateModifiedTime", or do you just create them as required from scratch?
Regards
Jeff
-
Knowledge Base Approver
The King of Overkill!
VBAX Master
Generally I just create those from scratch, though the above was something I had in a little bag of tricks I've compiled, as I dont usually change the modified date on things. Google is your best bet for websites (if you cant find what you want here), since it will link you places like msdn, freevbcode.com, codeproject.com, etc that have lots of snippets and what not. Or you can always just ask
Matt
-
Matt Back Again!!
I've loaded the programe on my Work computer and everything works fine except my Move code. This works on my home laptop where I have been doing the development.
What appears to happen is it doesn't recognise the
.Item(iItem).Move vFolder
stepping through the code it moves through the above but doesn't move it. It does copy the file to the server in the previous
.Item(iItem).SaveAs StrFile, 3
Part code below.
[VBA] StrName = StripIllegalChar(StrSubject)
StrAllName = StrName & ".msg"
StrFile = StrSavePath & " " & StrName & ".msg"
Call Check_Name
StrFile = StrSavePath & StrAllName
StrFile = Left(StrFile, 256) 'THIS NEEDS CHANGING. If the File + Path has more than 256
'characters it'll start by stripping the extension then the (x)
.Item(iItem).SaveAs StrFile, 3 'This works
'Next
End With
SaveMsg:
'''' The "Move" code is below here '''''
With Outlook.ActiveExplorer.Selection 'Added for moving the file
If Len(Dir(StrFile)) <> 0 Then 'Checks that the save occured and asks if further save required.
strMsg = "The e-mail has been saved" _
& vbCr & vbCr & "Do you want to save this e-mail to another location?"
Message = MsgBox(strMsg, vbYesNo + vbQuestion + vbDefaultButton2, "E-mail saved")
If Message = vbYes Then GoTo ExitSub 'This stops it moving the e-mail if you want to save again
If Message = vbNo Then Unload Quick_Form
If MoveToArchive = False Then GoTo NoMove 'To jump the Move code below if the form checkbox isn't ticked
.Item(iItem).MoveFile vFolder ' This doesn't Move the Mail This moves the mail if the box in the Form is ticked
[/VBA]
It recognises the Archives as if I change their name I get my error message. Is it anything to do with Windows 2000 rather than XP which is all I can see is different between my system an work. (though the .Move method is still shown in the help, file not surprising as we both run Outlook 2003). I've tried replacing .Move with .MoveFile but that doesn't seem to work I think I need to add the Source which I've yet to do.
Regards
Jeff
-
Knowledge Base Approver
The King of Overkill!
VBAX Master
That is odd, especially since you're using the same version! The .move method wont go away, so I'm really not sure why it isnt moving it (especially since it isn't erroring). Oh, I see now there is an On Error statement in there, try taking that out (in SaveSelectedEmails):
[vba]' On Error Resume Next
With Outlook.ActiveExplorer.Selection
iItem = 1[/vba]
Since you're not going to be looping with iItem, you may want to use your mItem variable instead of using the With block (also, you probably don't need 2 identical With blocks, though it isn't a big deal). But removing that On Error Resume Next (even temporarily) should help you figure out why the .Move isn't working.
Looking back more, I see the vFolder variable is being set with:[vba] On Error GoTo NoArchive 'This catches the error if either Archive isn't in place
If SentItem = True Then 'This is only true if the e-mail is a Sent Mail
Set vFolder = Application.Session.Folders("MailFile Sent") 'If true move it to this Archive
Else
Set vFolder = Application.Session.Folders("MailFile Inbox") 'If not move it to this archive
End If
MissArchive: 'After catching the error above the code continues from here
On Error GoTo 0 'This stops the On Error above from detecting any other errors[/vba]I see your .pst files are called "MailFile_Inbox", not sure if the space/underscore is causing the issue (perhaps your laptop either has both the space and underscore files..?)
ADDED: ok that doesn't make sense, since you'd go to the NoArchive line. Maybe you could change the above block to:[vba] On Error Resume Next 'This catches the error if either Archive isn't in place
If SentItem = True Then 'This is only true if the e-mail is a Sent Mail
Set vFolder = Application.Session.Folders("MailFile Sent") 'If true move it to this Archive
Else
Set vFolder = Application.Session.Folders("MailFile Inbox") 'If not move it to this archive
End If
On Error GoTo 0 'This stops the On Error above from detecting any other errors
If vFolder Is Nothing Then
strMsg = "The 'MailFile Inbox' and / or 'MailFile Sent' Archive folders" & vbCr & _
"are not present, but the 'Move' box is still ticked on the form." _
& vbCr & vbCr & "The E-mail will be saved to the server but not moved to the Archive." & vbCr & _
"Remove the tick or open the Archives. See the Help file for details"
Message = MsgBox(strMsg, vbOKOnly + vbCritical, "E-mail will not be moved.")
MoveToArchive = False 'This stops the Move if the archive files aren't present.
End If[/vba]
Just brainstorming here, trying to help troubleshoot it
Matt
-
Thanks for the suggestions Matt I'm away this weekend and may not have much time but I'll try your suggestions.
I'm taking both laptops and will try stepping through the code on both at the same time try to see any differences.
As for the _ in the archive name in the MailFile folder, it didn't make any difference on my home laptop and the work one recognises it without. I thought the position in my archives might have made a difference so renamed it "A mailfile Inbox" so it was at the top of the archives. I got my error message as I forgot to change the code, however I had no error when I changed the name in the code to match. I'll try the On Error GoTo 0
Thanks again (Should be working on a Bar type programme in MS Project but this is more interesting)
Jeff
-
Matt
I think I understand why my mail won't move but can't figure out a way to make it work
First the move does work when a mail enters the Sent folder. The code for this works because the mail isn't actually opened it's detected within the ThisOutlookSession Sub ItemsInSentFolder_ItemAdd. The code runs, the item is saved on the server and moved to the archive.
With the Inbox mails the way I initiate the Save & Move is to Open the mail and then Close it. The Close action is picked up in the clsCloseEvent Private Sub oMailItem_Close.
The problem is all the code runs before the mail actually closes. This was bypassed by an On Error Resume Next statement. Removing this Error catcher gives me a "Method 'Move' of Object 'MailItem' failed" statement.
What I need to know is Is there an "AfterMailClosed" event or some way to initiate further code after the Private Sub oMailItem_Close is finished?
What I may try is to put another button on the open e-mail toolbar to initiate the Close outside of the class module then run the code from here.
I've only just thought of that so that will be tommorows attempt.
Regards
Jeff T
-
-
Knowledge Base Approver
The King of Overkill!
VBAX Master
Your mass amount of bouncing smileys made me laugh upon seeing it Glad to hear the extra button worked -- you probably could have trapped the events of Inspector windows to wait until it closed, but as long as you have a working solution in a way you can understand, all is good.
Let me know your working solution when you get it, I can always just skim through it and see if I see anything that sticks out as inefficient or anything. I'm sure its fine though
Matt
-
Thanks for the offer Matt I'm always willing to learn. I know I've still a long way to go. However things I'd now do differently (I hope).
I'd be a bit more cautious with On Error statements.
I use the GoTo far to much. I need to use ElseIF type statements.
When using the buttons on the Open Mail I've loaded them in clsInspector, I should have put them in A_Toolbar (perhaps this didn't work) but to delete them I've used code to delete each button in turn. This works but runs through the code 17 times to delete 5 buttons. I imagine it's checking each button name in turn. I should have used it's name I think.
In the quick form code I've used 100 subs to call each button click seperately. I just couldn't figure this out.
This is the first time I've used Class Modules so I'm not sure if all the code could go into 1 module. there's not much in two of them.
Anyway thanks for your help. I'll load the new code in this reply then the other bits (Help doc, Excel file & 2no Archives) in the next post.
Jeff
-
Here's the rest of it.
Thanks again
Jeff
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules