Export Attachment from outlook to excel cells

0 votes

I'm having trouble getting attachments out of Outlook and into Excel cells. The file itself, not the filename, is the attachment. For instance, if the file is a PDF, only the PDF itself—not the filename or the contents inside the PDF—will be extracted to the cells. I am familiar with saving attachments to folders, but not cells. This is the code:

Sub GetOutlookDetails()

Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olFldr As Outlook.MAPIFolder
Dim olItem As Object
Dim olMailItem As Outlook.MailItem
Dim ws As Worksheet
Dim iRow As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim lastrow As Long

Set olApp = New Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")

'Set Location Mailbox
Set olFldr = olNS.Folders("Cash Allocations UKI")
Set olFldr = olFldr.Folders("Inbox")
Set olFldr = olFldr.Folders("GB - United Kingdom")

iRow = 5

Application.ScreenUpdating = False

'Find Unread email only in Mailbox
For Each olItem In olFldr.Items

If olItem.UnRead = True Then
    If olItem.Class = olMail Then
    Set olMailItem = olItem
        With olMailItem
            ws.Cells(iRow, "A") = .SenderEmailAddress
            ws.Cells(iRow, "B") = .Subject
            ws.Cells(iRow, "C") = .Body
            iRow = iRow + 1
        End With
    
    End If
    End If

Next olItem
Application.ScreenUpdating = False

'Remove Wrap Text
Columns("C:C").Select
With Selection
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
With Selection
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Range("A5").Select

'To put "."
lastrow = ThisWorkbook.Worksheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
Range("D5:D" & lastrow) = "."
End Sub

the idea is to embed the attachment received in each emails to column E

ws.Cells(iRow, "E") = .Attachments 'Stuck here
Apr 3, 2023 in Others by Kithuzzz
• 38,010 points
267 views

1 answer to this question.

0 votes

You will need to save the attachment as a file (Attachment.SaveAsFile, where Attachment object comes from the MailItem.Attachments collection), then insert it as an object using Worksheet.OLEObjects.Add. See https://www.howtoexcel.org/embed-pdf/ for more details.

answered Apr 3, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,714 views
0 votes
1 answer

Change date format of cell in excel from dd.mm.yyyy to yyy/mm/dd ( excel version 2013 )

Hello :)   Excel’s Format Cells function can quickly ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
1,244 views
0 votes
1 answer

How to import data from a HTML table on a website to excel?

Hello  To import any HTML file in excel there ...READ MORE

answered Feb 10, 2022 in Others by gaurav
• 23,260 points
5,970 views
0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
13,126 views
0 votes
1 answer

VBA How to extract the date and time of arrival of a answered email

Use MailItem.ReceivedTime property. I hope this helps you ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,420 points
1,985 views
0 votes
1 answer

Filter outlook sent items in vba failing for emails with multiple recipients

The PR DISPLAY TO parameter provides a ...READ MORE

answered Feb 21, 2023 in Others by narikkadan
• 63,420 points
428 views
0 votes
1 answer

Embed picture in outlook mail body excel vba

The image needs to be added and ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 63,420 points
4,300 views
0 votes
1 answer

Error populating email body from word documents

There is no need to use late ...READ MORE

answered Jan 15, 2023 in Others by narikkadan
• 63,420 points
819 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
440 views
0 votes
1 answer

How to remove borders from cells in a range in Excel using VB.net?

range.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,420 points
1,024 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP