AedmiO
New Member
- Joined
- Jul 28, 2021
- Messages
- 5
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
- Mobile
- Web
Hello everyone,
I'm currently doing a little project and would like to automatically update an Excel sheet based on the information taken from an Outlook mail from the Inbox folder using Outlook VBA. So while doing my research on the topic, I found this topic Macro to read email and update spreadsheet and think that it is kind of similar to what I'm looking for. The problem is what I want to do is a little bit different. First there will be more than two cells to be modified and they will be on different rows and different columns. Secondly, the e-mail that I will receive may look and work differently. I'm new to VBA and checked some thread on my topic and some youtube videos to learn VBA and I'm also trying to learn the specific stuffs linked to what I'm want to do. That's why I'd like to ask if you could guys help me (by providing me a code that performs what I'm looking for, whether it is complete or not) or give me directions to anything you deem worthful to check so that I could understand the new code or the code in the linked topic and maybe modify it to suit my needs.
I've linked several photos of the Excel sheet and the Outlook mail (with made-up data ) so that you could understand my situation a little bit better. There is also the final code used by the linked url's original poster to resolve his problem.
The data that will be updated are the end and start dates, the end and start hours, the service provider and the severity level. the devices whose data will be updated are the ones whose cells are filled in red.
I don't really know how the mail of the guy in the linked topic looks or works but if you have any idea on how my mail should look or works to make my objective possible, feel free to share it. The same thing applies to my excel sheet if I need to make some adjustment to make it works.
Any advice is welcome and thank you for your time.
I'm currently doing a little project and would like to automatically update an Excel sheet based on the information taken from an Outlook mail from the Inbox folder using Outlook VBA. So while doing my research on the topic, I found this topic Macro to read email and update spreadsheet and think that it is kind of similar to what I'm looking for. The problem is what I want to do is a little bit different. First there will be more than two cells to be modified and they will be on different rows and different columns. Secondly, the e-mail that I will receive may look and work differently. I'm new to VBA and checked some thread on my topic and some youtube videos to learn VBA and I'm also trying to learn the specific stuffs linked to what I'm want to do. That's why I'd like to ask if you could guys help me (by providing me a code that performs what I'm looking for, whether it is complete or not) or give me directions to anything you deem worthful to check so that I could understand the new code or the code in the linked topic and maybe modify it to suit my needs.
I've linked several photos of the Excel sheet and the Outlook mail (with made-up data ) so that you could understand my situation a little bit better. There is also the final code used by the linked url's original poster to resolve his problem.
The data that will be updated are the end and start dates, the end and start hours, the service provider and the severity level. the devices whose data will be updated are the ones whose cells are filled in red.
I don't really know how the mail of the guy in the linked topic looks or works but if you have any idea on how my mail should look or works to make my objective possible, feel free to share it. The same thing applies to my excel sheet if I need to make some adjustment to make it works.
VBA Code:
Public Sub Update_Products_Sold()
Dim outApp As Outlook.Application
Dim outNs As Outlook.Namespace
Dim outFolder As Outlook.MAPIFolder
Dim outItem As Object
Dim outMail As Outlook.MailItem
Dim product1 As Range, product2 As Range
Dim lastRunReceivedTime As Range, latestReceivedTime As Date
Dim parts As Variant, quantitySold As Integer
Dim numEmailsFound As Integer
With ThisWorkbook.Worksheets("Overview")
Set product1 = .Range("F5")
Set product2 = .Range("F7")
Set lastRunReceivedTime = .Range("B1")
End With
Set outApp = New Outlook.Application
Set outNs = outApp.GetNamespace("MAPI")
'Get ebay subfolder within Inbox folder
Set outFolder = outNs.GetDefaultFolder(olFolderInbox).Folders("ebay")
'Loop through emails
latestReceivedTime = lastRunReceivedTime.Value
numEmailsFound = 0
For Each outItem In outFolder.Items
If outItem.Class = Outlook.OlObjectClass.olMail Then
Set outMail = outItem
'Is this email from ebay and received after the latest received time of the last run?
If outMail.ReceivedTime > lastRunReceivedTime.Value And _
InStr(1, outMail.SenderEmailAddress, "@ebay", vbTextCompare) > 0 Then
'Yes, so extract quantity sold, identify product type in subject and update appropriate Excel cell
'MsgBox outMail.Body, Title:=outMail.Subject
parts = Split(outMail.Body, "Quantity sold:")
quantitySold = Split(parts(1), vbCrLf)(0)
If InStr(1, outMail.Subject, "LEATHER", vbTextCompare) > 0 Then
product1.Value = product1.Value + quantitySold
numEmailsFound = numEmailsFound + 1
ElseIf InStr(1, outMail.Subject, "PVC", vbTextCompare) > 0 Then
product2.Value = product2.Value + quantitySold
numEmailsFound = numEmailsFound + 1
End If
'Update latest received time
If outMail.ReceivedTime > latestReceivedTime Then latestReceivedTime = outMail.ReceivedTime
End If
End If
Next
'Update cell containing latest received time of an ebay email
lastRunReceivedTime.Value = latestReceivedTime
lastRunReceivedTime.NumberFormat = "dd/mm/yyyy hh:mm:ss"
MsgBox "Finished." & vbNewLine & "Number of emails found = " & numEmailsFound
End Sub
Any advice is welcome and thank you for your time.