Macro for saving a local copy of a mail (and renaming the local copy)

Question

Company i work for manually saves certain requests (sent per mail) to a shared drive, renaming them as such: "YYYYMMDD_Firstname_Lastname". The mails are saved as .msg

Since we get about a hundred of these per week, I'd like to macro this so I don't waste time.

The article here: Outlook VBA macro for saving emails copies in a local folder explains how to save files locally, but I'd like to make following additions: - Rename the copy before it gets saved to the shared drive (manually if needed) - Select the shared path it needs to be saved to (preferably a drop-down with three choices) - create a proper userform for this

If anyone could assist with the code, or provide me with tutorials/guides on how to do this myself, I'd be extremely grateful.

P.S. just started using and creating macro's a week ago. Still very much a beginner. any link to a good tutorial for developers would be greatly appreciated, regardless of whether it answers my questions.

Thanks guys!

Used the code described in the article as such:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Call SaveACopy(Item)
End Sub

Sub SaveACopy(Item As Object)
    Const olMsg As Long = 3

    Dim m As MailItem
    Dim savePath As String

    If TypeName(Item) <> "MailItem" Then Exit Sub

    Set m = Item

    savePath = "c:\users\your_user_name\desktop\"  '## Modify as needed
    savePath = savePath & m.Subject & Format(Now(), "yyyy-mm-dd-hhNNss")
    savePath = savePath & ".msg"


    m.SaveAs savePath, olMsg


End Sub

Update: using the macro provided by Tony Dallimore I've amanaged to identify that .SenderName is the main info I need from the mails to be processed. All I need now is to replace the spaces in that output by underscores, and add the date in reverse in front of it to have my filename.

Thanks a bunch to Tony Dallimore for the continuous assistance on this project.

Since it seems somewhat confusing looking back on my original question, I'll try to clarify:

I get about 100 mails a week informing us of approvals of certain user requests. Company policy is to save these mails as .msg on a shared drive used for administration before processing the request. The filename of these messages needs to be as such: "YYYYMMDD_FIRSTNAME_LASTNAME.msg" (with YYYY being the year, MM being the month, and DD being the day on which we received these mails)

We get three main "types" of such mails, saved in different locations, but using the same filename respectively.

What I'd need is a macro or set of macros that can save these mails in the correct networkdrive under the correct format at the press of a button, or using minimal clicks/manual input.

I've decided to use .SenderName and .Senton, since those seem to give me most of what I need.

Answer 1

This is not a direct answer to your question. It is an investigation which I hope will provide the information necessary for an answer.

You say “… mails are auto-generated by the system …”. This may explain why I do not fully understand why your code works. I will explain my confusion after I have provided some background.

There are four distinct methods by which a MailItem can be selected for processing:

1. The user can select one or more emails and then call a macro to process the selected MailItem. (Note it is an email to the user but a MailItem to a macro.)
2. A macro can read up or down a folder of MailItems, reviewing properties to determine which are to be processed. Sort and Filter can be used to more quickly target the MailItems of interest.
3. You can specify a rule that will look at each email as it arrives and review properties such as subject and sender. If the email has the required properties, a number of actions can be performed. If the standard actions are not adequate, you can link a macro to perform any action available to a VBA macro.
4. You can instruct Outlook to call a macro whenever a particular event occurs. Events include: MailItem added to folder Xxxx,MailItem opened, MailItemsent, MailItemsaved, MailItem closed, MailItem replied to or MailItem forwarded.

Your code is using approach 4. In particular, you are using a MailItemsent event. You say “ … we get about a hundred of these [emails] per week …”. If “get” is the correct word, I would expect MailItem added to folder Inbox to be the appropriate event. Perhaps your code works because the system is generating emails from user X to user X.

If these emails are generated by the system, we cannot be sure what properties are set and what values they are set to. Please copy the code below to an Outlook module. Select one or more of these emails and run macro CallSubForSelectedEmails.

Option Explicit
Public Sub CallSubForSelectedEmails()

  Dim Exp As Explorer
  Dim ItemCrnt As MailItem

  Set Exp = Outlook.Application.ActiveExplorer

  If Exp.Selection.Count = 0 Then
    Call MsgBox("Please select one or more emails then try again", vbOKOnly)
    Exit Sub
  Else
    For Each ItemCrnt In Exp.Selection
      If ItemCrnt.Class = olMail Then
        Call DsplSimpleProperties(ItemCrnt)
      End If
    Next
  End If

End Sub
Sub DsplSimpleProperties(ItemCrnt As Outlook.MailItem)

  Dim InxR As Long

  Debug.Print "=============================================="
  Debug.Print "  Profile: " & Session.CurrentProfileName
  Debug.Print "     User: " & Session.CurrentUser
  With ItemCrnt
    Debug.Print "  Created: " & .CreationTime
    Debug.Print " Receiver: " & .ReceivedByName
    Debug.Print " Received: " & .ReceivedTime
    For InxR = 1 To .Recipients.Count
      Debug.Print "Recipient: " & .Recipients(InxR)
    Next
    Debug.Print "   Sender: " & .Sender
    Debug.Print " SenderEA: " & .SenderEmailAddress
    Debug.Print " SenderNm: " & .SenderName
    Debug.Print "   SentOn: " & .SentOn
    Debug.Print "  Subject: " & .Subject
    Debug.Print "       To: " & .To
  End With

End Sub

For one of my emails, this routine outputs:

==============================================
  Profile: Outlook
     User: Tony Dallimore
  Created: 08/04/2019 19:59:22
 Receiver: Tony Dallimore
 Received: 08/04/2019 18:45:39
