Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Emails on a Server Using a VBA

    I have been trying to create a code to save incoming and outgoing emails to a project specific folder on our server, i'm having trouble working out the loop to run through the server location and find the job specific folder; The process should be as this:

    1. When the code is run a input box appears for a string of text to input the location of the folder on a server. IE Project Number
    2. When the project number is inputted the macro will search through our server “P:\Group\JOBDATA\$\$” and find the folder and save any incoming emails to a specific folder “Email.In” within the “Correspondence” Folder and “Email.Out” for outgoing emails.



    I have the SaveAs code ready but its the loop and input box which i'm having trouble with.

    Code:
    Sub SaveMessages()
     
        'Declaration
        Dim myItems, myItem As Object
        Dim myOrt As String
        Dim myOlApp As New Outlook.Application
        Dim myOlExp As Outlook.Explorer
        Dim myOlSel As Outlook.Selection
        Dim FindTerm(13)
        
        'Set invalid characters to replace
        FindTerm(0) = "*"
        FindTerm(1) = "@"
        FindTerm(2) = "\"
        FindTerm(3) = "("
        FindTerm(4) = ")"
        FindTerm(5) = "["
        FindTerm(6) = "]"
        FindTerm(7) = "?"
        FindTerm(8) = "<"
        FindTerm(9) = ">"
        FindTerm(10) = "!"
        FindTerm(11) = "{"
        FindTerm(12) = "}"
        FindTerm(13) = ":"
         
        'Ask for destination folder
        myOrt = InputBox("Destination", "Save Attachments", "P:\")
         
        On Error Resume Next
         
        'work on selected items
        Set myOlExp = myOlApp.ActiveExplorer
        Set myOlSel = myOlExp.Selection
         
        'for all items do...
        For Each myItem In myOlSel
         
          strdate = myItem.SentOn
          newdate = Format(strdate, "yyyymmddhhmm")
          strname = newdate & "-" & myItem.Subject & ".msg"
           
          For i = 1 To 13
          newstr = Replace(strname, FindTerm(i), " ")
          strname = newstr
          Next
          myItem.SaveAs myOrt & newstr
          myItem.Delete
             
        Next
         
        'free variables
        Set myItems = Nothing
        Set myItem = Nothing
        Set myOlApp = Nothing
        Set myOlExp = Nothing
        Set myOlSel = Nothing
         
    End Sub

    I know this might be asking a lot but if anyone can help that would be greatly appreciated.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Seems to me you haven't instantiated myOLApp, you've only dimmed it.
    Did you mean "Set myOlApp As New Outlook.Application"?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Nov 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    Seems to me you haven't instantiated myOLApp, you've only dimmed it.
    Did you mean "Set myOlApp As New Outlook.Application"?

    cheers, Paul
    I'm not too sure i'm fairly new to VBA /Code so this is as far i got.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Before you can use a variable, e.g. "myOLApp", you have to give it a value. In this case you want it to be the application Outlook (I assume). This is achieved by using "set" to give it the value "New Outlook.Application". A quick internet search will give you examples of this use.

    cheers, Paul

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •