Create a list of selected emails

1.      In Outlook, select the emails that you want to include in the list.
2.      Right-click on one of the selected emails and select "Copy" from the context menu.
3.      Open a blank spreadsheet in a program such as Microsoft Excel or Google Sheets.
4.      Right-click on the first cell in the spreadsheet and select "Paste" from the context menu. This will paste the list of selected emails into the spreadsheet.
5.      If you want to include specific information from the emails in the list, such as the sender, subject, and date, you can use the following formula to extract this information:
=MID(A1,FIND("FROM: ",A1)+6,FIND("SUBJECT: ",A1)-FIND("FROM: ",A1)-7)

This formula will extract the sender's email address from the first email in the list. You can modify the formula to extract other information, such as the subject or date, by changing the text that appears after the "FIND" function.

Hi,
I need info from the body, how can I get them?
see example below
Hot Tools Professional Black Gold Digital Flat Iron, 1 1/4 Inches
QTY 30
$38.22 ea
TAKE ALL
4-6 DAY LEAD TIME.

LINK TO AMAZON

 
Thanks

I have tried the below from Google but didn't work...

Go to the Microsoft Excel workbook and open the worksheet that you will embed the email message in, then click te Insert > Object. 3. In the Object dialog box, go to the Create from file tab, click the Browser button to find and select the email message that you haved saved in Step 1.

Hi,

in Powershell something like:

#Get all Inbox emails from specific vendor
$emails = Get-Mailbox -Filter { FromAddress -like "*vendor@example.com*" }

#Create an empty array to contain all the emails
$emailDetails = @()

#Loop through each email
foreach($email in $emails){
    #Get the body of the email
    $body = Get-MessageContent -Identity $email.Identity
    #Split the body into lines
    $lines = $body.Split("`n")
    #Split each line into words
    foreach($line in $lines){
        $words = $line.Split("`t")
        #Create a new array with the details from the email
        $details = [pscustomobject]@{
            Description = $words[0]
            MQO = $words[1]
            Price = $words[2]
            Link = $words[3]
        }
        #Add the details to the emailDetails array
        $emailDetails += $details
    }
}

#Export the details to an Excel file
$emailDetails | Export-Csv -Path "C:\Path\To\File.csv" -NoTypeInformation

Select allOpen in new window

Cheers

#Get all Inbox emails from specific vendor

How do I use this for specific folder, not in the inbox folder?

Since I would prefer solution in VBA, how about these sites.
https://stackoverflow.com/questions/18796071/extract-body-text-from-outlook
https://www.extendoffice.com/documents/outlook/5209-outlook-export-body-to-excel.html

Hi,

by all means:

Dim emails As Variant
Dim emailDetails As Variant
Dim body As Variant
Dim lines As Variant
Dim words As Variant
Dim details As Variant

'Get all Inbox emails from specific vendor
Set emails = Get-Mailbox -Filter { FromAddress -like "*vendor@example.com*" }

'Create an empty array to contain all the emails
Set emailDetails = Array()

'Loop through each email
For Each email In emails
    'Get the body of the email
    Set body = Get-MessageContent -Identity email.Identity
    'Split the body into lines
    Set lines = Split(body, "`n")
    'Split each line into words
    For Each line In lines
        Set words = Split(line, "`t")
        'Create a new array with the details from the email
        Set details = CreateObject("Scripting.Dictionary")
        details.Add "Description", words(0)
        details.Add "MQO", words(1)
        details.Add "Price", words(2)
        details.Add "Link", words(3)
        'Add the details to the emailDetails array
        emailDetails.Add details
    Next
Next

'Export the details to an Excel file
emailDetails.Export "C:\Path\To\File.csv", -NoTypeInformation

Select allOpen in new window

Cheers

@DFKE,
what about the question ?above

Ho,
See error I get when pasted in Access VBA editor.

Untitled.png

Will code posted here do the job?
https://officetricks.com/outlook-email-download-to-excel/

Actually to keep this simple, I need the code below to extract the following from the body.

ASIN: B07DFMGB2G
Unit Price: $5.00

MOQ: 20

Quantity Available: 60

Thanks

Public Sub ExtractDetailsFromOutlookFolderEmails()
  On Error GoTo errorHandler
  
  Dim olApp As Outlook.Application
  Dim olNameSpace As Namespace
  Dim olFolder As Folder
  Dim olItem As Object
  Dim olItemBody As String
  Dim olHTML As MSHTML.HTMLDocument
  Dim olElementCollection As MSHTML.IHTMLElementCollection
  Dim htmlElementTable As IHTMLElement
  Dim iTableRow As Integer
  Dim iTableColumn As Integer
  Dim wksList As Worksheet
  Dim iRow As Integer
  Dim iColumn As Integer
  Dim strFullName As String
  
  Set wksList = ThisWorkbook.Sheets("Sheet1")
  
  Set olApp = CreateObject("Outlook.Application")
  Set olNameSpace = olApp.GetNamespace("MAPI")
  Set olFolder = olNameSpace.GetDefaultFolder(olFolderInbox).Folders("Automation")
  
  iRow = 1
  For Each olItem In olFolder.Items
    If InStr(olItem.Subject, "Template Email Subject") > 0 Then
      iColumn = 1
      
      'Begin extracting the full name from the e-mail body text:  The account for LAST NAME, FIRST NAME has been created.
      olItemBody = Replace(olItem.body, "The account for", "####################")
      olItemBody = Replace(olItemBody, "has been created.", "####################")
      
      strFullName = Trim(Replace(Replace(Replace(Split(olItemBody, "####################")(1), Chr(10), ""), Chr(12), ""), Chr(13), ""))
  
      wksList.Cells(iRow, iColumn) = strFullName
      'End full name extract.
      
      'Begin extracting table data.
      Set olHTML = New MSHTML.HTMLDocument
      olHTML.body.innerHTML = olItem.HTMLBody
      
      Set olElementCollection = olHTML.getElementsByTagName("table")
      
      For Each htmlElementTable In olElementCollection
        For iTableRow = 0 To htmlElementTable.Rows.Length - 1
          For iTableColumn = 0 To htmlElementTable.Rows(iTableRow).Cells.Length - 1
            On Error Resume Next
            'Only extract data from the second column in the table since the first column is a header.
            If iTableColumn Mod 2 = 1 Then
              iColumn = iColumn + 1
              wksList.Cells(iRow, iColumn).Value = htmlElementTable.Rows(iTableRow).Cells(iTableColumn).innerText
            End If
            On Error GoTo errorHandler
          Next iTableColumn
        Next iTableRow
      Next htmlElementTable
      'End extracting table data.
      
      iRow = iRow + 1
    End If
  Next olItem
  
errorHandler:
  If Err.Number <> 0 Then
    MsgBox "Error extracting details. [ExtractDetailsFromOutlookFolderEmails]"
  End If
End Sub

Select allOpen in new window

Hi,
I see if i use the export utility from outlook I can get it to a csv file.
Now my question is
How do i extract only info needed, for example
from the below, I need
ASIN: B07XVV4TCZ
Unit Price: $2.00
Quantity Available: 55 (take all)

" <https://click.mlsend.com/link/o/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OQ==.hmtM1e4X4oHS1OLt4WAVMola61yHcyQsJ3P1WElm3GA> 
	
	View in browser <https://click.mlsend.com/link/c/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OSZiPTEwNjMwMjEzNjgmZD1sNXE4YjJl.q7vsykgBIWHHAn7vJCCmQBeK4pOJldaOjKuq818qIzg>  	
	
	

REVLON Super Lustrous Glass Shine Lipstick, Flawless Moisturizing Lip Color with Aloe, Hyaluronic Acid and Rose Quartz, Sparkling Honey (006), 0.15 oz

ASIN: B07XVVFCKV <https://click.mlsend.com/link/c/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OSZiPTEwNjMwMjEzNzAmZD1rMnEyejRj.t3IylwWIfJZ87gEUPhNvzuGN0hiBk7oRkVYPq_IQ7ks> 

Unit Price: $2.00


Quantity Available: 55 (take all)




 <https://click.mlsend.com/link/c/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OSZiPTEwNjMwMjEzNzImZD1wNGE0Zzdh.N-3nouGVVubu4TbaJbCAoLRP4PPPfRtczBUV0Flbkk8> 	
	

REVLON Super Lustrous Glass Shine Lipstick, Flawless Moisturizing Lip Color with Aloe, Hyaluronic Acid and Rose Quartz, Glistening Purple (011), 0.15 oz

ASIN: B07XVV4TCZ <https://click.mlsend.com/link/c/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OSZiPTEwNjMwMjEzNzQmZD11MXk3bTBy.7edOPDQ7OHNdSiQRmD-ODp_qZhPnzQ5-dmCFkF0RDTg> 

Unit Price: $2.00


Quantity Available: 90 (take all)




 <https://click.mlsend.com/link/c/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OSZiPTEwNjMwMjEzNzYmZD1kMWExZDNk.E51jVRdn5oe7D0Ugui7j_XJqkJmAtrrqa-GUWOwVkFw> 	
	


------------------------------

If interested, simply reply to this offer email




	



	
Unsubscribe <https://click.mlsend.com/link/c/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OSZiPTEwNjMwMjE0MjUmZD16MHg2ZjJw.gIoUwufOc9Pl79VGWI83KwMI3V9TcBNFKCIs4zmwkvo> 	
	
	
 <https://click.mlsend.com/link/c/YT0yMTI0OTYxMDUzNDAwODk3MDU3JmM9cTdsMCZlPTcwNDU5OSZiPTEwNjMwMjEzNzgmZD12NHM3azlt.vp-rcWtjK0P28BfwSkCjHC7oikqYS21omW8Jb-_sWtE> 	
	
"

Select allOpen in new window

thanks

Hi Ben,

Although the text you have shown above is no CSV but only a text file, here is some VBA to parse the file to get the information you want:

Option Explicit

Private colArticles As Collection

Public Sub ParseLines(strLines() As String)
    Dim objArticle As clsArticle
    Dim bolSaveArticle As Boolean
    
    Dim i As Long
    
    Set colArticles = New Collection
    
    For i = 0 To UBound(strLines)
        Select Case True
            Case strLines(i) Like "ASIN*"
                Set objArticle = New clsArticle
                objArticle.ASIN = Split(strLines(i), " ")(1)
            Case strLines(i) Like "Unit Price*" And Not objArticle Is Nothing
                objArticle.UnitPrice = Val(Mid(Split(strLines(i), " ")(2), 2))
            Case strLines(i) Like "Quantity Available*" And Not objArticle Is Nothing
                objArticle.Quantity = Split(strLines(i), " ")(2)
                If UBound(Split(strLines(i), " ")) > 2 Then
                    objArticle.Options = Right(strLines(i), Len(strLines(i)) - InStrRev(strLines(i), "(") + 1)
                End If
                bolSaveArticle = True
            Case Not objArticle Is Nothing And bolSaveArticle
                colArticles.Add objArticle, objArticle.ASIN
                Set objArticle = Nothing
                bolSaveArticle = False
        End Select
    Next
    
End Sub

Public Sub TestParseBody()
    Dim strFile As String
    Dim strLine As String
    Dim strLines() As String
    Dim i As Long
    Dim objArticle As clsArticle
    
    Dim f As Integer
    strFile = "PathToYourFile\Mailbody.txt"
    
    f = FreeFile
    
    Open strFile For Input As #f
    
    Do
        Line Input #f, strLine
        ReDim Preserve strLines(i)
        strLines(i) = strLine
        i = i + 1
    Loop Until EOF(f)
    
    Close #f
    
    ParseLines strLines
    
    For i = 1 To colArticles.Count
        Set objArticle = colArticles(i)
        With objArticle
            Debug.Print "ASIN: " & .ASIN, "Unit Price: " & .UnitPrice, "Quantity: " & .Quantity
        End With
    Next