Recipient: a.j.dallimore@acmeisp.com
   Sender: Lifecake
 SenderEA: support@lifecake.com
 SenderNm: Lifecake
   SentOn: 08/04/2019 18:45:37
  Subject: ?? Someone commented on Alex and Eric's video
       To: a.j.dallimore@acmeisp.com

Note 1, I am both the system user and the receiver of this email. This gives two possible ways of getting my first and last names. I use initials in my email address but your company may use names.

Note 2: my code uses approach 1 to select the emails to be processed. Macro CallSubForSelectedEmails calls macro DsplSimpleProperties for each selected email. I do all my investigations and all my development of email processing macros using code like this. This gives me complete control over which emails are processed. The call profile for macro DsplSimpleProperties is the same as that for a rule macro or an event macro. Once I have debugged my macro using approach 1 and switch to calling it from a rule or an event with minimal additional testing. I know of no easier way of debugging email processing macros.

Answer 2

Again this is not a complete answer because I do not have the information for a complete answer.

Task 1: Generate PathName

The information for the path name comes from the MailItem's Subject. For this example, I assume the request type is 1, 2 or 3 and it is the last character of the subject.

Dim PathName As String

' Generate end of subfolder name
Select Case Right$(ItemCrnt.Subject,1)
  Case "1"
    PathName = "xxxx"
  Case "2"
    PathName = "yyyy"
  Case "3"
    PathName = "zzzz"
  Case Else
    ' Subject does not conform to expected format.
    Exit Sub
End Select  

' Prefix root folder name and year of subfolder name
PathName = "P:\EMEA Requests\" & Year(ItemCrnt.SentOn) & "\" & PathName

Right$ is a function that extracts a specified number of trailing characters from a string. Functions Left$ and Mid$ are also available. If the subject is sufficiently complicated, we can consider Regex. Year is a function that extracts the year from a date. The value will be an integer but VBA will automatically convert it to a string if it used as a string.

If the routine cannot identify the request type, it abandons the MailItem. I will discuss this issue later.

Task 1; Suggestion 2: Generate PathName

You say the subjects lack a fixed format and just include words from the original request. You imply these words are good enough for a human to identify the request type. So the words for a request might include "hardware", "h'ware", "computer" or "laptop". Another request might include "software", "application or "app". This is a simple method of handling this type of situation. There is a better method which I will introduce if this looks feasible.

If Instr(1, LCase(ItemCrnt.Subject), "hardware") <> 0 Then 
  PathName = "xxxx"
ElseIf Instr(1, LCase(ItemCrnt.Subject), "h'ware") <> 0 Then 
  PathName = "xxxx"
ElseIf Instr(1, LCase(ItemCrnt.Subject), "computer") <> 0 Then 
  PathName = "xxxx"
ElseIf Instr(1, LCase(ItemCrnt.Subject), "laptop") <> 0 Then 
  PathName = "xxxx"
ElseIf Instr(1, LCase(ItemCrnt.Subject), "software") <> 0 Then 
  PathName = "yyyy"
ElseIf Instr(1, LCase(ItemCrnt.Subject), "application") <> 0 Then 
  PathName = "yyyy"
ElseIf Instr(1, LCase(ItemCrnt.Subject), "app") <> 0 Then 
  PathName = "yyyy"
Else
  PathName = ""
End If

You can keep adding possible keywords until your requestors run out of alternatives. Failing that you can use your userform with buttons approach after the macro has handled the easy messages.

Task 2: Generate FileName

Dim FileName As String

FileName = Format(ItemCrnt.SentOn, "yymmdd") & " " & Replace(ItemCrnt.SenderName," ", "_")

Task 0: Design

Before coding can start, you need to design the total process. You can start with something simple and then develop it as you better understand your requirement. You can code little bits as I did with PathName and FileName so you can understand the bits you need to fit together. But tackling something complex without a plan rarely ends satisfactorily.

My understanding of your requirement is incomplete but I will have a go at a design.

I would have a Rule that copied incoming emails of this type to an Outlook folder such as "Unsaved EMEA Requests". Note: these are copies; the original remains in the Inbox for processing as required. I assume there is a way to identify these emails that is within the functionality available to a rule.

I would have all the code in a macro which I would call once or twice a day as appropriate. This macro would read up folder "Unsaved EMEA Requests". If it can generate a path and file name for a message, it will save the message to the required disc folder and delete the message from the Outlook folder. If it could not process a message, it would leave it in Outlook folder "Unsaved EMEA Requests". If a message is left in Outlook folder "Unsaved EMEA Requests", you will know (1) that the macro needs enhancing to handle a previously unencountered message type or (2) the rule needs amending because it has copied the wrong sort of message.

I said "read up folder" not "read down folder". You access a MailItem within a folder by its position: 1, 2, 3, … Folder.Count. If you delete MailItem 2 then MailItem 3 becomes MailItem 2, MailItem 4 becomes MailItem 3 and so on. The value of Folder.Count is reduced by one. You sometimes see questions asking why their macro is only processing every other MailItem. The reason is they have coding like:

For InxI = 1 to Folder.Count
  ' Process and delete Folder.Item(InxI)
Next

With the above code, you process items 1, 2, 3 in turn. If you delete item 2, you will skip the original item 3 because it is now item 2.

The correct code is:

For InxI = Folder.Count To 1 Step -1
  ' Process and delete Folder.Item(InxI)
Next

With this code you process items 10, 9, 8, 7 in turn. If you delete item 9, you do not care that item 10 has become item 9 because you are now processing item 8.

If you are only reading items, you do not need to worry above this issue. But if you are adding or deleted items, you do need to worry about it.