Forum

Importing Outlook D...
 
Notifications
Clear all

Importing Outlook Data to Excel

2 Posts
2 Users
0 Reactions
93 Views
(@Anonymous)
Posts: 0
New Member 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

 
Posted : 28/11/2023 12:17 pm
(@catalinb)
Posts: 1937
Member Admin
 

Hi Jack,
Email items are just grouped into a conversation, but they are still individual items.

image_2023-12-05_074435918.png

If you want to keep some images and there is no specific naming convention to identify the ones you need, maybe you can work on Attachments tables. In these tables (Power Query), you can filter images that are attached as files, not InLine like signature images are, the IsInline attribute should be FALSE for these attachments.

 
Posted : 06/12/2023 1:50 am
Share: