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
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
