Outlook Macro - screenshot last email in email thread

[Satrapia]

Hi to all,
for my work, I usually keep trace of all customer emails in excel using screenshots.
so basically, every time the customer write me, I screenshot his email (without all the previous conversations) and paste in Excel spreadsheet.

is there any way to create a macro that make a screenshot of the last customer email and put it into clipboard?

thanks a lot,

 

[6StringJazzer]

It is possible, but I recommend that you manage these emails in folders right in Outlook, or maybe copy the actual email .msg file into Excel if it must be in Excel. Screenshots are a weak way of managing emails because you cannot copy the text, reply to it, edit it, or anything else that you might want to do to an email.

 

[Satrapia]

I can understand your point but for me it is good enough: in few second I can come back to all the conversation and check all the info.
If I need the actual email I have all the references (I am doing the screenshot with all the header info and date) and search for it in Outlook.

 

[Dan_W]

I can understand your point but for me it is good enough: in few second I can come back to all the conversation and check all the info.
If I need the actual email I have all the references (I am doing the screenshot with all the header info and date) and search for it in Outlook.

I have to agree with 6StringJazzer. This seems wildly inefficient. Yes, it is possible to so what you're asking, I suppose, and there are plenty of examples of how to take screenshots on this forum, but it would be (a) a lot slower than simply exporting the email info, and (b) less impact on resources. Also, if you're there extracting info about the email anyway, you could identify its unique item reference and use that to quickly pull it up, rather than searching outlook for it with the header information. Just a thought.

 

[Satrapia]

I have to agree with 6StringJazzer. This seems wildly inefficient. Yes, it is possible to so what you're asking, I suppose, and there are plenty of examples of how to take screenshots on this forum, but it would be (a) a lot slower than simply exporting the email info, and (b) less impact on resources. Also, if you're there extracting info about the email anyway, you could identify its unique item reference and use that to quickly pull it up, rather than searching outlook for it with the header information. Just a thought.

Ok, but if I export the email info only and I would like to re-check where they come from, I have to re-check all the email thread.
In this way I can just scroll a bit the sheet and read it.

Regarding that fact you cannot select the text you are right but, I just need the reference to be able to look at the customer email.
Usually info has been already copied and paste or re-write (we are talking about few numbers).
I just to justify where they come from and even if I wrote in the next cell "info coming from email of xx.xx.xxxx", I have to show the actual email and usually screenshot is more than enough.

I have tried to impor the email as object but it it not very useful for me.
you cannot see the email picture with object, you have to click on it.
I know I have better info with the object but I have to open one email at a time to check if the info is there or in the next one.

I know that I am generating a huge Excel file working in this way but it is quite good for me.

Regarding the code to take screenshot, I was not able to find a way to include email header and the part of the email not showing in the monitor (in case of long email).
thanks for help,

 

[RoryA]

Regarding the code to take screenshot, I was not able to find a way to include email header and the part of the email not showing in the monitor (in case of long email)

A screenshot is precisely that - a shot of the screen. Anything not visible on screen won't be part of it. Seems like yet another reason to change the way you are doing things...

 

[6StringJazzer]

It will be much easier to do this in your Excel file vs. Outlook.

Save your Excel file as macro-enabled.
Insert a new module and paste in the following code. Select an email in Outlook in your Inbox view. Run sub InsertTextBox. The text from the currently selected email will be inserted into a textbox located at the currently active cell. It will have scroll bars so you can view all of the text. Be aware that it is not protected against editing, so you can copy text but you can also change it.

Public Sub InsertTextBox()

   Dim NewTextBox As OLEObject
   
   Set NewTextBox = ActiveSheet.OLEObjects.Add( _
      ClassType:="Forms.TextBox.1", _
      Link:=False, _
      DisplayAsIcon:=False, _
      Left:=ActiveCell.Left, _
      Top:=ActiveCell.Top, _
      Width:=300, Height:=120)
   
   With NewTextBox.Object
      .MultiLine = True
      .Value = MailContent
      .ScrollBars = 3 ' both
   End With
   
End Sub

Public Function MailContent() As String

   Dim IncomingEmail As Object ' Outlook.MailItem
   Dim C As Long
   
   Dim OutlookApp As Object
   
   Set OutlookApp = GetOutlookApp
   If OutlookApp Is Nothing Then
      MsgBox "Could not get handle for Outlook application"
      Exit Function
   End If
   
   C = OutlookApp.ActiveExplorer.Selection.Count
   
   If C > 1 Then
      MsgBox "Select only one email to attach"
   Else
   
      If TypeName(OutlookApp.ActiveExplorer.Selection(1)) = "MailItem" Then ' not meeting notice, etc.
      
      
         Set IncomingEmail = OutlookApp.ActiveExplorer.Selection(MailIndex)
         
         With IncomingEmail
            MailContent = _
               "From: " & .SenderName & " <" & .SenderEmailAddress & ">" & vbCrLf & _
               "Received: " & Format(.ReceivedTime, "m/d/yyyy h:mm AM/PM") & vbCrLf & _
               "Subject: " & .Subject & vbCrLf & vbCrLf & _
               .Body
         End With
         
      End If
      
   End If
   
End Function



Public Function GetOutlookApp() As Object

   Dim OutlookApp As Object
   
   Set OutlookApp = GetObject(, "Outlook.Application")

   Set GetOutlookApp = OutlookApp
   
End Function

 

[Satrapia]

Hi 6StringJazzer,
I thank you for the macro provided.
I encountered an error but chatGPT helped me to fix it (I do not know why the error - I mean, I am not questioning your work, I just described what I did...).
The macro is copying the text but not the picture and sometimes I have some answer in the picture.
It is also copying all the email, also the old ones and not only the last reply.

I am struggling understanding why you (all the answers above) are not getting my points using screenshots.
I mean, I know that this could not be the best solution for all but in this way I am able to read all the customer input without be able to modify them (so no one could questioning about the origin of the data looking at the picture, even myself that I have always fear to touch "original" input) and I can trace all the emails in one single columns (without additional click and waiting time to open the object or the email).
The data I have to take from the email are few numbers, so even the point of not be able to select the text for me is ok.
We are talking about 3-4 emails usually until a max of 10.
It is like the old fashion printing the emails but without all the papers on the desk.

Thanks to all for the input,

 

[6StringJazzer]

I am struggling understanding why you (all the answers above) are not getting my points using screenshots.

I get your points perfectly, I just don't agree with them. Also this is the first time you have mentioned pictures in the emails.

Here is one problem that you just cannot solve with screenshots:

Regarding the code to take screenshot, I was not able to find a way to include email header and the part of the email not showing in the monitor (in case of long email).

If you must capture screenshots and insert them to Excel, I'm sure there's a way to do it, but I don't know what it is so I'm not the guy who can help. I suspect this is going to require using SendKeys to create the screenshot.