End Sub

Select allOpen in new window

Additionally you need to add a class module and name it "clsArticle":

Option Explicit

Public ASIN As String
Public UnitPrice As Currency
Public Quantity As Long
Public Options As String

Select allOpen in new window

In the end you get a collection which contains objects of the clsArticle class where each has a property for the desired values.

Instead of Debug.Print you could then insert the values into a database table.

Of course you need to make sure that the format is always the same and that all 3 rows exists: "ASIN", "Unit Price" and "Quantity Available" for each article in the mail body.

Hope that helps to integrate it into your mail code.

Cheers,

Christian

Hi Bit,
Good to know you're still in business-:)
See below error I got.
Thanks

Untitled.png

Although the text you have shown above is no CSV but only a text file,

I may have copied data from one csv cell into notepad in order to view full contents, but data comes from csv file.

I have followed Google's recommendation...
How to Export Outlook Emails to Excel?
Open Outlook >> click on "File" >> and select "Open and Export"
Click on "Import/Export" >> select "Export to a file" >> and select Excel or csv as the file type.
Select a destination folder to save the file in.
Click "Finish"

Hi,
Disregard previous comment.
It compiled, however getting the attached error on line below.

            Case Not objArticle Is Nothing And bolSaveArticle
                colArticles.Add objArticle, objArticle.ASIN

Select allOpen in new window

Untitled.png

This is how the file looks like.

Untitled.png

Hi,

following up on your request about being able to use it for a specific folder.

Dim emails As Variant
Dim emailDetails As Variant
Dim body As Variant
Dim lines As Variant
Dim words As Variant
Dim details As Variant

'Get all emails from the specific folder and vendor
Set emails = Search-Mailbox -Identity <MailboxName> -SearchQuery 'From:"vendor@example.com" AND folder:"<FolderName>"'

'Create an empty array to contain all the emails
Set emailDetails = Array()

'Loop through each email
For Each email In emails
    'Get the body of the email
    Set body = Get-MessageContent -Identity email.Identity
    'Split the body into lines
    Set lines = Split(body, "`n")
    'Split each line into words
    For Each line In lines
        Set words = Split(line, "`t")
        'Create a new array with the details from the email
        Set details = CreateObject("Scripting.Dictionary")
        details.Add "Description", words(0)
        details.Add "MQO", words(1)
        details.Add "Price", words(2)
        details.Add "Link", words(3)
        'Add the details to the emailDetails array
        emailDetails.Add details
    Next
Next

'Export the details to an Excel file
emailDetails.Export "C:\Path\To\File.csv", -NoTypeInformation

Select allOpen in new window

Specify the folder name in the -SearchQuery parameter.


Cheers

Thank you very much Bit!

Just one Q.
Some emails have them as
Unit Pack Price: $16.50
instead of just Unit Price.
How do I handle that?

Also when pasting contents of debug into Excel, it gives me all in one column (see attached), how can I change that in VBA, having each value going into a separate column, so I don't have to play around each time in Excel in order to split them...?
Thanks

Untitled.png

Hi Ben,

you can always adjust the "Case" rows to fit your needs:

strLines(i) Like "Unit Price*" And Not objArticle Is Nothing

Select allOpen in new window

change to:

strLines(i) Like "Unit* Price*" And Not objArticle Is Nothing

Select allOpen in new window

For more possibilities of Like in VBA look here:
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/like-operator

Cheers,

Christian

Hi Bit,
See related Q here, let me know if its feasible (for u I believe everything is-:)
https://www.experts-exchange.com/questions/29254198/Extract-Body-of-emails-into-Excel-2.html
Thanks