Simple VBA for Outlook - Copy last sent email

[spacecaptainsuperguy]

I've done some VBA in Excel, but never in Outlook and am hopeful I can get some pointers. I posted this in the General forum but so far it hasn't received any suggestions, so I thought I'd see if anyone on the Excel side might have some ideas.

I'm looking to create a simple macro that will do the following (sub-bullets to help explain what I'm currently doing manually):

• Open the most recently sent email and "forward" it.
  • By "Forward" it, I am looking to capture the header information you see when you forward an email (the From:, Sent:, To: & Subject: info). There may be a way to get this info in VBA...however forwarding it is how I've been getting it manually.
• Copy everything in the email from the word From: down to the bottom.
  • when I'm manually doing this, From: is the first word after my signature line of the new forwarded email. Again...there may be an easier/cleaner way to do this in VBA....I'm simply trying to explain what I'm doing manually so hopefully that helps.
• Close the forwarded email and do not save

That's it. I am trying to find a simple way to copy and paste this information into another program we use to log conversations. In a perfect world, I'd be able to run this macro in Outlook after sending the email and then jump over to our other program and simply paste it there from the clipboard.

Thanks in advance for any assistance.

 

[nguyendang]

Run the following macro in Outlook to see if it produces the expected result.
This macro will find the last sent mail and create a forwarded mail based on it, then print everything (except signature) in the body of that forwarded mail.
You may modify the code to suit your needs.

'Add a reference to Microsoft Office Word 1x.0
Option Explicit

Private Sub GetLastSentMail()
    Dim objSentEmailsFld As Outlook.Folder
    Set objSentEmailsFld = Application.Session.Stores.Item("abc@gmail.com").GetDefaultFolder(olFolderSentMail) 'abc@gmail.com: name of the store (shown in the main Outlook window)
    Dim strFilter As String
    Dim colItems As Outlook.Items
    Set colItems = objSentEmailsFld.Items
    If colItems.Count > 0 Then
        colItems.Sort "[ReceivedTime]", True
        Dim objMail As Outlook.MailItem
        Set objMail = colItems.GetFirst
        Dim objForwardedMail As Outlook.MailItem
        Set objForwardedMail = objMail.Forward
        Dim objWE As Word.document
        Set objWE = objForwardedMail.GetInspector.WordEditor
        Dim objWdSel As Word.Selection
        Set objWdSel = objWE.Windows.Item(1).Selection
        If objWE.bookmarks.Exists("_MailAutoSig") Then
            Dim objSig As Word.Range
            Set objSig = objWE.bookmarks.Item("_MailAutoSig").Range
            objSig.Select
            objWdSel.collapse wdCollapseEnd
            objWdSel.EndKey wdStory, wdExtend
            Debug.Print objWdSel.Range.Text
        End If
        objForwardedMail.Close olDiscard
    End If
End Sub

 

[spacecaptainsuperguy]

Run the following macro in Outlook to see if it produces the expected result.
This macro will find the last sent mail and create a forwarded mail based on it, then print everything (except signature) in the body of that forwarded mail.
You may modify the code to suit your needs.

'Add a reference to Microsoft Office Word 1x.0
Option Explicit

Private Sub GetLastSentMail()
    Dim objSentEmailsFld As Outlook.Folder
    Set objSentEmailsFld = Application.Session.Stores.Item("abc@gmail.com").GetDefaultFolder(olFolderSentMail) 'abc@gmail.com: name of the store (shown in the main Outlook window)
    Dim strFilter As String
    Dim colItems As Outlook.Items
    Set colItems = objSentEmailsFld.Items
    If colItems.Count > 0 Then
        colItems.Sort "[ReceivedTime]", True
        Dim objMail As Outlook.MailItem
        Set objMail = colItems.GetFirst
        Dim objForwardedMail As Outlook.MailItem
        Set objForwardedMail = objMail.Forward
        Dim objWE As Word.document
        Set objWE = objForwardedMail.GetInspector.WordEditor
        Dim objWdSel As Word.Selection
        Set objWdSel = objWE.Windows.Item(1).Selection
        If objWE.bookmarks.Exists("_MailAutoSig") Then
            Dim objSig As Word.Range
            Set objSig = objWE.bookmarks.Item("_MailAutoSig").Range
            objSig.Select
            objWdSel.collapse wdCollapseEnd
            objWdSel.EndKey wdStory, wdExtend
            Debug.Print objWdSel.Range.Text
        End If
        objForwardedMail.Close olDiscard
    End If
End Sub

Thanks so much.
I think I've figured out the name of the store as mentioned in your comment, but not 100%.
I tried stepping through the code but get an error on this line:

Dim objWE As Word.document

That says: User-defined type not defined
Does that relate to the comment at the top of the code about adding a reference to MS Word? or something else entirely.

 

[nguyendang]

Thanks so much.
I think I've figured out the name of the store as mentioned in your comment, but not 100%.
I tried stepping through the code but get an error on this line:

