2

I am currently working on my project which is to export emails from Outlook to my Excel file.

My current code is only exporting texts and not images. Some of my emails have a snipped images (.png/.jpg).

Is there a fix to this?

Here's my current code on Excel:

Sub getDataFromOutlook()

'~~Declarations~~'
Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

'~~Set email to be saved~~'
Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("SAMPLE")

i = 1


For Each OutlookMail In Folder.Items
'~~Write to Excel~~'
If OutlookMail.ReceivedTime >= Range("email_Receipt_Date").Value Then
    Range("email_Subject").Offset(i, 0).Value = OutlookMail.Subject
    Range("email_Subject").Offset(i, 0).Columns.AutoFit
    Range("email_Subject").Offset(i, 0).VerticalAlignment = xlTop
    Range("email_Date").Offset(i, 0).Value = OutlookMail.ReceivedTime
    Range("email_Date").Offset(i, 0).Columns.AutoFit
    Range("email_Date").Offset(i, 0).VerticalAlignment = xlTop
    Range("email_Sender").Offset(i, 0).Value = OutlookMail.SenderName
    Range("email_Sender").Offset(i, 0).Columns.AutoFit
    Range("email_Sender").Offset(i, 0).VerticalAlignment = xlTop
    Range("email_Body").Offset(i, 0).Value = OutlookMail.Body
    Range("email_Body").Offset(i, 0).Columns.AutoFit
    Range("email_Body").Offset(i, 0).VerticalAlignment = xlTop

    i = i + 1
End If

Next OutlookMail

'~~Set to Null~~'
Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Jeffrey Bernardo
  • 47
  • 6
  • If the images are inline of the email it may be that they are actually html IMG tags and link to a website. If that is the case you can read the html tag and download the image. – Andreas Aug 26 '18 at 07:21
  • Youre right, these images are inline with the email's body. But it doesnt have a link to redirect on other sites. It is just a screenshot. – Jeffrey Bernardo Aug 26 '18 at 09:16
  • In that case maybe it's a base64 image? I haven't looked at the html of a message with inline images like that. I can have a look at it tomorrow at work, I also have a VBA script that parse and save attachments and images (but mine are weblink). But I'll see what I can do tomorrow – Andreas Aug 26 '18 at 16:49
  • I found some mentions that if you export the email as rich text it makes the inline images accessible as attachments, maybe try that. – n8. Aug 27 '18 at 17:20

2 Answers2

0

Have a look at this post:

How to copy Outlook mail message into excel using VBA or Macros

I think it should give you what you need (scroll down toward the bottom).

swmcdonnell
  • 1,381
  • 9
  • 22
0

Have you tried Attachments? I haven't had the need to do this but that's where I'd put my initial effort.

MailItem.Attachments.Item(0) let's you work with the first attachment.

Since your variable OutlookMail holds a MailItem, the first thing I would try is...

ActiveSheet.Pictures.Insert(OutlookMail.Attachments.Item(0))

The second thing I would try is searching for an answer.

Pulling pictures from emails and inserting pictures into Excel are not obscure tasks so there will be many detailed examples available to those who look for them.

I'm reminded of my grandmother's response when I'd ask if my skateboard was in the front yard. "Look with your eyes, not your mouth", is what she'd tell me. That feels appropriate here.

ProfoundlyOblivious
  • 1,455
  • 1
  • 6
  • 12
  • I believe this only exports ATTACHED images from the email. What i am struggling is to export the image on the email's body. I looked on this blog [https://www.datanumen.com/blogs/quickly-export-image-attachments-outlook-email-excel-worksheet/] only attached images are exporting. I'll try again tomorrow, i am currently on vacation lol. Thanks Grandpa! :P – Jeffrey Bernardo Aug 26 '18 at 08:55
  • you could be right.... i haven't bothered finding out. but i did made make a tool to help my ap guy download attached invoices. mostly pdf, doc, a few xls, occasionally an htm, once in a while an odd duck snaps a pic of the invoice with his phone and attached. the thing is, he came to me saying the tool is picking up company logos. so i took a look and sure enough, i see a bunch of jpg's, png's and gif's. i peruse his inbox, couldn't find a single one as an attachment. perhaps sigs have a stealth attachment, maybe they all snuck in on one htm, or maybe your not right. have you tried? – ProfoundlyOblivious Aug 26 '18 at 21:35