Consulting

Page 3 of 3 FirstFirst 1 2 3
Results 41 to 58 of 58

Thread: Solved: Changing e-mail "subject" line within a Macro

  1. #41
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  2. #42
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  3. #43
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  4. #44
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  5. #45
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    This is the code I forgot in the first zip

  6. #46
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    Part 1 of the help file. I hyperlinked it so it may not work when recombined

  7. #47
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  8. #48
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  9. #49
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  10. #50
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  11. #51
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  12. #52
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  13. #53
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  14. #54
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  15. #55
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    Matt

    Putting a new button on the open mail toolbar seems to have worked. I've tested as much as I can until I return to work tommorow, so hopefully all is now OK. I still don't know why the previous code worked on one computer and not the other. This new version seems to work on both.


    I'll post the revised code once I've checked it in case anybody is interested.

    Next I'll have a go with your suggestion at #48 to change the modified date in Windows explorer. That should do away with the need for reverse dates.

    regards

    Jeff

  16. #56
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    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

  17. #57
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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

  18. #58
    VBAX Contributor JeffT's Avatar
    Joined
    Oct 2004
    Location
    Maidenhead, Nr London UK
    Posts
    105
    Location
    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
  •