MsOf10 Determine Who Sent Email - Shared Outlook Inbox Management-VBForums
Results 1 to 4 of 4

Thread: Determine Who Sent Email - Shared Outlook Inbox Management

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2012
    Posts
    10

    Determine Who Sent Email - Shared Outlook Inbox Management

    I manage a small department that uses a shared Outlook email inbox to communicate with our customers. Occasionally an employee will forget to add his/her signature to an email reply, thus making it harder for me to find out which employee sent the email.

    Each employee has their own company issued PC with a unique login. I know that each email has a unique message ID (i.e. entry ID, etc). After an email is sent, forwarded, or replied to from our shared email box the the sent item is saved in the standard Sent Items folder in Outlook. Is there a unique identifier embedded in every sent email (perhaps the PC username, full computer name, etc) that I can lookup to identify who sent any email? Any ideas are appreciated, thanks!

    Note: I'm unable to download any third party software so I'll have to build this from scratch. I'm vb savvy.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: Determine Who Sent Email - Shared Outlook Inbox Management

    there is a sendername property for each sent mailitem

    there is also an entryID property, but i have no idea if that is useful, of course you can also read any signature in the message body
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2012
    Posts
    10

    Re: Determine Who Sent Email - Shared Outlook Inbox Management

    Thank you for your reply, I appreciate all the info I can get. I'm still researching and still haven't been able to figure this out. I've been testing out the different outlook properities that I thought might give me the data I'm looking for. I'm testing using the below VBA code that I've added to my own Outlook (2010). The various property methods can be found here. You then create a rule in Outlook to run a script when a new email comes in (I'm having the script run with only certain keywords in the subject line so that it doesn't run with every email I receive right now).

    To test this, I've asked several of my employees today to send me an email from their computer (each has a different PC but has access to our a shared email box) and send me an email from the shared email box but without their standard email signature or any indication who they are. Of course they also used the keywords in the subject line so the script would run and pull the properties and add them to an Excel sheet. Unfortunately after trying nearly all of the properties listed on the website above, there was nothing unquie about each property that helped me to know which email came from which user. The EntryID was unique, but only by 1 character, and also the EntryID appears to just be a random ID for each email - but not for each user.

    So I'm still wondering if there's anyone out with some additional ideas. Keep in mind the above mentioned text was just an example. I'm not looking to enhance the below script, because that was just a test to see what properites I could pull when an email is sent to me. The employees will be sending emails to customers from our shared email box and I'm looking for a method that I can use to select a single/multiple email file(s) in our Sent Items folder and see which user sent the email - for situations where the user forgot to add their email signature and I need to figure out who sent the email. In addition, with something like this I would be able to count how many emails were sent by each user every day/week/month and keep track of the productivity of each employee. Thanks for your ideas!


    -----------------------------------------------
    Const xlUp As Long = -4162

    Sub ExportToExcel(MyMail As MailItem)
    Dim strID As String, olNS As Outlook.NameSpace
    Dim olMail As Outlook.MailItem
    Dim strFileName As String

    '~~> Excel Variables
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object
    Dim lRow As Long

    strID = MyMail.EntryID
    Set olNS = Application.GetNamespace("MAPI")
    Set olMail = olNS.GetItemFromID(strID)

    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")

    '~~> If not found then create new instance
    If Err.Number <> 0 Then
    Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0

    '~~> Show Excel
    oXLApp.Visible = True

    '~~> Open the relevant file
    Set oXLwb = oXLApp.Workbooks.Open("C:\Users\Desktop\Sample.xlsx")

    '~~> Set the relevant output sheet. Change as applicable
    Set oXLws = oXLwb.Sheets("Sheet1")

    lRow = oXLws.Range("A" & oXLApp.Rows.Count).End(xlUp).Row + 1

    '~~> Write to outlook
    With oXLws
    '
    '~~> Code here to output data from email to Excel File
    '~~> For example
    '
    .Range("A" & lRow).Value = olMail.Subject
    .Range("B" & lRow).Value = olMail.SenderName
    .Range("C" & lRow).Value = olMail.EntryID
    'etc, add/change other properties for each new column
    Last edited by gjohn282; Jun 27th, 2013 at 07:55 PM.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,889

    Re: Determine Who Sent Email - Shared Outlook Inbox Management

    threaten them with dire consequences, if they do not sign their emails
    change to using exchange server, where these matters are taken care of, by the logged in user from any computer being the sender
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.