Macro to read email and update spreadsheet

[bobbybrown]

Hi everyone,
I have been searching for a solution to my task for a little while now but haven't found anything that would help.
I have a spreadsheet that at the moment I am having to update manually. The sheet records sales of items on ebay.
I sell 2 types of items and when I sell an item, I open the sheet and increase the number sold for that particular item.
This then increases the number sold for that particular item and the sheet works everything out itself from that point.

What I am trying to do is....Have excel read an email (I can route them through outlook that isn't a problem) and update the sheet.

What it would need to do is scan the inbox for emails from ebay and find the relevant text to decide if its product 1 or prouduct 2, check the amount sold then amend the cell on the spreadsheet by adding that number.

For example, if my sheet says I have sold 5 of item 1, I then sell 3 of item 1 and the email comes through, the sheet should automatically update the total sold to 8 items.

Can anyone offer any advice on this? Is it possible?

Many thanks for the time you have taken to read this message.

 

[bobbybrown]

Many thanks John, I will put that in to a test sheet first thing in the morning and give it a whirl. I will report back!

 

[bobbybrown]

All added and tested, working well!
Many thanks to you John.

 

[bobbybrown]

One quick question John, I've added an auto run VBS file to my desktop to have the file auto run and save however it's giving me a prompt to ask if I want to save the changes to the file and I have to manually say yes, the code I'm using for this is below, can you see anything that is causing the save dialog pop up? On another sheet I have from a while ago this vbs file opens the file, runs the macro and saves the changes automatically. Obviously on the example below I have changed the paths file names and macro names.

Any help appreciated

kind regards

Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample() 
	Dim xlApp 
	Dim xlBook
	Dim myPath
	Dim myFile
	myPath = "c:\PATH\"
	If Right(myPath,1)<>"\" Then myPath = Trim(myPath) & "\"
	myFile = "FILENAME.xlsm"
	Set xlApp = CreateObject("Excel.Application") 
	Set xlBook = xlApp.Workbooks.Open(myPath & myFile, 0) 
	xlApp.Run "MACRONAME"
	xlApp.Quit 
	Set xlBook = Nothing 
	Set xlApp = Nothing 
End Sub

 

[John_w]

Between the Run and Quit try putting:

xlBook.Save

 

[bobbybrown]

Absolutely perfect! Many thanks!

 

[John_w]

You're welcome. Glad I could help.

 

[Chasedward]

Yes, this is certainly possible, using the Outlook object model, so you'll have to receive the emails in Outlook.

See if the following code gets you started. It is just basic code for looping through emails in the Outlook Inbox with "@ebay" in the sender's email address and displaying the email body text.

Some things you will need to define and change the code to handle:

1. What is the specific sheet and cell addresses of the 2 product sold counts?

2. How to find or identify the number sold in the email body text.

3. How to prevent Inbox emails being processed again when you run the code again. There are 3 methods I can think of: you manually move emails to another folder; or the code automatically moves emails to another folder; or the code looks at the email received time and processes only those emails received after the last run time.

Put the following code in a standard module. In the VBA editor, click Tools -> References and tick "Microsoft Outlook xx.0 Object Library", where xx.0 is the Outlook version.

Public Sub Read_Outlook_Emails()

    Dim outApp As Outlook.Application
    Dim outNs As Outlook.Namespace
    Dim outFolder As Outlook.MAPIFolder
    Dim outItem As Object
    Dim outMail As Outlook.MailItem
   
    Set outApp = New Outlook.Application
    Set outNs = outApp.GetNamespace("MAPI")
   
    'Get Inbox folder
   
    Set outFolder = outNs.GetDefaultFolder(olFolderInbox)
       
    'Loop through emails in Inbox
   
    For Each outItem In outFolder.Items
   
        If outItem.Class = Outlook.OlObjectClass.olMail Then
           
            Set outMail = outItem
           
            If InStr(outMail.SenderEmailAddress, "@ebay") > 0 Then
                MsgBox outMail.Body
            End If

        End If
    Next
   
    MsgBox "Finished"
   
End Sub

Hi John,

I am looking for something similar to this, but cannot work out from the above code, how it is updating the spreadsheet. I have emails coming in whereby I need it to check the title of the email and update a column in a spreadsheet with the date dependant on the title. If that makes sense. I am getting hundreds of email back for one order whereby I am having to update a spreadsheet manually.

Thanks Dawn

 

[John_w]

cannot work out from the above code, how it is updating the spreadsheet

You're looking at the wrong code. This code updates the worksheet cells, according to the OP's requirement.

Macro to read email and update spreadsheet

Hi everyone, I have been searching for a solution to my task for a little while now but haven't found anything that would help. I have a spreadsheet that at the moment I am having to update manually. The sheet records sales of items on ebay. I sell 2 types of items and when I sell an item, I...

www.mrexcel.com

If you still need help, please start your own thread with a detailed description of your requirement.

 

[AedmiO]

Absolutely perfect! Many thanks!

Hello bobbybrown,

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 and think that 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. That's why I'd like to ask if you could send me the example email (like you did to john) and maybe also the spreadsheet ( with anonymized details if you need to) so that I could understand the code and maybe modify it to suit my needs. If you don't have the said email and/or spreadsheet anymore, I'd like to ask if you still use the same macro or a similar macro. If that is the case, would it be possible for you to send me the example email and the spreadsheet you are currently using and if possible also the current macro.

Any advice is welcome and thank you for your time.

 

[bobbybrown]

Hello bobbybrown,

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 and think that 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. That's why I'd like to ask if you could send me the example email (like you did to john) and maybe also the spreadsheet ( with anonymized details if you need to) so that I could understand the code and maybe modify it to suit my needs. If you don't have the said email and/or spreadsheet anymore, I'd like to ask if you still use the same macro or a similar macro. If that is the case, would it be possible for you to send me the example email and the spreadsheet you are currently using and if possible also the current macro.

Any advice is welcome and thank you for your time.

Hi,
This was 6 years ago now and I no longer work where I did when this was needed. The company switched the emails to Google a couple of years back so it stopped working anyway. Sadly I don’t have access to the laptop it was stored on back then as the hard drive died a while ago. Everything was in this thread though from what I recall.