Excle VBA code to access outlook?

[williamhowar]

Hello,
I am writing a macro to automate some office work. We have multiple csvs that we get on mail. I am trying to create a macro that


Opens outlook -> searches in the subject line -> opens email -> opens attachment.


Would someone help me with some resources for this that would explain the logic behind it too?

[georgiboy]

Welcome to the forum williamhowar,

The below is a piece of code that I use, I run it from Excel, it loops through all emails in a specific folder of a shared mailbox. It looks at each email and then loops through the attachments of those emails, if it finds an attachment with specific words in the filename then it will save that file to a folder set in the code as 'FilePath', it will then mark that email as read.

I know it's not exactly what you are looking for but you should be able to modify it to suit your needs, to look at subject line and search for a string, open the file etc. Give us another shout if you need help.


Sub AttachmentSave()
Dim wb As Workbook, cnt As Long, atmt As Object, omailitem As Object
Dim olNS As Outlook.Namespace, olMailbox As Outlook.Folder, olInbox As Outlook.Folder, inFol As Outlook.Folder


Set olNS = Outlook.GetNamespace("MAPI") ' loads all folders/ mailbox's
Set olMailbox = olNS.Session.Folders.Item("Distribution Returns") ' shared mailbox
Set olInbox = olMailbox.Folders.Item("Inbox") ' inbox of shared mailbox
Set inFol = olMailbox.Folders.Item("Return Notification") ' folder to be searched
Set wb = ThisWorkbook ' this workbook set to wb
FilePath = wb.Path & "\Return Downloads\" ' filepath to save attachments to


' checks if the outlook folder is empty, if it is then exit the sub
If inFol.Items.Count < 1 Then
MsgBox "There are no mails to look at in the 'Return Notification' Outlook folder", vbExclamation, "Error"
Exit Sub
End If


For Each omailitem In inFol.Items ' loop through the emails
For Each atmt In omailitem.Attachments ' loop through the attachments
If InStr(LCase(atmt.DisplayName), "Search String") Then ' checks if the words "Search String" exist in the attachment name
cnt = cnt + 1 ' increments cnt by one (just a count)
' saves the attachment in a specific format with the cnt in the filename, saved with a xlsm extension
atmt.SaveAsFile FilePath & Format(Now(), "yyyymmddhhmmss") & "-" & cnt & ".xlsm"
End If
Next
omailitem.UnRead = False ' mark the email as unread
Next
End Sub

[Gasman]

Not sure you can open the attachment (more than happy to be proved wrong), but would need to save it to file and then open that?
I have some code somewhere that runs in outlook, that saves attachments to file.

I could go looking for it, if you think that is the way you will go.?

[williamhowar]

Thanks for your answer. I got it.

fnaf 2 (https://fnaf2.io)