Importing Outlook Data

L

Legacy 512328

Guest
Hi,

Apologies, I know this is a very long-winded and convoluted post, but it's hard to put into succinct words.

This query is both VBA related and Power Query related.

I'm trying to loop through emails in an Outlook folder and save and rename all attachments. I'd also like to modify the code to generate a list of emails in Excel. The code below does work; however, it seems to save far more attachments than I'd expect - I'm not sure if it's looping through all emails in a single thread/conversation and saving all attachments (duplicating saved attachments in the process). It's also picking up embedded images, such as images in email signatures (hence why I've added a Select statement to filter out jpg and png files - but there may be some image files that are saved as genuine attachments that I need to save, so I'm not sure how to approach it - the Select statement might not be the best approach).

When I generate a list of emails in Excel, emails are listed as a single conversation/email thread - are emails counted by conversation or individually - in other words, is an email thread with five emails considered a single mail item, or is each individual email in the thread a separate mail item in its own right?

Code:

Sub SaveAttachments()

With Application

.DisplayAlerts = False

.ScreenUpdating = False

End With

Dim ol As Outlook.Application

Dim ns As Outlook.Namespace

Dim fol As Outlook.Folder

Dim mi As Outlook.MailItem

Dim path As String

path = Path (substitute desired filepath)

Dim fso As FileSystemObject

Set fso = New FileSystemObject

Dim saveloc As Scripting.Folder

Set saveloc = fso.GetFolder(path)

Set ol = New Outlook.Application

Set ns = ol.GetNamespace("MAPI")

Set fol = ns.Folders(substitute desired folder).Folders(substitute desired folder)

Dim Attachment As Outlook.Attachment

Dim nextrow As Long

Dim attExt As String

nextrow = Range("A" & Rows.Count).End(xlUp).Row + 1

On Error Resume Next

For Each mi In fol.Items

If mi.Sender = SearchCriteria (substitute desired search criteria) Then

If mi.attachments.Count > 0 Then

For Each Attachment In mi.attachments

attExt = fso.GetExtensionName(Attachment.Filename)

Select Case attExt

Case "jpg", "png"

Case Else

Range("A" & nextrow).Value = Format(mi.ReceivedTime, "yyyymmdd") & "_" & Attachment.Filename

Range("B" & nextrow).Value = mi.SubjectRange

("C" & nextrow).Value = mi.ReceivedTimeAttachment.SaveAsFile saveloc & "\" & Format(mi.ReceivedTime, "yyyymmdd") & "_" & Attachment.Filename

nextrow = nextrow + 1

End Select

Next Attachment

End If

End If

Next mi

End Sub

When I use Power Query for the same task, I can successfully generate a list of my emails in Excel; however, the count of emails in my mailbox in Excel doesn't match the count of emails in Outlook. The count in Excel always seems to be less than the count in Outlook. I've tried this approach on multiple folders and some shared mailboxes and this is always the case. Again, I think this may be down to how the count is calculated on conversations - is an email thread with five emails considered a single mail item, or is each individual email in the thread a separate mail item in its own right? Also unlike the VBA code above, when I generate a list of emails with Power Query, emails are listed on an individual basis and not grouped together in conversations.

I'd be really grateful if anyone could help with my queries. I'm relatively new to both VBA and Power Query, so apologies if the answer is really obvious and straightforward. Thanks for your help.

Kind Regards,

Jack
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top