Excellent video. I was very useful, thank you very much for your contributions. For users who are not processed by the macro, review the following: 1) If you make a copy and paste of the web page, you must change the italic quotes for ("") 2) Check the defined cell name in Excel, it must be identical to the macro. Note that the date cell (B1) has a defined name, it is not in (A1) (1:25) 3) The location of the "ImpMail" folder in Outlook should be like the example, inside "Inbox" (12:00) 4) In tools activate the Objects library (11:42) 5) Check that the conditions are activated in blue font correctly (Set, Dim, Variant, ....) The above was what I checked and it helped me run the macro correctly. IT WORKS!!!!
Im so excited to go ba k to work tomorrow to start testing this code! This will surely help our department achieve our goals at a very efficient manner! Many thanks my friend for sharing this information.
i dont mean to be off topic but does someone know of a method to log back into an instagram account?? I was dumb lost the account password. I would appreciate any help you can offer me
Hi, very helpful video. Would I just need to add another if statement and define the cell on the sheet if want to also pull in only emails with specific text in the subject?
Hi Dinesh! Excellent video. I spent hours watching other videos and reading articles on how to get data from outlook to excel and yours was the most helpful to me. However, when I run it, I get this error: "Run-time error '5': Invalid procedure call or argument. Any idea what that means or how to fix it?? I'm pretty new at this and any knowledge I gain in college has since been forgotten.
Yes, you can. This Excel VBA tutorial will help further: www.exceltrainingvideos.com/get-outlook-data-from-chosen-folder/ Or search www.exceltrainingvideos.com
Great video. Can I make the folder name dynamic when my mails are distributed in separate folders and i want to select the desired folder using a drop down menu.
Hi Dinesh! Thank you so much for this video. Its a great help. I just to clarify that everytime I run my macro for this, my outlook should always be closed? Thank you for your response.
Thank you for this tutorial, it works very nicely. I have a project where I am trying to use this to pull emails from a subfolder within my inbox. How would I tweak this code to work for this situation? Thank you in advance for your assistance.
I watched this video several times and copied your VBA to Excel layout exactly. It works for what you have written it for. How do I get specific data from the body though? Lets say I am looking for a part number that proceeds the words, "Part Number:, " or "QTY:". Can you help please?
These links will help: www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/ www.exceltrainingvideos.com/how-to-transfer-data-from-one-sheet-to-another-using-wild-card-characters/ Or search www.exceltrainingvideos.com
Create two named ranges like start_Date and end_Date and use the following condition: If OutlookMail.ReceivedTime >= Range("start_Date").Value And OutlookMail.ReceivedTime
DINUSH, i am new to VBA and have very little experience. I am getting a runtime error but i work for a big company. Would that affect anything on the code? Does the outlook need to be routed to a different location? Im lost and would appreciate a response. Thanks I also have added the references which is 14.0 since I’m using outlook 2010
Mike, Run-time errors means your code can't perform what you told it to do; either by access or some other issues. Other possibilities is that "MAPI" should be the only NameSpace you would ever use. For this particular issue, Outlook 2007 does not support MAPI. Danny, excelvbadude.
Hi Dinesh! Thank you so much for such a wonderful and thorough video. I found it very easy to follow. My only dilemma is that everytime I try to run it I get the following error "Runtime Error '430' Class does not support automation..." I wrote out the code and verified it against the one you provided. I'm pretty new at this and not very familiar with these error codes so I'm not sure how to fix this or where I need to be looking. If you have any idea, I'd love your insight on the matter. Also, I wondered if instead of specifying the date on the excel spreadsheet and creating a range name for it, if I could just specify the date in the actual coding. Not sure if that makes sense or not. Thanks for your help!
@@Exceltrainingvideos Thank you for the quick reply! I did enable it. Although I had made that mistake originally and received the error "User defined type not defined"... once I fixed that, when trying to run code it would seem like it was loading but then I'd receive the error mentioned. I'll take a look again tonight and double check that I have the coding written correctly.
@@JeCrochet Did you ever get this working? I am facing the same issue and I do have the Microsoft Outlook 16.0 Object Library checked. I even tried running Excel as admin but that doesn't seem to help. For what it's worth, when I try debugging , it highlights the entire "If" statement.
Hi Dinesh.Thank so much for this video.I have a similar kind of requirement.But My requirement is now instead of giving a particular folder name, Is there a possibility we can pick any folder directly from outlook through VBA.could you pls help me on this
Hi Dinesh, great video. May I ask if this can extract data within an excel file that is attached to the email instead of text from the body of the email itself? It would be awesome if that is possible.
Helo sir i am getting error of user-defined type not defined and it's. Highlighting my codes first line which is Sub GetDataFromOutlook() can you please help wit it
Hi sir I have a scenario and I'm facing issue actually I have a email which I have to send on a daily basis with 3 pivotables in a workbook. The actions are go to sent items search for the subject and reply all and copy past the all 3 pivotables from excel to email body. But old message should also not be deleted. Can you please help
Thanks Dinesh, I can use your code in my work. by the way, could you please demonstrate how to copy the tables to the excel worksheet if the email content include some tables with formatted color? thank you so much
hello sir thank you for the wonderful video I have lots of emails in outlook and its taking time to copy emails in specific range, so is there any way to start search from the most recent email
Hi, In my office, we are working on emails But we are work from shared mailbox I tried using code as you have mentioned in above video but it only work on inbox or sub folder. Could you please help us extract information from shared mailboxes as well.
Example: Dim Ns As Outlook.NameSpace Dim Folder As Outlook.Folder Dim olShareName As Outlook.Recipient Set Ns = Application.GetNamespace("MAPI") Set olShareName = Ns.CreateRecipient("abcd@Email.com") '// Owner's email address Set Folder = Ns.GetSharedDefaultFolder(olShareName, olFolderInbox) '// Inbox Set Items = Folder.Items You should use your own path(s).
This really helps a lot. But how do i extract the recipient (email address) and also a specific data from the body of the email? Hope i can contact you.
Hi Dinesh, my VB skills are weak at best but I found this code to be quite helpful. I copied your code verbatim (changed the name of the inbox folder I was retrieving from) but it gives me a compile error: User-defined type not defined. Can you shed some light??
Hi sir, I want to take all the mail from back up folder in outlook. And is it possible to download the outlook mail attachment automatically using vba to particular folder and that folder path link should be showed in excel file (or if we click the link the file should be open) along with subject and other things u mentioned above
Hello Sir, I used your code and it run perfectly. Thank you very much for this tutorial! I want to add more features e.g. I want to connect my attachment from my Outlook E-Mail into my Excel Chart. How can I realize it? Thank you very much for your help! Best regards.
Hi Dinesh,Thanks for your video.When I run the code the cells are populated with the word "FALSE" instead of the actual data we are supposed to be importing. Can you please advise?Thanks
Thanks for sharing this video. When I run the code on my computer the subject and date get populated. However, the code gives me an error message when it tries to output the sender email and email body. I'm wondering if it's because I'm use MS 2013 and so instead of Microsoft Outlook 16.0 Object Library, I have Microsoft Outlook 15.0 Object Library. Would appreciate any insights on this issue.
Hi Sir! Can we look for emails in Inbox folder monthwise starting from current day? The inbox size has piled up a lot and everytime I search through it, it searches from the bottom of the folder. Its been over a year and half and a few tens of thousands of messages are cluttered in my inbox. I only want to extract emails from past two months starting from the current day. Implementing this code takes a lot of time. Instead if we were to search top down, it would be very easy. Can you please let me know what modification in code should be done in order to reverse the search order of the mail? Thank you for the help in anticipation :)
Thanks for sharing valuable information. I'm getting Error while running the above code: Method 'SenderName' of object '_MailItem' failed". Please provide any solution.
Yes you can! Something like this: Range("email_address").Offset(i, 0).Value = OutlookMail.SenderEmailAddress email_address is another named range in your excel worksheet.
Very informative tutorial. Can we extract search inside the body of the email and extract for example seriel number or date or something else. Thanks you
Yes. You can try the following and share: Once the body of the email is in your worksheet, it should be possible to find and extract any data using standard procedures.
Is it possible to take just a specific information data from body email? For example, in the body email I have the below information (sender always will put this information on body email as example): "Supplier will deliver the cargo to forwarder W/H on 25 APR. FCA HK Total:500KG 1PLT/25CTNS(Plastic pallet) 120*80*14.5cm =1.392CBM*1PLT GW:505.5KG NW:500KG CBM:1.392" But for me is just necessary take below information of: "1PLT/25CTNS(Plastic pallet) GW:505.5KG NW:500KG CBM:1.392" Is possible to create a macro to pick just this specific information from body email?
@@Exceltrainingvideos Hi Dinesh, thanks of this video. Were you able to come-up with something on extracting data from shared e-mail accounts in outlook?
Your video is very helpful! Hopefully I can tweak your code to download a csv attachment, rename it, add a time stamp to the name and save it to a shared folder. Thank you!
These tutorials will guide: ruclips.net/video/R0qzfYJr6d8/видео.html ruclips.net/video/9KJ0V3GdBm4/видео.html You can also search my channel using 'outlook' as the keyword.
Dear Dinesh Thank you for this video, it is seriously nice guidance. Yet, I have one problem, after i run the macro, it always stop on the "Next OutlookMail" (its gets highlighted with blue) and then it jump to the first row with "Sub getDataFromOutlook ()" (is highlighted with yellow) please, Do you have any idea where could be the problem ? Thank you
This link will help to check your VBA code: www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/ Most probably you forgot to write the line: Next OutlookMail.
Hello Dinesh, I got a error 438 (object doesn't support this property or method). It is pointing If OutlookMail.ReceivedTime >= Range(“email_Receipt_Date”).Value then Could you check it please?
Did you take reference to the Microsoft Outlook Library? This link will guide: www.exceltrainingvideos.com/tag/send-emails-from-a-specific-account-in-outlook/
Hello Danish, Thank you very much for this video. I have one request of help please, I'm trying to search outlook subject with my PO numbers and get the latest email received that contains this purchase order number in the subject so I can work with it. I'm searching a lot for a solution but I can't seem to find the right one yet, maybe you can help in that please? Thank you in advance and great video again.
while trying this i'm getting a compile error User-defined type not defined help me on this. Also can you say whether this can be done for functional mail box folder?
Hi thanks for the great tutorial! i have a slight problem, i use it to extract emails which contains tables and it causes errors specifically error 438, i know the code is correct since i tried it on emails with no tables and it works. Hope you can help me figure this out. Thank you. 😊
Hi Dinesh, how can I capture the content of table from mail body and past into Excel. And that mail arrives every hours in a day so I need to capture the table content and past into Excel automatically once mail arrives.
Hello sir...thank you ..Could you please help how to read the shared mailbox folders effectively... I have tried... But it's taking lot of time to execute
This VBA tutorial link will help: www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/ You can find more VBA solutions on this topic on my website www.exceltrainingvideos.com/ or this channel.
Hi Dinesh, Suppose I have Address information(Street no., pin code, person_name) in one field(record) and all these information is in access db. And I want to create a table in access db with column displayed as street no., pin code, person_name in one table . How to do that?
Hi Dinesh. I watched this video several times and copied your VBA and Excel layout exactly but get run time error. Running code finds 1st email subject and populates cell A4 then runtime occur "1004 Method range of object_Global failed" for the following VBA entries. Range(“email_Date”).Offset(i, 0).Value = OutlookMail.ReceivedTime Range(“email_Sender”).Offset(i, 0).Value = OutlookMail.SenderName Range(“email_Body”).Offset(i, 0).Value = OutlookMail.Body I skipped column auto fit & alignment trying to figure out why these lines fail. Outlook closed when running code. Using office 2016. Appreciate any assistance you can provide.
Copy/Paste of code will not work. Check your code against this link: www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/ You'll have to change folders and dates according to your requirements. Did you activate the Microsoft Outlook Object Library from 'Tools' --> References in the Visual Basic Editor?
Many Thanks Dinesh. Copy and paste of your VBA was my problem. I shouldn't be lazy :) Your code is excellent. Now all I have to work out is how to export Table from email body to spreadsheet instead of parse body text string copied to sheet.
Sir, I want the same code to be used from an specific folder but I want to extract data only for the unread emails and once I extract the same I want vba to mark that same emails as read, so that it doesn't pullup the same emails again. Can you help?
Try the following VBA code: Sub MarkUnreadMailsAsRead() Application.ScreenUpdating = False Dim oInbox As Outlook.MAPIFolder Dim oOutlook As Object, oSpace As Object, oMessage As Object Set oOutlook = CreateObject("Outlook.Application") Set oSpace = oOutlook.GetNamespace("MAPI") Set oInbox = oSpace.GetDefaultFolder(olFolderInbox) For Each oMessage In oInbox.Items oMessage.UnRead = False Next Set oOutlook = Nothing Set oSpace = Nothing Set oInbox = Nothing Application.ScreenUpdating = True End Sub
Hi Takyar. Nice videos from ur end. But if the person wants to retrive the data like Mail as Marked, Mail as flagged and If any appointments are there, macro should not that perticular mail. But for mail looping stops when the mail items belongs to any appointment or Appointment response. Please help on this Sir. Any body can help me on this please.
If you don't have any typos, then you are probably missing a reference. Remember to go to Tools/References and place a check by your current Microsoft Outlook Object Library. You should also have Excel and Office Object Libraries checked. HTH
Hi Dinesh sir, thanks for very useful trick to get the report from outlook in excel, could you please guide how can we add recipient of an email and CC in to this?
This VBA code will help: Range("email_address").Offset(i, 0).Value = OutlookMail.SenderEmailAddress email_address is another named range in your excel worksheet.
Hello Sir, Is it possible to import an Excel file data from Outlook attachment. Path : Data>Getdata>Fromonline services>From microsoft exchange online.
Hello Sir, I have an excel sheet which has data in tabular columns & rows in multiple sheets. these tables contain data like names and their task and few extra rows and columns with data. I need to send mails to each person's boss listing the data in tabular format from multiple sheets. these mails have a standard text in body & From details of the mail. below the standard text the tables should be present and at end the From details need to be present. Only data that is changing here for each mail is the data in tables & receiver name. How can these emails be sent automatically via vba.
Love this video! been so helpful and super easy to follow, thank you very much! is there a way to get the same info from another folder instead of the inbox? I am using this to count emails in a shared email and once the emails have been replied to they get moved to another folder. Thank you again!
Hi Very nice vid. However win I run your code I get an error at this statement Set OutlookNamespace = OutlookApp.GetNamespace(“MAPI”). The Error is "runtime Error... could not complete the operations one or more parameters are missing" I enable the Microsoft Outlook 12.0 object library, and am using Outlook 2007. Are their other libraries I need? Thanks for the help.
I can't get this to pull my data out. It may have something to do with my inbox folder being my gmail import. I am not sure outlook recognizes it as the default even after I declared it as such in options.. any Ideas?
Hi sir I wanted to create a macro to get the reports on monthly basis likewise sender name ,received date ,reply date and it has attachment or not. Regards Kunjan Singh
Check you code again. This link will help:www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/ Did you create a reference to the Microsoft Outlook Library?
Hi Sir, I am getting an error "User-defined type not defined" on this statement in the code "Dim OutlookApp As Outlook.Application". Could you please help me debug this code. Thank you.
You should activate the Microsoft Outlook Library. This link will guide: www.exceltrainingvideos.com/tag/get-data-from-picked-outlook-folder-into-excel/
HI sir, thanks for inputs its very useful. but can plz allobrate the topic on "column D" body of text will be in different types. can u plz if we want to pick any specific line from body of email in sepetate cell
Hi sir If in outlook i received an email with excel data daily with same formate attached. For example attached data having columns Date. Staff name staff id. Reference nmber count of payment How i can paste in excel master file daily without opening email
Excellent video. I was very useful, thank you very much for your contributions.
For users who are not processed by the macro, review the following:
1) If you make a copy and paste of the web page, you must change the italic quotes for ("")
2) Check the defined cell name in Excel, it must be identical to the macro. Note that the date cell (B1) has a defined name, it is not in (A1) (1:25)
3) The location of the "ImpMail" folder in Outlook should be like the example, inside "Inbox" (12:00)
4) In tools activate the Objects library (11:42)
5) Check that the conditions are activated in blue font correctly (Set, Dim, Variant, ....)
The above was what I checked and it helped me run the macro correctly.
IT WORKS!!!!
Noted.
Thank you sir.
Thank you for the video..
Can we put specific subject insterd of date
Pls reply.
Im so excited to go ba k to work tomorrow to start testing this code! This will surely help our department achieve our goals at a very efficient manner! Many thanks my friend for sharing this information.
Im a beginner and it makes more comfortable in understanding :) thank you so much Sir !!
i dont mean to be off topic but does someone know of a method to log back into an instagram account??
I was dumb lost the account password. I would appreciate any help you can offer me
Thank you so much Sir. It's really helpful. And I have a small query, can I use a folder in shared mailbox ??
THanks this is very helpful! How would you retrieve calendar items in this fashion?
Thank you Dinesh sir. Most educational video!
Most welcome!
Hi, very helpful video. Would I just need to add another if statement and define the cell on the sheet if want to also pull in only emails with specific text in the subject?
Yes you can!
This link will guide: ruclips.net/video/9KJ0V3GdBm4/видео.html
Hi Dinesh! Excellent video. I spent hours watching other videos and reading articles on how to get data from outlook to excel and yours was the most helpful to me. However, when I run it, I get this error: "Run-time error '5': Invalid procedure call or argument. Any idea what that means or how to fix it?? I'm pretty new at this and any knowledge I gain in college has since been forgotten.
Thank for ur help, its help me so much. but i have some Question, can't i import email from folder "inbox" directly?, not from sub folder?
Yes, you can. This Excel VBA tutorial will help further: www.exceltrainingvideos.com/get-outlook-data-from-chosen-folder/
Or search www.exceltrainingvideos.com
Hi Dinesh, do you know how to also include images from outlook email to excel? Thank you!
Great video. Can I make the folder name dynamic when my mails are distributed in separate folders and i want to select the desired folder using a drop down menu.
ruclips.net/video/R0qzfYJr6d8/видео.html
How can I filter out the mails received between two given dates??
Hi Dinesh! Thank you so much for this video. Its a great help. I just to clarify that everytime I run my macro for this, my outlook should always be closed? Thank you for your response.
No. When you run the code, do you see the Outlook icon in the taskbar?
@@Exceltrainingvideos thanks a lot..
Thank you for this tutorial, it works very nicely. I have a project where I am trying to use this to pull emails from a subfolder within my inbox. How would I tweak this code to work for this situation? Thank you in advance for your assistance.
Glad it helped! This link will help: ruclips.net/video/R0qzfYJr6d8/видео.html
Please search this channel using the keyword 'outlook'.
I watched this video several times and copied your VBA to Excel layout exactly. It works for what you have written it for. How do I get specific data from the body though? Lets say I am looking for a part number that proceeds the words, "Part Number:, " or "QTY:". Can you help please?
These links will help:
www.exceltrainingvideos.com/automate-copying-excel-column-data-from-sheet1-to-sheet2-with-vba/
www.exceltrainingvideos.com/how-to-transfer-data-from-one-sheet-to-another-using-wild-card-characters/
Or search www.exceltrainingvideos.com
A wonderful video and thank You for all of your wonderful work. Is it possible to have a date range to retrieve the information vs just a single date?
Create two named ranges like start_Date and end_Date and use the following condition:
If OutlookMail.ReceivedTime >= Range("start_Date").Value And OutlookMail.ReceivedTime
DINUSH, i am new to VBA and have very little experience. I am getting a runtime error but i work for a big company. Would that affect anything on the code? Does the outlook need to be routed to a different location? Im lost and would appreciate a response. Thanks I also have added the references which is 14.0 since I’m using outlook 2010
Please contact your system administrator.
Mike, Run-time errors means your code can't perform what you told it to do; either by access or some other issues. Other possibilities is that "MAPI" should be the only NameSpace you would ever use. For this particular issue, Outlook 2007 does not support MAPI.
Danny,
excelvbadude.
Hello Dinesh the video is super, any chance I can get the text, I am very new to this but the scheme would really help me out. Please advise.
Hi Dinesh! Thank you so much for such a wonderful and thorough video. I found it very easy to follow. My only dilemma is that everytime I try to run it I get the following error "Runtime Error '430' Class does not support automation..." I wrote out the code and verified it against the one you provided. I'm pretty new at this and not very familiar with these error codes so I'm not sure how to fix this or where I need to be looking. If you have any idea, I'd love your insight on the matter.
Also, I wondered if instead of specifying the date on the excel spreadsheet and creating a range name for it, if I could just specify the date in the actual coding. Not sure if that makes sense or not.
Thanks for your help!
Did you activate the Microsoft Outlook Library from VBE --> Tools --> References? I mention this in my video at around 11.20 min.
Regarding the date: I think your idea should work.
@@Exceltrainingvideos Thank you for the quick reply! I did enable it. Although I had made that mistake originally and received the error "User defined type not defined"... once I fixed that, when trying to run code it would seem like it was loading but then I'd receive the error mentioned. I'll take a look again tonight and double check that I have the coding written correctly.
@@JeCrochet Did you ever get this working? I am facing the same issue and I do have the Microsoft Outlook 16.0 Object Library checked. I even tried running Excel as admin but that doesn't seem to help. For what it's worth, when I try debugging , it highlights the entire "If" statement.
Hi Dinesh.Thank so much for this video.I have a similar kind of requirement.But My requirement is now instead of giving a particular folder name, Is there a possibility we can pick any folder directly from outlook through VBA.could you pls help me on this
This VBA solution is an exact fit to your query: ruclips.net/video/R0qzfYJr6d8/видео.html
Please share with your friends too.
Hi Dinesh, great video. May I ask if this can extract data within an excel file that is attached to the email instead of text from the body of the email itself? It would be awesome if that is possible.
This VBA tutorial will show the way: www.exceltrainingvideos.com/import-email-attachments-from-outlook-into-excel-automatically/
Thank You Sir!, One query, what do we need to do, if we do not want to fetch data from a particular folder, but from the inbox itself?
Us the path to the inbox.
Helo sir i am getting error of user-defined type not defined and it's. Highlighting my codes first line which is Sub GetDataFromOutlook() can you please help wit it
Hi sir I have a scenario and I'm facing issue actually I have a email which I have to send on a daily basis with 3 pivotables in a workbook. The actions are go to sent items search for the subject and reply all and copy past the all 3 pivotables from excel to email body. But old message should also not be deleted. Can you please help
Thanks Dinesh, I can use your code in my work. by the way,
could you please demonstrate how to copy the tables to the excel worksheet if the email content include some tables with formatted color? thank you so much
hello sir
thank you for the wonderful video
I have lots of emails in outlook and its taking time to copy emails in specific range, so is there any way to start search from the most recent email
Hi,
In my office, we are working on emails
But we are work from shared mailbox
I tried using code as you have mentioned in above video but it only work on inbox or sub folder. Could you please help us extract information from shared mailboxes as well.
Example:
Dim Ns As Outlook.NameSpace
Dim Folder As Outlook.Folder
Dim olShareName As Outlook.Recipient
Set Ns = Application.GetNamespace("MAPI")
Set olShareName = Ns.CreateRecipient("abcd@Email.com") '// Owner's email address
Set Folder = Ns.GetSharedDefaultFolder(olShareName, olFolderInbox) '// Inbox
Set Items = Folder.Items
You should use your own path(s).
@@Exceltrainingvideos Im getting error at olFolderInbox
@@Exceltrainingvideos Can u mention how to make folder path in the code
This really helps a lot. But how do i extract the recipient (email address) and also a specific data from the body of the email? Hope i can contact you.
Wonderful sir..Thank you for sharing the knowledge
You are most welcome
Hi Dinesh, my VB skills are weak at best but I found this code to be quite helpful. I copied your code verbatim (changed the name of the inbox folder I was retrieving from) but it gives me a compile error: User-defined type not defined. Can you shed some light??
Did you activate the Microsoft Outlook Object Library from Tools--> References?
@@Exceltrainingvideos Yes, I did!
Hi sir
Can you please elaborate on Range which you have defined for date, subject body etc
Hi sir, I want to take all the mail from back up folder in outlook. And is it possible to download the outlook mail attachment automatically using vba to particular folder and that folder path link should be showed in excel file (or if we click the link the file should be open) along with subject and other things u mentioned above
Search www.exceltrainingvideos.com/
Hello Sir, I used your code and it run perfectly. Thank you very much for this tutorial! I want to add more features e.g. I want to connect my attachment from my Outlook E-Mail into my Excel Chart. How can I realize it? Thank you very much for your help! Best regards.
I could not run the code , it gives compilation error user-defined type not define
Hi Dinesh,Thanks for your video.When I run the code the cells are populated with the word "FALSE" instead of the actual data we are supposed to be importing. Can you please advise?Thanks
Check your code. This link will help: www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/
Thanks for sharing this video. When I run the code on my computer the subject and date get populated. However, the code gives me an error message when it tries to output the sender email and email body. I'm wondering if it's because I'm use MS 2013 and so instead of Microsoft Outlook 16.0 Object Library, I have Microsoft Outlook 15.0 Object Library.
Would appreciate any insights on this issue.
Check your code against the code given in the following link:
www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/
Hi Sir!
Can we look for emails in Inbox folder monthwise starting from current day?
The inbox size has piled up a lot and everytime I search through it, it searches from the bottom of the folder. Its been over a year and half and a few tens of thousands of messages are cluttered in my inbox. I only want to extract emails from past two months starting from the current day. Implementing this code takes a lot of time.
Instead if we were to search top down, it would be very easy. Can you please let me know what modification in code should be done in order to reverse the search order of the mail?
Thank you for the help in anticipation :)
In Outlook you can easily create a 'Date' rule.
Thanks for sharing valuable information. I'm getting Error while running the above code: Method 'SenderName' of object '_MailItem' failed". Please provide any solution.
This link will help: www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/
Thank you so much for the help my man! Couldn't figure what was wrong and it turns out I hadn't checked off the right box in the tools section XD
Glad I could help! Please share this Excel VBA tutorials channel with your friends too. Thanks.
Hi Thank you this is really useful - do you know if you can do the same with email addresses?
Yes you can! Something like this:
Range("email_address").Offset(i, 0).Value = OutlookMail.SenderEmailAddress
email_address is another named range in your excel worksheet.
@@Exceltrainingvideos thank you very much
Hi Dinesh,
I was trying the exact steps and I keep getting the error "user-defined type not defined". Would you happen to know the issue?
Have you referenced the Outlook Library?
www.exceltrainingvideos.com/get-outlook-data-from-chosen-folder/
@@Exceltrainingvideos I am having the same issue. I have outlook libraries selected under reference.
Very informative tutorial. Can we extract search inside the body of the email and extract for example seriel number or date or something else. Thanks you
Yes. You can try the following and share: Once the body of the email is in your worksheet, it should be possible to find and extract any data using standard procedures.
Is it possible to take just a specific information data from body email? For example, in the body email I have the below information (sender always will put this information on body email as example):
"Supplier will deliver the cargo to forwarder W/H on 25 APR.
FCA HK
Total:500KG
1PLT/25CTNS(Plastic pallet)
120*80*14.5cm =1.392CBM*1PLT
GW:505.5KG
NW:500KG
CBM:1.392"
But for me is just necessary take below information of:
"1PLT/25CTNS(Plastic pallet)
GW:505.5KG
NW:500KG
CBM:1.392"
Is possible to create a macro to pick just this specific information from body email?
You can use text functions like InStr and Mid. This link will guide: www.exceltrainingvideos.com/nested-do-while-loop-instr-function-in-excel-vba/
Hi Dinesh, do we need to write this line of code when outlook is already open - set OutlookApp = new Outlook.Application?
Right. This link will guide: www.exceltrainingvideos.com/how-to-import-data-from-word-tables-into-excel-with-vba/
how to select from a folder which is not from your main mailbox but a shared mailbox that is added to outlook? Thanks much appreciate you work Dinesh!
We'll work on this idea.
@@Exceltrainingvideos Hi Dinesh, thanks of this video. Were you able to come-up with something on extracting data from shared e-mail accounts in outlook?
Will this work on a shared mailbox or do i need to change any specific lines to get this to work thanks
This Excel VBA lesson will make your life easier: ruclips.net/video/R0qzfYJr6d8/видео.html
@@Exceltrainingvideos thanks Dinesh but I wanted to specify the folder instead of pick folder how can I do this?
@@leegarrett1908 Use the path to the folder as shown in the video.
Can i get JPG, PNG or any image file from the email body? Current code only let me export text data.
Hi can we bypass the complicated vba with power query? Data source will be attachment excel from daily repetitive email
You can try it out and share!
Your video is very helpful! Hopefully I can tweak your code to download a csv attachment, rename it, add a time stamp to the name and save it to a shared folder. Thank you!
Glad you found the VBA training video helpful!
Great!! Have two queries -
1) how to get data from the shared mailbox
2) how to only get data for emails with specific word in the body
These tutorials will guide:
ruclips.net/video/R0qzfYJr6d8/видео.html
ruclips.net/video/9KJ0V3GdBm4/видео.html
You can also search my channel using 'outlook' as the keyword.
It's a very useful video, thanks so much for your kind sharing.
This is the video i were searching for. Thank you, this made my day.
Glad I could help. Please share the Outlook VBA lesson with your friends too.
Dear Dinesh
Thank you for this video, it is seriously nice guidance.
Yet, I have one problem, after i run the macro, it always stop on the "Next OutlookMail" (its gets highlighted with blue)
and then it jump to the first row with "Sub getDataFromOutlook ()" (is highlighted with yellow)
please, Do you have any idea where could be the problem ? Thank you
This link will help to check your VBA code: www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/
Most probably you forgot to write the line: Next OutlookMail.
Hello Dinesh, I got a error 438 (object doesn't support this property or method). It is pointing
If OutlookMail.ReceivedTime >= Range(“email_Receipt_Date”).Value then
Could you check it please?
Did you take reference to the Microsoft Outlook Library?
This link will guide: www.exceltrainingvideos.com/tag/send-emails-from-a-specific-account-in-outlook/
awesome sir, this is SQL's part we never think about it, we were finding this in power query
Glad to hear that. Please share the tutorials with your friends.
@@Exceltrainingvideos ok sir but power Query is more suitable I also got trick to get data from outlook with power query
@@shubhamsharma8373 share the link shubam if you have; how to do with power query!?
Hello Danish,
Thank you very much for this video.
I have one request of help please, I'm trying to search outlook subject with my PO numbers and get the latest email received that contains this purchase order number in the subject so I can work with it.
I'm searching a lot for a solution but I can't seem to find the right one yet, maybe you can help in that please?
Thank you in advance and great video again.
This Excel tutorial will guide: ruclips.net/video/9KJ0V3GdBm4/видео.html
Hi Dinesh,
Thanks for sharing your knowledge..i tried the same..but when i run the VBA i got a Run-time error 5 message..can you help me on this?
Check your code carefully. I'm sure you know that even a character change matters in coding.
while trying this i'm getting a compile error
User-defined type not defined
help me on this. Also can you say whether this can be done for functional mail box folder?
Most probably you haven't activated the Microsoft Outlook Library from Tools --- References.
Hi thanks for the great tutorial! i have a slight problem, i use it to extract emails which contains tables and it causes errors specifically error 438, i know the code is correct since i tried it on emails with no tables and it works. Hope you can help me figure this out. Thank you. 😊
Let me check
This is excellent! Very useful for work. How would I access a shared Exchange folder? Thank you :)
I don't use 'Exchange'.
Hi Dinesh, how can I capture the content of table from mail body and past into Excel.
And that mail arrives every hours in a day so I need to capture the table content and past into Excel automatically once mail arrives.
Great project to work on especially the timer!
Hello sir...thank you ..Could you please help how to read the shared mailbox folders effectively... I have tried... But it's taking lot of time to execute
maybe using arrays instead of recording each line
Hi Dinesh, we have four email accounts linked to MS Outlook, could you please suggest me how to get from a particular folder from a particular account
This link will help: www.exceltrainingvideos.com/tag/get-data-from-outlook-into-excel-based-on-multiple-conditions/
Hi there, is it possible to extract all emails from Inbox and all subfolders in the Inbox?
This Excel VBA tutorial will help: ruclips.net/video/R0qzfYJr6d8/видео.html
Wow thank you sir.. This is exactly what i need now
You are most welcome. Please share with your friends too.
Hi, how can we use for sent email? is it right Set Folder = OutlookNamespace.GetDefaultFolder(olFolderSentMail)? please suggest
This VBA tutorial link will help: www.exceltrainingvideos.com/how-to-use-automation-to-send-ms-outlook-mail-using-excel-vba/
You can find more VBA solutions on this topic on my website www.exceltrainingvideos.com/ or this channel.
Hi Dinesh, Suppose I have Address information(Street no., pin code, person_name) in one field(record) and all these information is in access db. And I want to create a table in access db with column displayed as street no., pin code, person_name in one table . How to do that?
Please ask questions related to Excel.
Hi Dinesh.
I watched this video several times and copied your VBA and Excel layout exactly but get run time error.
Running code finds 1st email subject and populates cell A4 then runtime occur
"1004 Method range of object_Global failed" for the following VBA entries.
Range(“email_Date”).Offset(i, 0).Value = OutlookMail.ReceivedTime
Range(“email_Sender”).Offset(i, 0).Value = OutlookMail.SenderName
Range(“email_Body”).Offset(i, 0).Value = OutlookMail.Body
I skipped column auto fit & alignment trying to figure out why these lines fail. Outlook closed when running code. Using office 2016.
Appreciate any assistance you can provide.
Copy/Paste of code will not work. Check your code against this link:
www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/
You'll have to change folders and dates according to your requirements. Did you activate the Microsoft Outlook Object Library from 'Tools' --> References in the Visual Basic Editor?
Many Thanks Dinesh. Copy and paste of your VBA was my problem. I shouldn't be lazy :) Your code is excellent. Now all I have to work out is how to export Table from email body to spreadsheet instead of parse body text string copied to sheet.
Hi! Is there any code that I can make the body of the email to shrinktofit? So that I will easily see the whole email?
And to easily clear the data?
Sir, this is very useful... Now I want get data from out look to excel . but I want only table data. Please help on this with VBA thanks in advanced.
Sir, I want the same code to be used from an specific folder but I want to extract data only for the unread emails and once I extract the same I want vba to mark that same emails as read, so that it doesn't pullup the same emails again.
Can you help?
Try the following VBA code:
Sub MarkUnreadMailsAsRead()
Application.ScreenUpdating = False
Dim oInbox As Outlook.MAPIFolder
Dim oOutlook As Object, oSpace As Object, oMessage As Object
Set oOutlook = CreateObject("Outlook.Application")
Set oSpace = oOutlook.GetNamespace("MAPI")
Set oInbox = oSpace.GetDefaultFolder(olFolderInbox)
For Each oMessage In oInbox.Items
oMessage.UnRead = False
Next
Set oOutlook = Nothing
Set oSpace = Nothing
Set oInbox = Nothing
Application.ScreenUpdating = True
End Sub
Hi Takyar. Nice videos from ur end. But if the person wants to retrive the data like Mail as Marked, Mail as flagged and If any appointments are there, macro should not that perticular mail. But for mail looping stops when the mail items belongs to any appointment or Appointment response. Please help on this Sir.
Any body can help me on this please.
Good question. Will work on the idea.
Hi! I replaced impMail with my own folder but I get an error… Why is this?
what have i done wrong when it prompts a Compile Error: user-defined type not defined? sorry very new to this
If you don't have any typos, then you are probably missing a reference. Remember to go to Tools/References and place a check by your current Microsoft Outlook Object Library. You should also have Excel and Office Object Libraries checked. HTH
Hi Dinesh sir, thanks for very useful trick to get the report from outlook in excel, could you please guide how can we add recipient of an email and CC in to this?
This VBA code will help:
Range("email_address").Offset(i, 0).Value = OutlookMail.SenderEmailAddress
email_address is another named range in your excel worksheet.
@@Exceltrainingvideos Thank you so much sir for your prompt response it works :)
Hello Sir - This was a very informative video. Cn the same be done on a shared mailbox.? please advise the requirement is urgent
This Excel VBA tutorial will help: ruclips.net/video/R0qzfYJr6d8/видео.html
@@Exceltrainingvideos thank you very much for the response.. I shall check that bideo
Fabulous Video- thank you for sharing!
Glad you enjoyed it! Please share the VBA tutorials in Excel with your friends also.
Great Information, helped me a lot.
Thank you
Hello Sir, Is it possible to import an Excel file data from Outlook attachment. Path : Data>Getdata>Fromonline services>From microsoft exchange online.
Yes, you can
Sir ur a life saver
Wow! I'm glad that this VBA tutorial on Outlook helped.
Great. tutorial! I have a question though. How can I pull emails from a pst file?
Do you know the exact path to the file?
Hello Sir, I have an excel sheet which has data in tabular columns & rows in multiple sheets. these tables contain data like names and their task and few extra rows and columns with data. I need to send mails to each person's boss listing the data in tabular format from multiple sheets. these mails have a standard text in body & From details of the mail. below the standard text the tables should be present and at end the From details need to be present. Only data that is changing here for each mail is the data in tables & receiver name. How can these emails be sent automatically via vba.
I've enrolled for a new Ph. D.
Love this video! been so helpful and super easy to follow, thank you very much! is there a way to get the same info from another folder instead of the inbox? I am using this to count emails in a shared email and once the emails have been replied to they get moved to another folder. Thank you again!
This link will guide: ruclips.net/video/R0qzfYJr6d8/видео.html
how about if I want to extract only the "email Sender" "title of the Email" and the "Date"
Hi Very nice vid. However win I run your code I get an error at this statement
Set OutlookNamespace = OutlookApp.GetNamespace(“MAPI”).
The Error is
"runtime Error...
could not complete the operations one or more parameters are missing"
I enable the Microsoft Outlook 12.0 object library, and am using Outlook 2007. Are their other libraries I need? Thanks for the help.
ooops - no need to respond I had a typo - everything works great, the refernce mentioned is the only one needed- Thanks
@@phoenixrising2645 Hi there, I have the same error...what was your typo? Thanks
in the dim statement - "Dim OutlookApp As Outlook.Application" I had "Dim OutlookApp As OutlookApp.Application" note the "OutlookApp" typo.
I can't get this to pull my data out. It may have something to do with my inbox folder being my gmail import. I am not sure outlook recognizes it as the default even after I declared it as such in options.. any Ideas?
This link will help: ruclips.net/video/R0qzfYJr6d8/видео.html
Are you sure that the emails have been imported into the inbox folder?
Hi sir
I wanted to create a macro to get the reports on monthly basis likewise sender name ,received date ,reply date and it has attachment or not.
Regards
Kunjan Singh
As a starter this link will help: www.exceltrainingvideos.com/generate-report-based-on-dates/
You may like to search www.exceltrainingvideos.com
Sir I am looking for outlook automation
Like who has sent mail on which date he or she has sent and when we have replied date
Can we similarly extract data from pdfs into excel, without converting pdfs to excel sheets?
This link will help: www.exceltrainingvideos.com/convert-pdf-to-excel/
hi pls guide as i got error with this lince - Set OutlookNamespace = OutlookApp.GetNamespace(“MAPI”)
Check you code again. This link will help:www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/
Did you create a reference to the Microsoft Outlook Library?
How can I get just the whole file not having to pick a date? Do i just delete that part of code?
Yes.
Good day Sir! Great Videos! how can I include the recipient name, what is the correct code?
www.exceltrainingvideos.com/get-data-from-outlook-address-book-automatically/
Is it possible to pick data from emails based on specific text in the subject
These links will help:
ruclips.net/video/35g8J2mzdUY/видео.html
www.exceltrainingvideos.com/tag/get-data-from-outlook-with-vba/
Awesome! thank you very much!
Glad it helped!
Hi Sir, I am getting an error "User-defined type not defined" on this statement in the code "Dim OutlookApp As Outlook.Application". Could you please help me debug this code. Thank you.
You should activate the Microsoft Outlook Library. This link will guide: www.exceltrainingvideos.com/tag/get-data-from-picked-outlook-folder-into-excel/
HI sir, thanks for inputs its very useful. but can plz allobrate the topic on "column D" body of text will be in different types. can u plz if we want to pick any specific line from body of email in sepetate cell
Search www.exceltrainingvideos.com
I have the Outlook Object library set but I'm still getting the compiler error "MAPI" not defined?
Check carefully - might be a silly coding error.
Hi sir
If in outlook i received an email with excel data daily with same formate attached. For example attached data having columns
Date. Staff name staff id. Reference nmber count of payment
How i can paste in excel master file daily without opening email
Hello Sir,
Could you help me with the "Inbox" rather than sub folder.
(How to use the Path to the Inbox) ?
This VBA tutorial link will guide: www.exceltrainingvideos.com/import-email-attachments-from-outlook-into-excel-automatically/
@@Exceltrainingvideos there's nothing showing that how to use the inbox only, its showing subfolder (CJ), Please help sir
Sir, can i have VBA code to fetch the undelivered mails from Outlook...
Thanks.
Thanks. . One question from my side. .. how to run program for Email Sender ..
No idea.