Dim objWE As Word.document

That says: User-defined type not defined
Does that relate to the comment at the top of the code about adding a reference to MS Word? or something else entirely.

Yes, it does.
You need to add a reference to Microsoft Word 1x.0 (depending on the version of Office you're using) Object Library by clicking Tools-References in VBE.

 

[spacecaptainsuperguy]

Thank you. That got rid of the error message. In order to be able to run the macro, I had to get rid of the PRIVATE. Is there a reason that it is/needs to be private? When I remove it and then run it, it runs without error, however nothing seems to be copied to the clipboard. Am I doing something wrong or missing something else? I'm not sure how to best troubleshoot this one so any advice is greatly appreciated.

 

[nguyendang]

Thank you. That got rid of the error message. In order to be able to run the macro, I had to get rid of the PRIVATE. Is there a reason that it is/needs to be private? When I remove it and then run it, it runs without error, however nothing seems to be copied to the clipboard. Am I doing something wrong or missing something else? I'm not sure how to best troubleshoot this one so any advice is greatly appreciated.

To figure out a store, you can check the main Outlook window (in case your Outlook has multiple email accounts logged in).
For example, "nguyendang050695@gmail.com" is the name of the store which contains a Sent Mail folder from where I want to get the last sent mail.

You may try the following VBA macro to see if it works.

Option Explicit

'Add a reference to Microsoft Office Word 1x.0 Object Library

Public Sub GetLastSentMail()
    Dim objSentEmailsFld As Outlook.Folder
    Set objSentEmailsFld = Application.Session.Stores.Item("abc@gmail.com").GetDefaultFolder(olFolderSentMail) 'abc@gmail.com: name of the store (shown in the main Outlook window)
    Dim strFilter As String
    Dim colItems As Outlook.Items
    Set colItems = objSentEmailsFld.Items
    If colItems.Count > 0 Then
        colItems.Sort "[ReceivedTime]", True
        Dim objMail As Outlook.MailItem
        Set objMail = colItems.GetFirst
        Dim objForwardedMail As Outlook.MailItem
        Set objForwardedMail = objMail.Forward
        objForwardedMail.Display
        Dim objWE As Word.document
        Set objWE = objForwardedMail.GetInspector.WordEditor
        Dim objWdSel As Word.Selection
        Set objWdSel = objWE.Windows.Item(1).Selection
        If objWE.bookmarks.Exists("_MailAutoSig") Then
            Dim objSig As Word.Range
            Set objSig = objWE.bookmarks.Item("_MailAutoSig").Range
            objSig.Select
            objWdSel.collapse wdCollapseEnd
            objWdSel.EndKey wdStory, wdExtend
        Else
            objWdSel.EndKey wdStory, wdExtend
        End If
        objWdSel.Range.Copy
        Debug.Print objWdSel.Range.Text
        objForwardedMail.Close olDiscard
    End If
End Sub

 

[spacecaptainsuperguy]

To figure out a store, you can check the main Outlook window (in case your Outlook has multiple email accounts logged in).
For example, "nguyendang050695@gmail.com" is the name of the store which contains a Sent Mail folder from where I want to get the last sent mail.
View attachment 93008
You may try the following VBA macro to see if it works.

Option Explicit

'Add a reference to Microsoft Office Word 1x.0 Object Library

Public Sub GetLastSentMail()
    Dim objSentEmailsFld As Outlook.Folder
    Set objSentEmailsFld = Application.Session.Stores.Item("abc@gmail.com").GetDefaultFolder(olFolderSentMail) 'abc@gmail.com: name of the store (shown in the main Outlook window)
    Dim strFilter As String
    Dim colItems As Outlook.Items
    Set colItems = objSentEmailsFld.Items
    If colItems.Count > 0 Then
        colItems.Sort "[ReceivedTime]", True
        Dim objMail As Outlook.MailItem
        Set objMail = colItems.GetFirst
        Dim objForwardedMail As Outlook.MailItem
        Set objForwardedMail = objMail.Forward
        objForwardedMail.Display
        Dim objWE As Word.document
        Set objWE = objForwardedMail.GetInspector.WordEditor
        Dim objWdSel As Word.Selection
        Set objWdSel = objWE.Windows.Item(1).Selection
        If objWE.bookmarks.Exists("_MailAutoSig") Then
            Dim objSig As Word.Range
            Set objSig = objWE.bookmarks.Item("_MailAutoSig").Range
            objSig.Select
            objWdSel.collapse wdCollapseEnd
            objWdSel.EndKey wdStory, wdExtend
        Else
            objWdSel.EndKey wdStory, wdExtend
        End If
        objWdSel.Range.Copy
        Debug.Print objWdSel.Range.Text
        objForwardedMail.Close olDiscard
    End If
End Sub

Holy jeepers that worked perfectly. Thanks so much for your help!!