This macro collects the fields from each Outlook message in a selection and writes the values of the fields to an Excel worksheet. It's easily adapted to work with any field and any Outlook item type.
In Excel 2016, rCount is finding the last USED line, not the next blank line. Use rCount = rCount + 1 to move down one line.
Updated November 25 2017 to get all recipient addresses.
Updated October 20 2017 to create a new workbook (user will need to save it). Also added column names and adjusted the column widths.
If you want to run the macro on all messages in the selected folder, use this file. In addition, it will create the workbook if it doesn't exist and add the columns headers if needed.
An Excel version of this macro is available in a workbook template here or as a text file here. The workbook code removes hyperlinked URLs from the messages (for easier reading in Excel).
Option Explicit
Sub CopyToExcel()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String
Dim currentExplorer As Explorer
Dim Selection As Selection
Dim olItem As Outlook.MailItem
Dim obj As Object
Dim strColA, strColB, strColC, strColD, strColE As String
' Get Excel set up
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
'## Open a specific workbook to input the data
'the path of the workbook under the windows user account
'enviro = CStr(Environ("USERPROFILE"))
' strPath = enviro & "\Documents\test.xlsx"
' Set xlWB = xlApp.Workbooks.Open(strPath)
' Set xlSheet = xlWB.Sheets("Sheet1")
'## End Specific workbook
'## Use New Workbook
Set xlWB = xlApp.Workbooks.Add
Set xlSheet = xlWB.Sheets("Sheet1")
'## end use new workbook
' Add column names
xlSheet.Range("A1") = "Sender"
xlSheet.Range("B1") = "Sender address"
xlSheet.Range("C1") = "Message Body"
xlSheet.Range("D1") = "Sent To"
xlSheet.Range("E1") = "Recieved Time"
' Process the message record
On Error Resume Next
'Find the next empty line of the worksheet
rCount = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
'needed for Exchange 2016. Remove if causing blank lines.
rCount = rCount + 1
' get the values from outlook
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection
Set olItem = obj
'collect the fields
strColA = olItem.SenderName
strColB = olItem.SenderEmailAddress
strColC = olItem.Body
strColD = olItem.To
strColE = olItem.ReceivedTime
'### Get all recipient addresses
' instead of To names
Dim strRecipients As String
Dim Recipient As Outlook.Recipient
For Each Recipient In olItem.Recipients
strRecipients = Recipient.Address & "; " & strRecipients
Next Recipient
strColD = strRecipients
'### end all recipients addresses
'### Get the Exchange address
' if not using Exchange, this block can be removed
Dim olEU As Outlook.ExchangeUser
Dim oEDL As Outlook.ExchangeDistributionList
Dim recip As Outlook.Recipient
Set recip = Application.Session.CreateRecipient(strColB)
If InStr(1, strColB, "/") > 0 Then
' if exchange, get smtp address
Select Case recip.AddressEntry.AddressEntryUserType
Case OlAddressEntryUserType.olExchangeUserAddressEntry
Set olEU = recip.AddressEntry.GetExchangeUser
If Not (olEU Is Nothing) Then
strColB = olEU.PrimarySmtpAddress
End If
Case OlAddressEntryUserType.olOutlookContactAddressEntry
Set olEU = recip.AddressEntry.GetExchangeUser
If Not (olEU Is Nothing) Then
strColB = olEU.PrimarySmtpAddress
End If
Case OlAddressEntryUserType.olExchangeDistributionListAddressEntry
Set oEDL = recip.AddressEntry.GetExchangeDistributionList
If Not (oEDL Is Nothing) Then
strColB = olEU.PrimarySmtpAddress
End If
End Select
End If
' ### End Exchange section
'write them in the excel sheet
xlSheet.Range("A" & rCount) = strColA ' sender name
xlSheet.Range("B" & rCount) = strColB ' sender address
xlSheet.Range("C" & rCount) = strColC ' message body
xlSheet.Range("D" & rCount) = strColD ' sent to
xlSheet.Range("E" & rCount) = strColE ' recieved time
'Next row
rCount = rCount + 1
Next
' size the cells
xlSheet.Columns("A:E").EntireColumn.AutoFit
xlSheet.Columns("C:C").ColumnWidth = 100
xlSheet.Columns("D:D").ColumnWidth = 30
xlSheet.Range("A2").Select
xlSheet.Columns("A:E").VerticalAlignment = xlTop
xlApp.Visible = True
' to save but not close
'xlWB.Save
' to save and close
' xlWB.Close 1
' If bXStarted Then
' xlApp.Quit
' End If
' end save and close
Set olItem = Nothing
Set obj = Nothing
Set currentExplorer = Nothing
Set xlSheet = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
End Sub
Automate using an ItemAdd or Run a Script Macro
With a few slight modifications, we can watch a folder for new messages and process new mail as it arrives.
This set of macros needs to go into ThisOutlookSession.
Warning: If too many messages come in at one time, the macro could fail.
If you need to filter the messages that added to the spreadsheet you have two options: use an If statement to exit the macro or convert it to a Run a Rule script.
If you use an if Statement, it should be the first line of the bjItems_ItemAdd macro.
Private Sub objItems_ItemAdd(ByVal Item As Object)
If InStr(1, Item.Subject, "Tip") = 0 Then Exit Sub
For a run a script rule, delete Private Sub objItems_ItemAdd(ByVal Item As Object) and all of the lines above it then use this as the macro name and create your rule.
Public Sub ShowMessage(Item As Outlook.MailItem)
Option Explicit
Private objNS As Outlook.NameSpace
Private WithEvents objItems As Outlook.Items
Private Sub Application_Startup()
Dim objWatchFolder As Outlook.Folder
Set objNS = Application.GetNamespace("MAPI")
'Set the folder and items to watch:
' Use this for a folder in your default data file
Set objWatchFolder = objNS.GetDefaultFolder(olFolderInbox)
' to watch a folder in a non-default data file
' seehttp://slipstick.me/qf for GetFolderPath Function
' Set objWatchFolder = GetFolderPath("me@domain.com\Inbox")
Set objItems = objWatchFolder.Items
Set objWatchFolder = Nothing
End Sub
Private Sub objItems_ItemAdd(ByVal Item As Object)
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String
Dim strColB, strColC, strColD, strColE, strColF As String
' Get Excel set up
enviro = CStr(Environ("USERPROFILE"))
'the path of the workbook
strPath = enviro & "\Documents\test.xlsx"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Sheet1")
' Process the message record
On Error Resume Next
'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
'needed for Exchange 2016. Remove if causing blank lines.
rCount = rCount + 1
'collect the fields
strColC = Item.SenderEmailAddress
strColB = Item.SenderName
strColD = Item.Body
strColE = Item.To
strColF = Item.ReceivedTime
' Get the Exchange address
' if not using Exchange, this block can be removed
Dim olEU As Outlook.ExchangeUser
Dim oEDL As Outlook.ExchangeDistributionList
Dim recip As Outlook.Recipient
Set recip = Application.session.CreateRecipient(strColC)
If InStr(1, strColC, "/") > 0 Then
' if exchange, get smtp address
Select Case recip.AddressEntry.AddressEntryUserType
Case OlAddressEntryUserType.olExchangeUserAddressEntry
Set olEU = recip.AddressEntry.GetExchangeUser
If Not (olEU Is Nothing) Then
strColC = olEU.PrimarySmtpAddress
End If
Case OlAddressEntryUserType.olOutlookContactAddressEntry
Set olEU = recip.AddressEntry.GetExchangeUser
If Not (olEU Is Nothing) Then
strColC = olEU.PrimarySmtpAddress
End If
Case OlAddressEntryUserType.olExchangeDistributionListAddressEntry
Set oEDL = recip.AddressEntry.GetExchangeDistributionList
If Not (oEDL Is Nothing) Then
strColC = olEU.PrimarySmtpAddress
End If
End Select
End If
' End Exchange section
'write them in the excel sheet
xlSheet.Range("B" & rCount) = strColB
xlSheet.Range("c" & rCount) = strColC
xlSheet.Range("d" & rCount) = strColD
xlSheet.Range("e" & rCount) = strColE
xlSheet.Range("f" & rCount) = strColF
'Next row
rCount = rCount + 1
xlWB.Close 1
If bXStarted Then
xlApp.Quit
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
Martin says
Hi Diane - I love your work, and maybe depend on it a little too much!
I'm having an odd issue with your code.
When I have a block of emails selected (e.g. a month's worth), the macro gets to a point as it moves down the list of emails where it will not update the sender / sender address / message body / Sent To values BUT the Received Time will properly output row-by-row.
It isn't the particular email that is causing issues, because if I select the day the email is sent, I will get proper output - one spreadsheet row per email.
Because it works up to a point and then stops working for some (not all) of the olItem fields, I can't even begin to troubleshoot it. Can you help?
Diane Poremsky says
Any idea what the point is? A few others reported problems at something like 200 messages - I understood for them that it just stopped - not that one field worked. I exported some 40,000 messages last weeks, no problem (it took 15 min or more to complete.)
Are the messages all emails? If you have reports, meeting invites or other non-mail mailed items, it should error but could be trying to export them - they don't have all the same fields though. (Should have sender, subject, and date.)
if you need non-mail items exported, change Dim olItem As Outlook.MailItem to Dim olItem As Object, if you don't need them exported, make this change:
For Each obj In Selection
if obj,messageclass = "IPM.Note" then
Set olItem = obj
----snipped---
rCount = rCount + 1
end if
Next
Martin says
Hi Diane
I don't know if the original commenter was me or not, but I have a suspiciously similar problem after 244 rows output out of 2016 total. Have tried the two variations you have mentioned above, with no success. Did you ever get a fix that worked for the other people you mentioned?
Diane Poremsky says
No, not that I know of.
It always dies after 244 rows?
Martin says
Just run it again with a different selection of emails (a filtered month; 381 total), sorted ascending.
This time, row 248 (including header) is the last row with different sender/sender address/message body/sent to. These values just repeat from row 249 onward (as mentioned, only the Received Time corresponds to an email after this point).
Do the same thing again with that filtered month but in descending order, and the last properly output row is row 238.
Perplexing.
Martin says
FYI, 248 ascending vs 238 descending is not a typo.
Muhammad K Khan says
hi how can i get only email address (to,cc,from field) all folders using that script, and duplicate removal.
Diane Poremsky says
If you want all addresses it was sent to, you need to get the recipients.
'### Get all recipient addresses
' instead of To names
Dim strRecipients As String
Dim Recipient As Outlook.Recipient
For Each Recipient In olItem.Recipients
strRecipients = Recipient.Address & "; " & strRecipients
Next Recipient
strColD = strRecipients
'### end all recipients addresses
Remove the fields you don't want here -
'write them in the excel sheet
xlSheet.Range("A" & rCount) = strColA ' sender name
xlSheet.Range("B" & rCount) = strColB ' sender address
xlSheet.Range("C" & rCount) = strColC ' message body
xlSheet.Range("D" & rCount) = strColD ' sent to
xlSheet.Range("E" & rCount) = strColE ' recieved time
N Pacheco says
Hello. In your email, you know how you can put a category heading on an email... is there a way to import that information as well as the subject, body, and sender into Excel? Thanks....
Diane Poremsky says
Dang clipboard = nothing like pasting the wrong answer. :)
Yes, you can get the categories - it is .categories:
For example:
strColE = olItem.Categories
Diane Poremsky says
The export function built into outlook would include it - but the body field is messed up when exporting.
The macro would use this;
'collect the fields
strColC = Item.SenderEmailAddress
strColB = Item.SenderName
strColD = Item.Body
strColE = Item.To
strColF = Item.ReceivedTime
strColG = Item.Categories
'write them in the excel sheet
xlSheet.Range("B" & rCount) = strColB
xlSheet.Range("c" & rCount) = strColC
xlSheet.Range("d" & rCount) = strColD
xlSheet.Range("e" & rCount) = strColE
xlSheet.Range("f" & rCount) = strColF
xlSheet.Range("g" & rCount) = strColG
Iuri says
Hey there, nice addition btw !
Also wanted to know like Tommy if the "in Folder" field is possible to be added through macro.
Diane Poremsky says
item.Parent will get the folder name the item is in.
item.Parent.FolderPath gets the folder path
RHarrill says
Hi Diane Thank you for all the great vba code for exporting emails to Excel. It has been years since I coded with vba so your examples made the project go much faster. I am having one issue. The Received Date in the email is in UK date format dd-mm-yyyy. When it exports, it exports to a USA date format - mm-dd-yyyy. So 08-10-2020 becomes 10-08-2020 so to us - that reads 10 Aug 2020 not 8 Oct 2020. Changing the format in Exceldoes not get around this problem. Do you have any ideas on how to resolve? Kind regards
Diane Poremsky says
Try using this:
Format(olItem.ReceivedTime, "dd-mm-yyyy")
Tim says
Is it possible to grab the data from .msg files within a folder rather than from Outlook?
Diane Poremsky says
Get the Send/receive date msg files saved on the computer? Only if you open the message - its not saved in the metadata exposed in the file system. So yeah, its possible, but is a bit slower.
Dianne says
Oh this is what I'm looking for but need to add other fields. Category, Name of Attachment, In Folder, In Sub-folder.
Please can you help?
Tommy says
Hi Dianne,
Thanks for the great code and your assume comments!
Just one thing i could not find back on your site.
How can i loop trough folders directly underneed from an shared EX mailbox?
->Inbox (loop trough each mail)
------->folder 1 to 40 (loop trough each mail)
Thank You!!!!!!!
Tommy says
In addition, this is what i use successfully to access the inbox.
Set OutlookApp = New Outlook.ApplicationSet OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set objOwner = OutlookNamespace.CreateRecipient("test@test.com")
Set objFolder = OutlookNamespace.GetSharedDefaultFolder(objOwner, olFolderInbox)
Set objItems = objFolder.Items
For Each obj In objItems
Set olItem = obj
Tommy says
Hey Dianne,
Now iam baffled. If i run the code, slight modified in outlook, no problems.
If i run it trough excel, i get only the Inbox, not the sub-folders.
objFolder shows only 1 folder, not my 40 sub-folders. Any thoughts?
Tommy says
Hi Dianne,
Nevermind got it.
VBA is awsume, i had no idea how cool it is!!!!
Dianne says
I was so excited to find this. You have saved me. This was all manual. I still have some manual parts to it unless you can figure out a way to help modify it? I'd like to grab the category, list the name of any attachments and what folder it's in. I hope you still read this.
I seem to have a bit of a problems sometimes, though, it causes an error and will only copy what's in the in box. What am I doing wrong?
Diane Poremsky says
>>
I'd like to grab the category, list the name of any attachments and what folder it's in. I hope you still read this.
>>
I still read the comments, although I'm sometimes slow. :)
Two are easy:
olItem.Categories
olitem.parent
xlSheet.Range("F" & rCount) = olItem.Categories
xlSheet.Range("G" & rCount) = olItem.Parent
For attachments, you need to loop the attachment collection.
Dim oAtt As Attachment
Dim strAtt As String
For Each oAtt In olItem.Attachments
strAtt = strAtt & ";" & oAtt.FileName
Next oAtt
xlSheet.Range("H" & rCount) = strAtt
Katie Cox says
Thank you for the code! When I am trying to use the attachment code above, instead of returning only the attachments for a particular, it is including each attachment in sequence (i.e. the first row of data has 3 attachments, the 2nd row has those same 3 plus the new one, etc.). Any help to fix this problem?
Ashraf says
Firstly, thank you for the great code. It should make my life a lot easier.
Secondly, is there a way to process the Recipient email address in the same way as the senders'.
Currently, what I'm getting in the recipient column is /O="Organization Name"/OU="Organization location"/cn=Recipients/cn="recipients' short name"
Diane Poremsky says
Sure, its just a matter of adding fields and repeating the Exchange code block. You will need to break the recipients list into individual names and test each separately.
You need to test for the / after this line:
For Each Recipient In olItem.Recipients
and get the smtp before moving on to the next recipient.
Ashraf says
Thank you for the help. I was able to get it working. Recipients outside the organization still appear in the same old way (with different OU value) but I guess I'll have to work with that and fix it manually.
Thank you again.
Diane Poremsky says
This macro works here - to convert both sender and recipient addresses to the smtp.
Diane Poremsky says
BTW, addresses from outside your org should not have an x500 address - only internal senders.
Ashraf says
Its not exactly outside the organization, just a different division.
OU for my division - ou=head office
OU for addresses outlook notifies as external - ou=Exchange Administrative Group
The code you provided actually skips the recipients with the 2nd OU. It doesn't meet any of the cases (checked using debug).
Thank you for taking the time to help.
Diane Poremsky says
The second OU is not in the AD your account accesses? It would fail it if can't find it - you'd need to have an option for if not found.
If (oEDL Is Nothing) Then
' trim the junk from the line and use the alias
or check the ou and skip if not in your division.
Ashraf says
While checking the code using debug I found that when checking the recipients who are labelled as outside my organization (diff. division) the cases are tested for match but none match the criteria and the value is left blank. What I decided to do was assign the value of "olEU" (I wanted that one) and then test it if there's still 'cn=' in it, if so then get me whatever's after the last '='.
Diane Poremsky says
>>
when checking the recipients who are labelled as outside my organization (diff. division) the cases are tested for match but none match the criteria and the value is left blank.
>>
It's blank because the user is not in your GAL.
I'd probably do an 'if blank, then use the alias' .
Ashraf says
I'll try that one. Thank you.
JOms says
I'm currently using this one for production logs i just want to ask if there's a way that i can get it based on specific range of dates only?
Diane Poremsky says
yes, you either need to filter or use an if statement. Filter will be faster if you have a lot of messages in the folder.
I don't have any samples that use email published (that I recall) - this shows how to do it - just need to change the folders
https://www.slipstick.com/developer/print-list-recurring-dates-vba/
Samir says
The string "strColD = olItem.To" is retrieving addresses from CC and other addresses from a mail chain. Please help me refine it?
Diane Poremsky says
You just want the To address, not all of the recipients?
To'd need to check for position of the recipient -
For Each Recipient In olItem.Recipients
If Recipient.Type = olTo then
'rest of code here
end if
Hariharan says
Hi, I am from non IT experience holder but i need the code to pull report, like.
Folder, From, Subject, Received, Replied Time 1, Replied Time 2, Replied Count, Categories
Example screen shot attached Please help me to run macro and it is purpose to measure the time taken to response the received email.
Diane Poremsky says
You'll need to add the "last verb" (last action - reply or forward) to export - I have some samples that get that value. ( The macro at https://www.slipstick.com/developer/code-samples/forward-messages-not-replied/ has an example of using last verb. )
There is only 1 last_verb per message. If you reply twice, only the last one is 'saved' - you'd need to get the time of the first from the sent item.
I don't think we can get the replied count easily using Outlook VBA - you could do in Excel - match to/from/subject fields.
I'll work on updating the macro to include the replied to time information.
hariharan says
Please ref the attached screenshot, One of my frd have this last verb option but I don't have that option,please help me to add that Column in outlook and how to do that.
He have the option to add replied On to find the mail replied time.
Diane Poremsky says
The field is not normally exposed in the interface - He installed one of the CFG files here:
https://www.slipstick.com/exchange/adding-extended-mapi-fields-to-outlook/
Diane Poremsky says
You need to install the CFG on this page:
https://www.slipstick.com/exchange/adding-extended-mapi-fields-to-outlook/
chris Tipping says
Hi Diane - love your code, a couple of questions, I currently have an auto script running and saving to excel on all emails:
1) How do i input an if statement, to select a subject line contain different words ie it may contain "enquiry", "booking" or "availability", I only want to pull these emails in no others.
2) Each email contains various booking information, what is the best way to pull this out if they are not the same format?
Thanks in advance.
Diane Poremsky says
The first is easy:
if instr(1, item.subject, "booking") > 0 or instr(1, item.subject, "enquiry") > 0 or instr(1, item.subject, "availability") > 0 then
' do whatever
end if
if you have more words, an array is better - i have samples of how do to arrays on this site.
2 will require regex. I have a sample at https://www.slipstick.com/developer/regex-parse-message-text/ - there is one sample that uses select case to get different values
chris Tipping says
Cool thanks - so with the if statement does that go at the begging of the whole macro and in the ThisOutlookSession code?
Diane Poremsky says
I'd put it before the excel workbook code (and the end if after the workbook is closed) - but if you are using the run a script rule, you can set up the rule to find the words in the subject.
charlie says
I get an issue running the excel with an error 438 (object does not support...) the error stop in strReceived = olItem.ReceivedTime
thanks
Diane Poremsky says
is the outlook item and email message? That error can be triggered if the item doesn't have that field.
mike says
Hello there, great macro. I've got pretty similar one. Have You got any idea of how to add a column with recipient's email address? The column 'Item.to' mostly gives just their names, but i need e-mail addresses. Thank You.
Diane Poremsky says
You need to get the recipients collection - the first macro has code that gets the addresses:
'### Get all recipient addresses
' instead of To names
Dim strRecipients As String
Dim Recipient As Outlook.Recipient
For Each Recipient In olItem.Recipients
strRecipients = Recipient.Address & "; " & strRecipients
Next Recipient
priyanka says
how to save email ID of sender in to excel by clicking a button on email.
Diane Poremsky says
The first macro runs on a selection - it can be one or more messages, so it will do what you want - if you don't need all of the fields, or need other fields, delete the lines - specifically the ones that write to excel, but can also remove the ones that set the variables to outlook fields.
if you want to run it on open messages, you need to either change the selection lines or use the objItems_ItemAdd macro in the itemadd section - then use a stub macro to call it.
Sub RunScript()Dim objApp As Outlook.Application
Dim objItem As Object ' MailItem
Set objApp = Application
'selected item
'Set objItem = objApp.ActiveExplorer.Selection.Item(1)
'open item
Set objItem = objApp.ActiveInspector.CurrentItem
'macro name you want to run goes here
objItems_ItemAdd objItem
End Sub
https://www.slipstick.com/developer/outlook-vba-work-with-open-item-or-select-item/
Jeff says
Hi! Great topic! I am just wondering if we can import if there are images on the email's body. Currently it is only working on the text. Is there a way we can fix this?
Diane Poremsky says
This line: strColC = olItem.Body tells it to use plain text body. Change it to strColC = olItem.HTMLBody and it will get the raw HTML (not pretty). If you are looking for more of a copy & paste, when the HTML is formatted, you'll need to use word object model.
khan says
Hi, Thank you for the code. It's helped a lot in my workplace. I did have a question, I've currently got the code saved. However, when i try to update the log it seems to add the same e-mail in again. Is there a way that it can recognise not to add the same e-mail twice?
Diane Poremsky says
This is happening when you run it at a later time? The only way to avoid it is to set a value - can be a category or custom field - and only add the ones that don't have the field set. If you run it say, once a week, you could check the dates and only process mail that arrived after that date.
khan says
Thank you for your prompt response, If I run the script. Then run it again when another e-mail has arrived it will add the first e-mail again. I hope that makes sense. Or once the e-mail has been saved to the spreadsheet I could remove it from the inbox and this would ultimately solve the solution.
Fernando says
Hello Diane
I'm using this script but I need to add some fields, if it have a cost to customize can you tell me how much cost to do please.
I need to add if it was responded or not, and when I respond an email update the last two fields of the Excel file.
Is it possible? I'm using Outlook 2013 and my accounts are configured POP.
Please if is difficult to do the customization tell me how much cost to do.
Thanks
Diane Poremsky says
You'll use the Last_Verb values to find out if it was acted on -
Set propertyAccessor = objVariant.propertyAccessor
lastverbtime = "http://schemas.microsoft.com/mapi/proptag/0x10820040")
lastverb = "http://schemas.microsoft.com/mapi/proptag/0x10810003"
lastaction = propertyAccessor.GetProperty(lastverb)
' 102, 103, 104 are replied, forwarded, reply all
You'll need to do something like if value = "102" then value = "Replied" to get the words. I forget offhand how the date is formatting - I think short date/time, if so it won't need to be formatted.
more info on getting other fields not exposed in the object model is here: https://www.slipstick.com/developer/read-mapi-properties-exposed-outlooks-object-model/
Diane Poremsky says
BTW, I am available to do custom work - I charge $100/hour for short projects and a discounted rate for projects that take longer than an hour or two.
Alex H says
Hi! Thank you for this great macro! Especially the linked one at https://www.slipstick.com/macros/CopyToExcel-selectedfolder.txt is awesome! How can I make it loop through all the subfolders?
Thx!
Alex
Diane Poremsky says
Sorry, I thought I answered this before - you'll need to use a script that loops the folders, such as shown here: https://www.slipstick.com/developer/print-list-of-outlook-folders/ - you'd call the Excel macro where the debug.print line is.
There is also a code sample here: http://vboffice.net/en/developers/looping-recursively-through-folders-and-subfolders (mine is probably built off of Michael's - but has a lot of garbage in it you don't need.
Casey says
I have a form that employees send in to make a change or set up an additional payroll that is currently logged manually into excel. I tried setting up the macro that you have with the tweaks on the column headers but I am working in 2016 and it just is not liking this portion. Right now this is an extremely tedius process and I am trying to work smarter not harder.
Set objOL = Outlook.Application
Set objFolder = objOL.ActiveExplorer.PDCR
Set objItems = objFolder.Items
For Each obj In objItems
Set olItem = obj
strColA = olItem.SenderName
End If
Case OlAddressEntryUserType.olOutlookContactAddressEntry
Set olEU = recip.AddressEntry.GetExchangeUser
If Not (olEU Is Nothing) Then
strColB = olEU.PrimarySmtpAddress
End If
Case OlAddressEntryUserType.olExchangeDistributionListAddressEntry
Set oEDL = recip.AddressEntry.GetExchangeDistributionList
If Not (oEDL Is Nothing) Then
strColB = olEU.PrimarySmtpAddress
End If
End Select
End If
Diane Poremsky says
So its failing on getting the sender's address? Any error messages?
This is incorrect:Set objFolder = objOL.ActiveExplorer.PDCR
Using Set objFolder = objOL.ActiveExplorer will allow you to run it on the folder you are currently viewing.
Hrvoje says
Hi,
I use macro workbook template to extract emails from outlook but now my email, precisely folder from I extracted messages, run and load messages very slowly. Can someone help me with some solution how can it be fixed?
Thank you in advance
Diane Poremsky says
The folder that you previously used a macro on now redraws the screen slowly? Try resetting the view on the folder.
Sharath says
I am getting an error message in this line
strBody = olItem.Body
Error message is Run time Error "287"
Application-Defined Or object defined error
Diane Poremsky says
What version of outlook do you use? Do you have a valid antivirus installed? This error can mean that outlook is waiting for a response to the security dialog - but that dialog shouldn't come up with newer versions of outlook if you have a valid antivirus installed.
Diane Poremsky says
BTW, does it error on any specific line? If you are trying the sample workbook version, make sure you set the references to the correct object libraries in tools, references.
Sharath says
I am getting an error message in this line
strBody = olItem.Body
Error message is Run time Error "287"
Application-Defined Or object defined error
Diane Poremsky says
does it work on some messages? you might get errors on non-email items that have different field, but it should work for body.
Sharath says
I am using MS 2013
And i am facing this issue with some fields like Body, TO and CC and the macro is able to get data for the remaining fields
Diane Poremsky says
the macro gets these fields - but you can add more - check the object model help for the field names to use if you don't know the field names.
strColA = olItem.SenderName
strColB = olItem.SenderEmailAddress
strColC = olItem.Body
strColD = olItem.To
strColE = olItem.ReceivedTime
Sharath says
i tired using the file and the above macros but its not coping Sender name Body of the email as well rest of the code is working file can some please help out
Jaybo says
I just need to pull the sender IP from the email header information into excel so I can use them to build a blacklist for spam emails. Can I add something in this macro to accomplish this or run a similar macro to just pull the sender IP from messages in a particular folder?
Diane Poremsky says
You'll need to use regex to grab the ip from the header. See https://www.slipstick.com/developer/code-samples/outlooks-internet-headers/ for sample code - it doesn't get the ip, but you'll just need to set up the correct pattern.
Scott Goodman says
Hi Diane, can you advise how I can also transfer the information in the 'Categories' column as these are marked with a 'quick clik' for each user as they open an email.
Thank you
Diane Poremsky says
that field is olItem.Categories Either assign it to a variable then a cell, or use
xlSheet.Range("F" & rCount) = olItem.Categories
(assigning to a variable is recommended but either method works).
If they have multiple categories assigned, all will be added to the cell.
Pablo Sepulveda (Chile) says
Thanks for your post! I used it and y did a kind of mix to achive what i wanted to do.
I have one last question though. i really dont know how to export the bounced emails. I dont hace any problem with the other emails, but with the bounced ones, the code simply doesnt export them. I have reading some website and y think its becase the bounced email are a kind of "report items" and not a "emails items".
Do you know how can i solved this?
Thanks for everything, u help me a lot!!!
Diane Poremsky says
Correct, bounced mails aren't mailitems, they are reports. The object class is olReport - other classes of non-mail items that might be in the mailbox are olMeetingResponseNegative, olMeetingResponsePositive, olMeetingCancellation, olMeetingRequest, olMeetingAccepted, olMeetingTentative.
You will probably need to use a case statement to get the correct field names, but start by changing this line: Dim olItem As Outlook.MailItem to Dim olItem As object. That will pick up all but may error on this lines:
Set olItem = obj
'collect the fields
strColA = olItem.SenderName
strColB = olItem.SenderEmailAddress
strColC = olItem.Body
strColD = olItem.To
strColE = olItem.ReceivedTime
You can use case statements to get the correct fields for different items. The macro at https://www.slipstick.com/developer/macro-move-aged-mail/#case has an example of case statements eith the different items - the fields that fail using object will be in the case statement with the correct field name.
Rob P. says
Hi Diane, looks like my first attempt at posting didn't take, apologies if this is a duplicate.
Thank you for all of the wonderful insight on Outlook programming...I've learned a ton from your posts and I can't thank you enough for it.
I have a challenge that's a little off-topic for this particular post, but it stems from my trying to accomplish a nearly identical report to what you've shared above. Please feel free to redirect me to another post if appropriate.
My challenge is persisting a few custom text values to an Appointment item (IPM.Appointment) via custom fields I've added to a custom form region. The values persist and display properly in my Outlook views ("Calendar" and "List") however they are not present when I try to access them via the macro.
If I edit the custom field values directly in the List view, *then* my macro can read them. Any subsequent updates from the form region persist just fine. Thus it would seem that the custom properties simply are not being *added* to the Appointment item via my form. Is that expected behavior? Do I need to add custom code to my Appointment form to grab the custom field values and explicitly create the properties on the Appointment item if they are not already present?
Thank you so much in advance. This has been driving me nuts for days...my sanity thanks you as well! :)
Diane Poremsky says
it took - i was offline most of the weekend and everything stays in the moderation queue until i answer it, because it's easier to find the comments that need answered. :)
I'll need to test it (I don't work with form regions much)... are you adding the value to a field or leaving it in the control?
Rob P. says
Thanks so much Diane. I'm not sure how to answer that last bit though...I have a field on a custom form region and the field is bound to a control (text). If I enter an initial value via the field, the value appears in the control (and persists in the field) but is not accessible via my macro. Only once I have entered or edited the value directly in the control does the value become available in the macro.
Thank you!
Diane Poremsky says
How are you referencing the field? Just using the field name or by the control name? I think you need to use something like this:
str = formRegion1.textBox1.value (or str = formRegion1.textBox1.Text)
not using userproperties: str = obj.UserProperties("fieldname").Value
Carlos Firmino says
I have a little doubt.
I need to know the email address sent, when I use To, it returns the information of the sent email name. How can I get him to bring the email address he sent?
Diane Poremsky says
Do you need all sent to addresses (assuming it was sent to multiple people) or just your address it was sent to? To get all addresses, you need to use the recipient collection; for your address, you can use propertyaccessor.
Carlos Firmino says
Sensational! Thank you! the spreadsheet in Excel worked perfectly!
Lauren.C says
Hi, How would i be able to see the category and sub-folder the emails are in also?
Diane Poremsky says
Category is olItem.Categories, folder is olItem.Parent, or if you want the folder path, olItem.Parent.FolderPath
Dhawal says
Many Thanks for the great script !!
I am able to capture details when any user / tool have sent emails.
But I am unable to capture details for Undeliverable emails coming to my mailbox.
We are tracking inactive people for whom we are getting undeliverable emails.
Diane Poremsky says
That's because NDRs are reports, not mail items. Change Dim olItem As Outlook.MailItem to Dim olItem As Object and it should work - note that you need to add error handling for fields that are not supported in reports.
Kavish says
Hi Diane,
I downloaded the template file and when I run the code it only brings the date time and subject and is showing a run time error 287 (Application defined or object defined error. I've selected the Microsoft outlook 15 object library from VBA reference. I've installed MS office professional plus 2013.
Thnx
Diane Poremsky says
Regular expressions is set in VBA references, so its not that. Step into the macro - which line does it quit on? is the outlook message a normal email? it may fail on read recipients, ndrs etc.
Moorthy P says
xlSheet.Range("A1") = "Sender Name"
xlSheet.Range("B1") = "Sender Email"
xlSheet.Range("C1") = "Subject"
xlSheet.Range("D1") = "Body"
xlSheet.Range("E1") = "Sent To"
xlSheet.Range("F1") = "Sent To Email" (This column i want add )
xlSheet.Range("G1") = "Date"
xlSheet.Range("H1") = "Categories"
Diane Poremsky says
You'll need to get the recipients collection and the .address field. I have samples on the site that get the addresses, such as https://www.slipstick.com/developer/recipient-email-address-sent-items/ - it just needs worked into this macro.
Moorthy P says
Hi Diane,
How can i add to mail address.
Regards,
Moorthy P
Gerrit says
Thank you for te nice script. I have a question. In stead of the display name i need the e-mailaddress. Can you tell me if that is possible and how?
Diane Poremsky says
This is the sender address:
strColC = olItem.SenderEmailAddress
If you need the recipients, you will need to go through the recipients collection.
Gerrit says
Thank you for your answer. Regarding the sender adress no problems, it works fine. I'm less successful with the recipients. Can you help me with what and where to place the code?
Diane Poremsky says
You'd add this before the exchange address code
'### Get all recipients
Dim strRecipients As String
Dim Recipient As Outlook.Recipient
For Each Recipient In olItem.Recipients
strRecipients = Recipient.Address & "; " & strRecipients
Next Recipient
strColD = strRecipients
'### end all recipients
Shree says
Hi,
Can you help me how exactly i can replace the "Display Name" to "Display Address" in all 'From' , 'To' and 'CC'
Since i am not a coder i couldn't implement the one liner solution which you have given.
Diane Poremsky says
the code for the from has always been in the macro - its the senderemailaddress. Getting the recipients needs more code - i added to the macro on the page last week. This doesn't split it between to and cc addresses though.
'### Get all recipient addresses
' instead of To names
Dim strRecipients As String
Dim Recipient As Outlook.Recipient
For Each Recipient In olItem.Recipients
strRecipients = Recipient.Address & "; " & strRecipients
Next Recipient
strColD = strRecipients
'### end all recipients addresses
To split the addresses into to and cc, you'd need to replace strRecipients = Recipient.Address & "; " & strRecipients with this:
If Recipient.Type = olTo Then
strTo = Recipient.Address & ";" & strTo
ElseIf Recipient.Type = olCC Then
strCC = Recipient.Address & ";" & strCC
End If
Next
then add the values to the columns
strColD = strTo
strColE = strCC
Luis Olivas says
Hello great info, just one Q,
What lines should i add to compare the info already in excel?
I mean, i want to copy my whole inbox, but if i would run this macro again tomorrow
i'll have yesterday's emails listed again.
Hope is not confusing.. i.e.
run macro day 1
cell a| cell b
1 sender email
2 sender email
Run macro day 2
expectation
cell a| cell b
1 sender email
2 sender email
3 sender email
4 sender email
Reality
cell a| cell b
1 sender email day 1 email
2 sender email day 1 email
1 sender email day 1 email
2 sender email day 1 email
3 sender email day 2email
4 sender email day 2 email
Greetings.
Diane Poremsky says
if you run it daily, you could check the received date and only process mail received today (or yesterday) - or you should be able to get the received date of the last message from the workbook and use that as the date to process newer mail.
Another option is to set a custom field or category on the messages as they are added to Excel and only process mail without that value or category.
Veer says
Hey ,
i want to import mail from outlook to excel and this need to be moved to a outlook folder on selcting the name of the folder by a dropdown. i have two buttons "Copy mails " and assign
" on clicking copy mail i am able to fetch mail details on excel but i am not able to figure out how to assign the mail to folders can anyone please help me with this ?
Diane Poremsky says
The dropdown is in excel or in outlook? You could pass the folder name but to outlook from excel - it would be easiest if you select the folder immediately after importing the mail so outlook knowns which message you need to move though, unless you also record the message id and do a look up.
Dave says
Came across this thread and was wowed with the results. Thanks. My question is this: anyway to point this to edit in excel online?
I plan on updating a shared sheet as multiple people are working on the same excel sheet. Thanks
Diane Poremsky says
Not that i know of. You can write to a file stored online if you have the path (or its synced to the desktop) but you need to use desktop excel.
Dave says
thanks for all your help :)
James Kong says
hi
1) i am getting the following error when running the file
Compile Error:Can't find object or library and highlights
Dim objOL As Outlook.Application
how do i solve this
2) is there a macro to send the list back to outlook
Diane Poremsky says
That means you did not set a reference to the outlook object model in the VB Editor's Tools, References dialog.
In what format do you want it back in outlook? You can't import CSV files to email - a lot of data will be lost.
Swathi says
Hi I have a doubt regarding outlook user forms.I want a user form which contains some text boxes and some combo boxes and a submit button.once the data is filled and the submit button is clicked the data should get saved to the last line of an excel sheet placed in a SharePoint.
Is there any way to do this?
Diane Poremsky says
it's definitely doable - will be easiest (I think) if the sharepoint file is synced using one drive (so you can use a local path to access it). For the save to sharepoint part, you can record the macro in Excel then tweak it to work in outlook.
shudeepta says
Hello - I am getting a compile error method data not found. This is in excel .MultiLine = True
Diane Poremsky says
Try removing the leading blank space and the blank line above it - sometimes the code gets weird character when you copy from the webpage.
Carlos says
Hi,
Im just getting this error when a i run the macro, any reason of why is this happening?
Compile error: Can´t find project or library on this line Dim objOL as Outlook.Application
Thanks
Diane Poremsky says
did you add a reference to outlook object library in Excel's VB editor's tools, references?
Carlos says
Hi,
When I go to Reference on VB editor it says "MISSING: Microsoft Outlook 15.0 Object Library", how can I fix this?
Thanks for your help
Diane Poremsky says
This is in thw Excel workbook sample? That's the version i wrote it in - In the Excel VB Editor, look on Tools, References- find the outlook object library in the list. if you are using a different version of office, untick the outlook 15 object model and find the correct one.
Riaan Joubert says
I tried to run the copymailtoexcel macro but get a compile error on the statemen
Dim Reg1 As RegExp "User defined type not defined"
Diane Poremsky says
You need to go to the VB Editor's Tools menu, select References and select the object mode - if you are using an Excel macro to access outlook, you need to select the outlook object library, if you are using Outlook to send stuff to excel, you need to use the excel object model.
Jeff Kohler says
Hi. I think this macro is what I need. I am using MS Professional Office 2010. My Outlook receives emails from CrawfordChesterCounty@gmail and it is IMAP/SMTP. My goal is this: I have 5,000 emails and I want to COUNT ALL EMAIL BY SENDER. I think your macro may be what I need but I am getting an error and I don't know how to fix it. The error is: "Compile error. Can't find project or library" and in the Visual Basic code, the first two lines are highlighted, "Public Sub CopyMailtoExcel(), Dim objOL As Outlook.Application". Will this macro give me what I need? and How can I fix my error? Thank you.
Diane Poremsky says
Did you put the macro in outlook's VB editor or Excel's? Did you set a reference to outlook object library in Excel's VB editor's tools, references?
Rajat Mahajan says
Hi Diane,
Would this work on webmail outlook as well??
Thanks,
Diane Poremsky says
No, you can't run macros on web mail, you can only run them in outlook.
Raju says
Am I missing or failing to understand here: why column A is never been used? can you help me understand please? Thanks.
Diane Poremsky says
The reason it was left open was to allow a column for notes or otherwise mark certain messages. You can adjust the code that writes the values to columns if you want to put a value into it.
Raju says
Thanks for the clarification, got it.
Chris says
Hi Diane,
I am unable to extract location and recurrence (Appointment Items) from a search folder that I have selected. Any ideas?
Thanks,
Chris
Diane Poremsky says
It won't work on Instant search results - you need to run it on the folder, not the results. It will work on a mail search folder. This line: Dim olItem As Outlook.MailItem tells it to look for mail items. Try changing the it to Dim olItem as object to use it with other items.
Chris says
I just used the code to export Outlook data to Excel and it worked like a charm. Windows 7, Excel 2013.
I've changed the code to pull data I need. Next, I'm going see if I can get it to pull from an updated search folder to pull emails and appointments with a due date within 10 hours and with category of "ongoing". I'll let you know if I get it to work.
Thank you Diane for your wisdom. I've resorted to your slipstick VBA docs for over a year now.
Carlo says
I've noticed when I run this Macro that after around 250 emails (between 243 and 256) it keeps returning the same sender email address. I can't work out why, is there any way to fix this? I'm at a loss.
Everything else here works perfectly.
Diane Poremsky says
Is it messing up other fields? Try adding strColC = "" right before it gets the address from the item. Does it get the correct address?
Julissa says
I have the same problem, since row 250 it starts to duplicate column A,B,C & D. I added at the end the following strColA = ""
strColB = ""
strColC = ""
strColD = ""
But the problem persist.
Could you help me?
Diane Poremsky says
BTW, if you want to run it from excel, try this macro - CopyMailtoExcel. You'll need to set reference in the workbook to Outlook and VB Reg Expressions (or remove that segment). It runs on all messages in the selected Outlook folder.
BTW - figured i should do a workbook template too - CopyOutlookMailtoExcel.xlsm
Fer says
Hello Diane!
Awesome information, I want to ask if Is it easy to add a column to write "Responded" when I replay to the email?
Diane Poremsky says
In Outlook? Sure. See https://www.slipstick.com/tutorial/create-a-custom-field-to-mark-messages/. if you mean in Excel, yes, you just need to add another column:" xlSheet.Range("g" & rCount) = "Responded" (you can get the date and type of response (FW, RE, or Reply all too)
Fer says
Hello Diane
Thank you :D
HIMANSHU HATHI says
Hello Diane,
I get email in format as below i need to get job name data and status exported to excell. Any suggestions ?
Email goes to specific folder per rule, so need to pick from that folder and export this information to excel.
Is it possible for you to write this kind macro for me, willing to pay.
Thank you in advnace
Backup job: USB-ALL-SERVERS (Full) Success
Created by man01 at 4/18/2016 8:35 PM. 10 of 10 VMs processed
Thursday, February 09, 2017 8:08:02 PM
Success 10 Start time 8:08:02 PM Total size 1.4 TB Backup size 254.4 GB
Warning 0 End time 12:10:02 AM (+1) Data read 1.3 TB Dedupe 3.4x
Error 0 Duration 4:02:00 Transferred 254.2 GB Compression 1.6x
Details
Name Status Start time End time Size Read Transferred Duration Details
linux Success 8:08:39 PM 8:29:43 PM 117.1 GB 117.1 GB 10.6 GB 0:21:03
DC01 Success 8:29:26 PM 8:36:59 PM 40.0 GB 39.3 GB 13.7 GB 0:07:33
EXC01 Success 8:36:42 PM 10:17:14 PM 600.0 GB 584.0 GB 140.4 GB 1:40:32
FS01 Success 10:16:31 PM 11:08:33 PM 300.0 GB 299.3 GB 21.2 GB :52:02
RDB01 Success 11:08:11 PM 11:15:44 PM 40.0 GB 39.3 GB 9.9 GB 0:07:32
RDS01 Success 11:15:27 PM 11:42:49 PM 100.0 GB 96.0 GB 23.8 GB 0:27:21
SHRVL Success 11:42:30 PM 11:47:02 PM 40.0 GB 21.5 GB 9.2 GB 0:04:31
ANT01 Success 11:46:46 PM 11:57:09 PM 60.0 GB 56.0 GB 11.9 GB 0:10:23
PHP01 Success 11:56:52 PM 11:59:32 PM 40.0 GB 11.1 GB 5.0 GB 0:02:40
PRT01 Success 11:59:17 PM 12:09:50 AM (+1) 60.0 GB 56.0 GB 8.6 GB 0:10:32
Diane Poremsky says
And item add macro can watch the folder and trigger the copy to excel macro. do you need all of the rows (and the entire line)?
Abi says
Hi Diane,
Thank you for sharing! I'm trying to modify the code so that I can export our contact categories into an excel sheet. Any tips?
Thanks!
Diane Poremsky says
You'd add a line (or change a line)
xlSheet.Range("g" & rCount) = olitem.categories
Mike says
Hi Diane,
This code seems to work for me if i wanted to do this manually. What I want to do is:
1. Look at unread emails from a specific inbox folder - still needed
2. Extract the ‘from’ address - done in the script
3. Extract the ‘from’ name - done in the script
4. Write to a file - done in the script
5. Mark email ‘read’ - still needed
6. Do this on a schedule (once a day) - HOW??
Very willing to pay for this :) I am not a codey person...
Thanks!
Diane Poremsky says
1. https://www.slipstick.com/developer/working-vba-nondefault-outlook-folders/ - use the getfolderpath function for folders not in your default mailbox. for unread message use an if statement for olItem.unread = true to only apply it to unread items.
5. olitem.unread = false after the item is written to the file
6. scheduling is harder - i usually use a task and a macro that looks for the task reminder. https://www.slipstick.com/developer/code-samples/running-outlook-macros-schedule/
Richard says
Hello.
Thank you, this code is very useful!.
But i have a question.
How can make this code Export the data of the folder and the subfolders. Any help would be appreciated.
Richard says
Hello,
thank you so much, this is very nice and useful code!
However how can i make this code to export all mails in the Subfolders, because all the mails are in the subfolders, i'm new VBA and any help would be appreciated
Jyotsna Gupta says
Hi,
I have copied above code and it worked, I have only one issue as I am getting only 1st mail from outlook to excel sheet (in a one row). How do I get all mails (Inbox) from outlook to excel? Please help
Kestas says
Hello,
thank you so much, this is very nice and useful code!
However, how should i tweak this code to make two following things:
1. clean the excel file before writing into it? (if i re-run the code, it adds more lines to excel, but i would like that every time code would clear the file before writing into it.
2. send the file to specific email address after the code is finished?
Diane Poremsky says
Both would be fairly easy to do -
This lines control the starting position -
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
change it to rCount = 2 to start at line 2
not exactly clearing... just overwriting.
this is the excel code for clearing contents - it needs tweaked to work from outlook though.
ActiveSheet.UsedRange.ClearContents
this sends a new message - https://www.slipstick.com/developer/create-a-new-message-using-vba/ - remove the sub name and end sub lines and put the code at the end of the code that copies the fields to excel.
Kestas says
Perfect, thank you once again! :)
Reer says
Hi Diane, I have process where we use generics mailbox for communication. Where we receive queries from customer and our job is to responds to those queries. Can I get a unique number for email where I can place pivot in excel to find out how many time that the communication happen(Back and forth)
Diane Poremsky says
Like add a code to the subject or message body of the initial message, as many support@ addresses do? sure. You can use a macro or a 3rd party utility to add the code.
https://www.slipstick.com/outlook/email/add-a-file-number-or-keyword-to-the-subject-line-of-messages/
Utilities that can do this are at https://www.slipstick.com/addins/help-desk-utilities-outlook-exchange-server/
Lambs says
Hi Diane, I am using Outlook/Excel 2010 and but currently its only grabbing the first email details, all the next emails are not populating. Ive copied your code exactly, and removed rCount but still no change.
Any help would be appreciated
Diane Poremsky says
Are you selecting the emails? This is telling it to work on the selection:
For Each obj In Selection
Set olItem = obj
Jyotsna Gupta says
Please let me know If you have done any code change for getting all mails from Inbox, as still I am getting only first mail from outlook to excel.
Leonardo Jardim says
Olá, eu testei o modo de selecção e ele funcionou perfeitamente, mas como eu deixar no automático? assim que eu receber determinado e-mail ele envia automatico para célula.
Hello, I tested the selection mode and it worked perfectly, but as I leave on automatic? Once I get given email it sends automatic to cell.
Diane Poremsky says
I added an automatic macro to the page - as written, its an itemadd macro but there are instructions to convert it to a run a script rule.
Kaden Peek says
Hi Diane,
Thank you for your code. It is working great so far. I had two quick questions for you, if you don't mind (I know a good deal of excel vba, but am relatively new to outlook):
1) If I already have the excel worksheet open that I am trying to import the emails to, how would I edit the line that opens the excel workbook again.
2) I want to edit this to become a run a script rule that works on incoming messages, but I haven't been able to figure out how to do it so far.
Any guidance you could provide would be great. Thanks!
Diane Poremsky says
This use it if open -
Set xlApp = GetObject(, "Excel.Application")
and if it errors (because its not open) - this opens it
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
if you want to use the current worksheet - you'd need to change these lines to use the active workbook and activesheet.
Set xlWB = xlApp.activeWorkbook 'not sure this is right
Set xlSheet = xlWB.ActiveSheet
run a script rules -
Sub CopyToExcel(olItem as outlook.mailitem)
then remove the lines that loops the selection.
the macro at https://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/ is a run a script. while not identical, it shows how its done.
Sophie says
Hello Diane,
I could not find my way to extract only a part of an Outlook mail into excel.
From such an outlook mail content:
4dd80745-640d-e611-9f4c-005056b07a94
yes
D671B005-D90D-E611-9F4C-005056B07A94
ADDITIONAL01-
HLLI01-Satisfied
HLLI02-Yes
HLLI03-Important
HLLI04a-Likely
SF01_r1-Satisfied
SF01_r2-Satisfied
SF01_r3-Satisfied
how to capture and send to 1 excel cell the range of text from the very first character (<) up to ADDITIONAL01- ?
Many thanks beforehands for your assistance.
B rgds,
Sophie Gassiot
Diane Poremsky says
You'll need to use RegEX (or Instr) to capture the data you want to send to Excel.
https://www.slipstick.com/developer/regex-parse-message-text/
Gaus says
Thank for above code. It is really easy our work. I am getting the required data except sender email ID. It shows like "/O=SYNTEL/OU=MUMHIRA-IND/CN=RECIPIENTS/CN=BROADCAST_BPOID". Please assist me and confirm as to what changes are required.
Diane Poremsky says
you need to get the smtp address - this macro shows how to get the recipient addresses and the sender's smtp address.
sender-recipients-sender-email.txt
Diane Poremsky says
As an FYI, i added the code to the macro in the article to get the exchange SMTP
Pooja says
Hey Diane,
Your code is amazing. It has every comment which makes it easier to understand.
I am trying to get the sender properties like sender's email address, job title, department. How can I get that?
Also I want the code to get details about all the emails in a folder and not just one email.
Thank you in advance! :)
Diane Poremsky says
you need to look up the contact and get the fields from the contact, if they exist. This macro shows the basics in looking up contacts - https://www.slipstick.com/developer/categorize-messages-using-contact-category/ - you'll assign the field values to variables and write them in the excel book.
Rehan says
Hi Diane,
How can I add email subject line to VBA code?
Regards,
Rehan
Diane Poremsky says
You'd add another line for the variable:
strColG = olItem.Subject
and add another line in the code that sends it to excel:
xlSheet.Range("g" & rCount) = strColg
Rehan says
Diane,
I tried making the changes as communicated but had no luck. Even I tried to replace the existing variable with subject to check but had no luck.
Best Regards,
Diane Poremsky says
Any error messages? Are the other fields captured?
does this work:
xlSheet.Range("g" & rCount) = olItem.Subject
Andres mora says
Diane,
This is by far the clearest example of outlook-excel vbas. Thanks.
Question: if a have a standard format in each body of the emails, for example:
Name: john
Adress: some adress
Age: 18
How can i format the code in a way to extract parts of the body of the mail for separate cells?
Thanks!
Diane Poremsky says
i prefer to use regex to get the values - the sample at https://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/ shows how to do it - but it uses one pattern that gets split - you'll want to use the sample at the end of https://www.slipstick.com/developer/regex-parse-message-text/ to get multiple values.
if i = 1 then strname = M.SubMatches(1)
if i = 2 then strAddress = M.SubMatches(1)
if i = 1 then strAge = M.SubMatches(1)
ChuahCS says
Dear Diane,
First, thanks for your sharing.
I have one question, I need to add additional field "Your replied on xxxx" to my tracker to export reply date and time. Can you enlighten me how I can do it?
Diane Poremsky says
You need to get the last verb values to check to see if the message was replied or reply all or Forward
Dim propertyAccessor As Outlook.propertyAccessor
Set propertyAccessor = olItem.propertyAccessor
lastverb = "https://schemas.microsoft.com/mapi/proptag/0x10810003" ' Reply = 102, Reply All = 103, Forward
lastaction = propertyAccessor.GetProperty(lastverb) ' this gets the time
Paula says
Thanks Diane , would you help me on how to combine this code to extract the replied time into your main code .. as I'm new to VBA
Diane Poremsky says
someone else asked about that too - will try to put something together over the next few days.
Vlad says
Hi Diane, any idea how to incorporate that in the code above , I am selecting the mails in the Sent inbox and the time I get in Excel is the time and date I have sent those emails. How to get the Time and date those mails have been received ? Thank you
Diane Poremsky says
Are you requesting read and/or delivery receipts? After the receipts are processed, the message would be update and we might be able to grab the tracking fields. (I will need to check.)
Jeanne Goodman says
Diane, this is brilliant and life-saving. I modified the script to work with Appointments and (after realizing I needed to change the declarations to
Dim olItem As Outlook.AppointmentItem
it worked beautifully.
Any chance you could help me to figure out how to get it to cycle through multiple calendars, search for a particular date (with a message box to prompt for the date), and then put that data in Excel?
Best,
Jeanne
Diane Poremsky says
This should kina give you an idea of how to walk calendars (as written, it checks for one that are selected) and how to search the calendar - https://www.slipstick.com/outlook/combine-outlook-calendars-print-one/
Larry says
Hi Diane, Very nice and helpful. One thing is I am attempting to export Outlook Report items from the Inbox, such as this one below which you receive from gmail. Your code does not export these messages can you suggest how I could export these.
Your message did not reach some or all of the intended recipients.
Subject: Any Subject
Sent: 7/26/2016 5:03 PM
The following recipient(s) cannot be reached:
'XXXXX@XXXX.COM' on 7/26/2016 5:03 PM
550 5.4.5 Daily user sending quota exceeded. c26sm1774750qte.1 - gsmtp
Diane Poremsky says
This: Dim olItem As Outlook.MailItem says to use mailitems. NDRs are not mail items. Either dim it as an object or use code that uses other item types too. (as long as the fields works, i'd change it to object.) Sample of a case statement that uses different item types is here - https://www.slipstick.com/developer/macro-move-aged-mail/#case
Jason W says
Hello! I tried the macro but i keep getting the error, Subscript out of range, do you know how to fix this?
Diane Poremsky says
What line does it quit on?
Does the workbook have a sheet named as referenced in this line? Change the sheet name or change the macro to use Sheet1
Set xlSheet = xlWB.Sheets("Test1")
(I'll update the macro to use Sheet1)
Clare says
Hi Diane,
Thanks for sharing this macro. This is my first time to use this as I am trying to finish a project. I have difficulty in successfully running the file. The error message I get is Compile error; User-defined type not defined.
Thanks in advance for your help.
Diane Poremsky says
this indicates you didn't set a reference to the Excel object library.
Mark Beiner says
Thank you very much for the macro. It's exactly what I need. However, I am getting a Run-time error '9': Subscript out of range.
How can I fix this?
Diane Poremsky says
What line does it quit on?
Does the workbook have a sheet named as referenced in this line:
Set xlSheet = xlWB.Sheets("Test1")
(I'll update the macro to use Sheet1)
Florjancic Michael says
Hey saw this code and it does almost what i need or more i'm trying to
How would i have to modify your code so that i can get the outlook calendar (with some info) out of it into a preformatet excel-sheet?
trying to get stuff together so i can make an weekly report out of my outlook calendar. at best that it does it every week at Sunday 24:00 automaticly and i get an excel-sheet out of it.
or where could i get the code-lines (to now how i get at least the informations out of outlook)? if you could help, that would be great cause i have no clue about VBA. just some knowledge of java-coding
thx
Diane Poremsky says
Change Dim olItem As Outlook.MailItem to either Dim olItem As Object or Dim olItem As Outlook.AppointmentItem.
Then change the fields you capture - field names are here - https://msdn.microsoft.com/en-us/library/office/dn320241.aspx
strColB = olItem.SenderName
strColC = olItem.SenderEmailAddress
strColD = olItem.Body
strColE = olItem.To
strColF = olItem.ReceivedTime
Fernando says
Hi Diane.
I would like to have your knowledge...
Thank you for sharing your knowledge
Ritesh says
HI Mam,
i have to fatch details from particular mail only (not all mails) which are automatically(by using rules) saved in my Outlook folder "ABC".
please help how it is possible.
thanks in advance
Ritesh
Diane Poremsky says
As long as there is a pattern you can search for, you can use regex to get the details - https://www.slipstick.com/developer/regex-parse-message-text/ - that specific sample works on the selected message, which sounds like it's what you need.
Rajarshi Paul Choudhury says
Hi Diane..
Thank you for the code.
However, I am new to VBA and I am facing some issue.
Whenever I Run the Code I get a compile error: User-defined type not found and highlights "currentExplorer As Explorer" .
Diane Poremsky says
You are running this code in Outlook's VBA editor and copied it exactly as on the page?
Matthew Giroux says
Hi Diane, Thanks for this great macro!
I too am running into this error. Checked my object Library, and i am not seeing Explorer in there. Are you using any specific references for this line? I am running 32bit, 2016 excel.
Thanks again for your support!
Diane Poremsky says
The only reference is to Excel object model. does it work at all? If you have NDRs or meeting invites, it could error as it only works with a plain email item.
Does it error on any specific line?
Diane Poremsky says
Also, are you running it in Excel or Outlook? This is an Outlook macro. Explorer references Outlook's main window also known as "Explorer".
Jeff Landers says
Hi Diane, this works great. I modified it slightly for only a couple of the pieces of data from outlook, but I can't get it to start on the second row because I have headers on the first row. It overwrites them. Also, will this continue on down as more email data continues to be extracted over time. the goal is to use this macro to extract weekly emails that are selected by the user and fill in a spreadsheet of raw data, which I will then use with a master spreadsheet for pivot tables.
Diane Poremsky says
This line finds the next available line, based on a value in column B - it shouldn't be overwriting row 1 if there is a value in column B. If b is empty, you can use a different column, change the B to a column that will always have data in it. It will continue down the sheet, starting with the last empty row - as long as the column in this line always has data in it.
'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
Mihir says
This Macro works great!! I can't seem to export the body of message after first 250 messages. Subject and other field works.
Diane Poremsky says
So just the body fails after 250 messages are processed? Try this to clear the variable each time (not sure it will fix the problem tho.)
strColD = ""
strColD = olItem.Body
Erjon says
Hello Diane, this is a graet makro and thank you for it, the only problem for me is that when i pres it to list all the mail in excel it cant find the next blank cell in excel, so the line:
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
is not working in my case i think, every time i try to export a mail it returns it in the first row
Diane Poremsky says
Does Column B contain a value? If not, change it to a column that will never be empty.
Michelle R says
Only had to finagle a couple things to get the code to work like a charm - including finding a unique search term to hunt only the emails that I *really* wanted data copied from.
Thank you!
Bryan says
Hello Diane. I've been using this macro - almost verbatim - for several years. The only changes are the name/location of the file and the OL variables used (I pull 4: ReceivedTime, SenderName, To, and Subject).
I am having a problem with the macro I never before experienced. The only change I can think of is that I moved from MS Office 2013 to 2016 - maybe just coincidence? possibly a setting I forgot to transfer from 2013 to 2016? The problem is as follows:
beginning with the 247th record, the second and third fields (SenderName and To) simply repeat the data from the 246th record. The first and fourth records are correct (ReceivedTime and Subject) - the repeat is only of the second & third. This continues no matter how many records (above 246) are found. I copied and pasted your code from above and my code and compared to confirm there are no differences other than as described.
Can you help me find / fix the problem?
Thank you,
Bryan
Diane Poremsky says
well, i don't think its because you changed versions - not code wise anyway. Have you tried stepping through the macro and seeing if you can see where its going wrong? You can hover over variables and see if they match what you expect at each point.
Yogesh says
Hi can you please tell how can i export outlook data with date range,bcoz i want to export data for specific date only.
and i am using the below code , please add the date range macro code if possible.
ub sample_macro()
'reference -> microsoft outlook
Dim oitem As Outlook.MailItem
Dim ol As Outlook.Application
Dim olns As Outlook.Namespace
Dim oinbox As Outlook.Folder
Dim j As Long
ThisWorkbook.Sheets(1).Range("a2:d" & ThisWorkbook.Sheets(1).Range("a1048576").End(xlUp).Row + 1).Clear 'clear existing data if any
Set ol = New Outlook.Application
Set olns = ol.GetNamespace("MAPI")
Set oinbox = olns.GetDefaultFolder(olFolderInbox) 'select the inbox
Set oinbox = oinbox.Folders("Ap") ' select if you want to choose any specific folder
oinbox.Items.Sort "[ReceivedTime]", True
j = 2
For Each oitem In oinbox.Items ' loop outlook emails
ThisWorkbook.Sheets(1).Range("a" & j).Value = oitem.SenderName
ThisWorkbook.Sheets(1).Range("b" & j).Value = oitem.Subject
ThisWorkbook.Sheets(1).Range("c" & j).Value = oitem.ReceivedTime
ThisWorkbook.Sheets(1).Range("d" & j).Value = oitem.LastModificationTime
ThisWorkbook.Sheets(1).Range("e" & j).Value = oitem.UnRead
ThisWorkbook.Sheets(1).Range("f" & j).Value = oitem.Categories
ThisWorkbook.Sheets(1).Range("g" & j).Value = oitem.CreationTime
j = j + 1
Next
Set oinbox = Nothing
Set olns = Nothing
Set ol = Nothing
End Sub
Diane Poremsky says
one method is in the macro here - https://www.slipstick.com/developer/macro-move-aged-mail/ - it checks the received date and processes older messages.
To limit it to a specific date, use
If Format(oitem.SentOn, "m/d/yyyy") = "4/8/2016" Then
' copy to excel
End If
Yogesh says
Hi Diane,
thank you very much for your quick reply. date range issue got fixed now however i am getting the error type mismatch and cursure stop
at here
> Next
Set oinbox = Nothing
Set olns = Nothing
Set ol = Nothing
End Sub
bcose i removed the subfolder line "Set oinbox = oinbox.Folders("Ap") ' select if you want to choose any specific folder"
bcoz i wanted to export only inbox items
Please help !
Thanks,
Yogesh
Diane Poremsky says
Are there items other than email that your are trying export to excel? NDRs, Receipts, Reports and meeting requests are not a a mailitem object and will trigger the mismatch error.
Set oinbox = olns.GetDefaultFolder(olFolderInbox) should be getting the inbox. What error does that trigger?
Yogesh says
Hi Diane,
Thank you for your quick response.
the error is "Run Time Error '13' "
"Type Mismatch"
and cusure stopped at here
>|Next
Diane Poremsky says
Does it export any messages? What is the next message after the last one exported? That would be triggering the error.
Francisco says
Great macro. Thanks!
Quick question. How can you add headers of the information you are exporting to the Excel file?
Diane Poremsky says
The full internet headers? use the function at https://www.slipstick.com/developer/code-samples/outlooks-internet-headers/ and use this to put it in the cell- xlSheet.Range("g" & rCount) = GetInetHeaders(olItem)
Ralph Moran says
Hello Diane, I'm trying to to create a XLSX file with data but this code sends me a 1004 error, it says "Sorry, we couldn't find the file. Is it possible it was deleted, moved or...?" First, this code doesn't create the file. It works if I manually create the file and the sheet name.
Could you tell me what is wrong?
By the way, I create a recursive procedure to check all email items into subfolders, this is the code:
Private Sub processFolder(ByVal oParent As Outlook.MAPIFolder)
Dim oFolder As Outlook.MAPIFolder
Dim oMail As Object
' Run all email inside this folder
For Each oMail In oParent.Items
' Emails are typename MailItem
If TypeOf oMail Is MailItem Then
If oMail.SenderEmailType "EX" Then
rCount = rCount + 1
Debug.Print oMail.SenderName & " - " & oMail.ReceivedTime & " >" & rCount
End If
End If
Next
' Recursive function: Run subfolders inside this folder
If (oParent.Folders.Count > 0) Then
For Each oFolder In oParent.Folders
processFolder oFolder
Next
End If
End Sub
Diane Poremsky says
The macro doesn't create the file - the excel file needs to exist.
Pushan says
Thanks Diane for this beauty. Saved my department a ton of time in extracting the fields from Outlook for over 2000 emails. Heartfelt thanks from my side
Brandon B says
Is there a way to change the visual display; such as the Callback field? Having it read as Client Requirements. I import and export fields to a marketing email data base and with the CSV (Import / export map) there are only certain fields that can be mapped. Callback, and Radio are examples of Phone Fields that I would rather use for more relevant data. It would be handy and NICE to simply chance the visual screen field name, so I don't have to use a side "cheat-sheet" for my users to remember and understand how we are "really" using the field.
Changing it from a Phone format to text would be great too.
We also use the Business PO Box field as a field holder for a start or Move-in date (as we track office leases), and we use the anniversary field for the date a lease ends, and the Office Location field for the Tenant RSF or size (Number).
The only reason we use these standard fields is to be able to import and exprt with the wizard.
Any thoughts as to what we could do or change.
Thank you
Diane Poremsky says
No, you can't change the display name in the flyout menu in the default form. If you create a custom form, you could display each field on the form and change the label. Same goes for the other fields - you can change labels if you add those fields to a custom form.
Grace C says
Hi Diane, this is really great! I'm just wondering is there an easy way to make the selection of emails being exported a folder from a shared inbox, or a sub-folder? I'm having trouble working out how to do it.
Diane Poremsky says
As long as you select the folder and messages first, it should work as is - the first line uses the current folder and the second one uses the selected messages:
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
Grace C says
Thanks, but is there anyway for the macro to automatically export all emails from a specific folder without manually selecting them?
Diane Poremsky says
Yes, with a little tweaking. The macro at https://www.slipstick.com/developer/code-samples/working-items-folder-selected-items/ shows how. Basically, replace this part:
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection
with this:
Set objFolder = objOL.ActiveExplorer.CurrentFolder
Set objItems = objFolder.Items
For Each obj In objItems
you'll need to dim objFolder and ojbitems - the link above shows how.
Joost Duivenvoorden says
Dear Diane, this looks like a very useful macro. I'm looking into using it for a department mailbox. How can I point to specific a mailbox and a specific folder?
I also don't want to apply the macro to a selection of e-mails, but rather to all e-mails, in that specific folder, older than 1 day. How would that look like? I'm no expert :-)
Diane Poremsky says
You need to tweak these lines -
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection
assuming its in your profile as a shared mailbox, you'd replace them with something like this - you'll need the full code for a shared mailbox at the end of the article here - https://www.slipstick.com/developer/working-vba-nondefault-outlook-folders/
Set objFolder = objOL.GetSharedDefaultFolder(objOwner, olFolderCalendar)
Set objItems = objFolder.Items
For Each obj In objItems
This shows how to work with all messages in a folder -
https://www.slipstick.com/developer/code-samples/working-items-folder-selected-items/
The macro at https://www.slipstick.com/developer/macro-move-aged-mail/ shows how to use an if statement to check the date
A.D. says
Hi Diane,
I have several hundred PSTs in different folders on a drive. I need to extract the header info from every email into either Access or Excel. I found your code which I'm hoping to be able to modify so it can open every single folder and subfolder starting from the root...
In the comments below, you mention a way to export all items in a folder. But for some reason objItems contains nothing, and therefore the strCol* variables are empty: e.x.
strColC = objItems.SenderEmailAddress
and as a result my table is empty.
One thing I did notice is that when declaring
Dim objFolder As Outlook.MAPIFolder, the VB editor did not list MAPIFolder in the drop-down menu so I typed in and got no errors when debugging.
I wonder if this has anything to do with my empty table issue?
Your help is greatly appreciated!
Here are the part of the code I'm concerned with:
'Dim currentExplorer As Explorer
'Dim Selection As Selection
'Working with the entire folder:
Dim objOL As Outlook.Application
Dim objFolder As Outlook.MAPIFolder
Dim objItems As Outlook.Items
Dim olItem As Outlook.MailItem
Dim obj As Object
Dim strColB, strColC, strColD, strColE, strColF As String
' get the values from outlook
'Set currentExplorer = Application.ActiveExplorer
'Set Selection = currentExplorer.Selection
'For Each obj In Selection
'Set olItem = obj
'Do this instead of above:
Set objOL = Outlook.Application
Set objFolder = objOL.ActiveExplorer.CurrentFolder
Set objItems = objFolder.Items
For Each obj In objItems
With obj
'collect the fields
strColB = objItems.SenderName
strColC = objItems.SenderEmailAddress
strColD = objItems.Body
strColE = objItems.To
strColF = objItems.ReceivedTime
'write them in the excel sheet
xlSheet.Range("B" & rCount) = strColB
xlSheet.Range("c" & rCount) = strColC
xlSheet.Range("d" & rCount) = strColD
xlSheet.Range("e" & rCount) = strColE
xlSheet.Range("f" & rCount) = strColF
'Next row
rCount = rCount + 1
End With
Diane Poremsky says
>>> VB editor did not list MAPIFolder in the drop-down menu so I typed in and got no errors when debugging.
I wonder if this has anything to do with my empty table issue?
It shouldn't, but try Folder instead. Either should give the same results (or at least they do for me).
The problem is here - it should be obj.SenderName
For Each obj In objItems
With obj
'collect the fields
strColB = objItems.SenderName
I think i some code here that walks folders - I'll see if i can find it.
Alex Gonzalez says
hi Diane thanks for the code i'm triying to adapt it to a entire conversation not to just one email do you have any suggestion to make this possible?
Diane Poremsky says
Are you copying just one conversation to Excel or did you want to keep the conversations together? conversations use the conversation id - you'd need to get that using propertyAccessor - https://www.slipstick.com/developer/read-mapi-properties-exposed-outlooks-object-model/
"PR_CONVERSATION_INDEX", propertyAccessor.BinaryToString(propertyAccessor.GetProperty("https://schemas.microsoft.com/mapi/proptag/0x00710102"))
Kenneth Chiu says
How can I just get today's mail information?
Diane Poremsky says
It works on the selection - so you could select just todays messages. Or you can add an if statement that checks the received date field.
Set olItem = obj
If Format(olItem.ReceivedTime, "m/d/yyyy") = Format(Date, "m/d/yyyy") Then
'snip
end if
Next
Deanna Kemler says
Will this code pick up multiple paragraphs of an Outlook email and transfer them to a single cell in Excel? The emails I'm working with have these lines:
First Name:
Last Name:
Email:
Phone:
Location:
Zip Code:
Details:
Classification:
I've got code working that does an excellent job of transferring these to corresponding fields in an Excel spreadsheet. HOWEVER, the "Details" section often consists of several paragraphs and my current code transfers only the first paragraph.
I've been trying to adapt the fix suggested on this page (https://www.visualbasicscript.com/m104298-print.aspx), but am getting error messages. So I'm looking for other options.
Do you have any suggestions? Thank you!
Diane Poremsky says
I use regex to grab the fields - should be able to use something like this:
.Pattern =”(Details:[:]+\s*(.*)\s*Classification:)”
https://www.slipstick.com/developer/regex-parse-message-text/
Chris says
Hi - thank you for the code and helpful replies. I was wondering if you would be able to help me with something. Is it possible to tweak the code so that it exports for the current folder and all subfolders? I would also like to add a date filter so that only emails with the specified date range are exported. Thanks!
Chris Taaffe says
Hi, hopefully you can help me. I have various copies of code to export email details using VB in both Outlook and Excel however, what I need is to obtain the details from Read Receipts which I understand are classed as ReportItems and not MailItems.
Is there a way of doing this as I really don't want to log each one manually?
Sarvesh prasad says
Hii..
Thankyou for the code.
But sorry I am a Newbie to VBA and I am facing some issue.
Whenever I am Run the Code I get a compile error: User-defined type not found and highlights "currentExplorer As Explorer" .
I am using office 2010.
Nicholas Markwalter says
Thank You for the great macro, it works great!
Outlook 2010 (Windows 7).
How can i get file attachment file "names" to appear into a new column?
Thank You!
Diane Poremsky says
Get the attachment names from the attachment collection:
Set objAttachments = olitem.Attachments
lngCount = objAttachments.Count
If lngCount > 0 Then
For i = lngCount To 1 Step -1
' Get the file name.
strFile = objAttachments.Item(i).FileName
strFile = strFile & ";" & strFile
Next i
End If
then write to the column:
xlSheet.Range("g" & rCount) = strfile
Suzanna says
Hi Diane,
I've been having an issue getting the names for more than one attachment - the "strfile & ";" & strfile" just repeats the file name of the first attachment
Diane Poremsky says
add debug.print i, objAttachments.Item(i).FileName right after the For i line then run it and watch the results in the Immediate window (or replace debug.print with msgbox) - does i increment down and the filename change?
Eb G says
Where can I fit this into the main code above? Sorry - am a VBA newbie! Many thanks.
Diane Poremsky says
The first macro on the page goes into a module in the Outlook VBA Editor. In Outlook, press Alt+F11 to open the editor then right click and add a new Module. paste it into that. More info on using the editor is at https://www.slipstick.com/developer/how-to-use-outlooks-vba-editor/
if you want to run it from excel, there is a link to a workbook template with the Excel version of this macro.
Raymond says
How can I add
1. If e-mail has been replied to or not.
2. What time it was replied to.
As olItems(x). ?
Diane Poremsky says
I thought I had a code sample in the comments that did that, it must be on another article. Basically, you need to get the extended mapi fields for those values. The sample code at https://www.slipstick.com/developer/read-mapi-properties-exposed-outlooks-object-model/ shows how to get those values - you just need to work them into the macro to write to Excel. You'll probably want to use an if statement to get a more usable value
strColF = olItem.ReceivedTime
strColG = propertyAccessor.GetProperty("https://schemas.microsoft.com/mapi/proptag/0x10810003") 'last action
strColH = propertyAccessor.GetProperty("https://schemas.microsoft.com/mapi/proptag/0x10820040") ' time of last action
'write them in the excel sheet
xlSheet.Range("f" & rCount) = strColF
xlSheet.Range("g" & rCount) = strColG
xlSheet.Range("h" & rCount) = strColH
You'll probably want to use an if statement to get a more usable value and assign it to a string variable:
strAction = propertyAccessor.GetProperty("https://schemas.microsoft.com/mapi/proptag/0x10810003")
if strAction = "102" or strAction = "103" then strAction = "Replied"
elseif strAction = "104" then strAction = "Forwarded"
End if
xlSheet.Range("g" & rCount) = strAction
Simon says
Hi Diane,
Thank you for this wonderful macro.
I am wondering whether it will be possible to have an additional column that tells us which folder the emails items are extracted from. I will be really grateful if you could help me on this!
Many thanks
Diane Poremsky says
You can.
Try using
xlSheet.Range("g" & rCount) = currentExplorer.name
Sergei says
Exactly what my boss wanted me to find, how to export sender, subject and received date to excel from Outlook :)
CoolAuto says
I am thinking not... Sending same excel file to multiple recipients. But I want for a filter in a file to filter out recipients other than one who is opening it. I think this can NOT be accomplished through TO field in Outlook, but rather Excel VBA needs a way to first determine on who's computer, or better yet who's outlook e-mail box that Excel file is being opened from, then use that to apply a filter in that excel file. Do you see what I mean?
CoolAuto says
Hi Diane,
Excel 2010 file. Upon open, from Outlook e-mail, I want a macro to grab e-mail address of a recipient to then filter to only his/her items on the spreadsheet of that Excel file. But that CAN'T be just from "To" field of that e-mail, as there are multiple recipients. I can figure out other portions, but need that e-mail address grab portion AND how to have that address be then integrated into the filtering criteria.
Can you help?
Diane Poremsky says
are you checking every address in the to field against the spreadsheet? You'll use the recipient collection -
This will grab each address, one at a time
Dim Recipients As Outlook.Recipients
Dim recip As Outlook.Recipient
Dim i
Set Recipients = Item.Recipients
For i = Recipients.Count To 1 Step -1
Set recip = Recipients.Item(i)
Claudia Fernandes says
Hello Diane,
Thank you very much for your fast reply.
Then can you please let me know how can I compare emails received with the first reply to each on sent? Basically I need to check if me and my colleagues are compliantly replying to all emails within X minutes; do you have any idea how I could do this? I'm running out of ... Because we do user the same inital email to reply orforward several times.
Once again thank you very much for your help on this.
Diane Poremsky says
Are the sent items stored in the same mailbox? If so (and assuming the subject varies) you'll need to export both the inbox and sent folder to Excel then sort by subject (you'll need a way to sort correctly since replies will have RE in the subject.)
Claudia Fernandes says
Hello,
Do you know how I can extract the first email reply time from Outlook?
What I would want to know is how much time it took me to answer each email; Ive found out the last reply time, not the first unfortunately.
Thank you very much!
Diane Poremsky says
Outlook doesn't track the each time your reply, only the last one. If you reply to the same message more than once, you'd need to look at the sent items to get each reply time.
James says
Hi Im just wondering if there is a way using this code to extract the emails from exchange accounts? Im not getting the actual email address for any internal employees.
Cheers
james says
Or is there a way to extract all the info from the contact cards i have in my outlook?
Cheers
Diane Poremsky says
You can use Outlook Import/Export function to export the contacts. This would be easier than macros because you don't need to add every field to the code.
Diane Poremsky says
Yeah, you can get their smtp addresses or aliases, although aliases are messy if using Office 365 Exchange.
Const PR_SMTP_ADDRESS As String = "https://schemas.microsoft.com/mapi/proptag/0x39FE001E
Set recips = Item.Recipients
For Each recip In recips
Set pa = recip.propertyAccessor
Address = LCase(pa.GetProperty(PR_SMTP_ADDRESS))
That sample code is from https://www.slipstick.com/how-to-outlook/prevent-sending-messages-to-wrong-email-address/ - it has all of the dim's you'll need.
Travis says
I'm trying to use the code you provided to get the email addresses of internal employees, but am not sure where the code should be added in the macro.
Also, the first line of exported data is always being overwritten. I've added a header in my excel and changed the order so that it is date, sender name, sender email and subject and it works great except for these two issues. Any help would be greatly appreciated!
Thanks
Diane Poremsky says
You'd put it right after this line:
Set olItem = obj
use a variable for the address them write the address to the cells:
strColC = strAddress
This line: rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row finds the next empty line in the worksheet. Change the B to any column that will always contain data.
Travis says
Thanks so much for your fast response and apologies for my newbie questions, but I'm still having difficulty with these two things.
1) I've attempted to create a variable for the address but was apparently unable to understand how to do so. Also, I get a Compile error: Variable not defined for "Item" in the string Set recips = Item.Recipients. Can you help point me in the right direction for creating the necessary variable(s)?
2) rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row is present in my code, but it is still regularly overwriting the last row even though there is data there. I've also tried with C, D and E - same story.
I tried to paste my code in my reply, but it is too long.
Diane Poremsky says
Counting: for whatever reason, Excel 2016 (or at least the current builds) don't count right - it finds the last line. :( I added
rCount = rCount + 1
after the rcount to move it ahead.
You need the DIM statements for those variables. But... that code won't work for the sender address. If you need the sender's SMTP, try this:
Set olItem = obj
'dim's should be together at the top of the page
Dim Address As String
If olItem.SenderEmailType = "EX" Then
Dim recipient As recipient
Set recipient = Application.Session.CreateRecipient(olItem.SenderEmailAddress)
Address = recipient.AddressEntry.GetExchangeUser.PrimarySmtpAddress
Else
Address = olItem.SenderEmailAddress
End If
strColB = olItem.SenderName
strColC = Address
Travis says
Thanks Diane! You're a star!
himanshu says
Requirement: I need your help as i am asked to analyse the data of my current mailbox.
Further more i need to keep the track of mails being replied and missed .Can you tell me the ways to do this ..in excel or in outlook .I have outlook 2010 and 2003 mailbox configured .
Diane Poremsky says
Analyzing the mailbox would probably be best in Excel, since you can use Excel functions. Unless it's just simple stuff like message count, replies and dates.
If you have all of the fields you need in the view, see https://www.slipstick.com/tutorial/no-export-way-to-use-outlook-data/ for an easy way to get data into Excel.
How to get the replied to time: https://www.slipstick.com/exchange/adding-extended-mapi-fields-to-outlook/
There two pages show how to get some information within outlook:
https://www.slipstick.com/outlook/rules/count-received-emails/
https://www.slipstick.com/outlook/creating-reports-count-conversations-outlook/
3rd party utilities are here:
https://www.slipstick.com/addins/productivity-addins/outlook-reporting-tools-utilities/
Satej says
Also, the macro runs only for the emails which are selected. How can I modify it to run on all the emails in the selected Outlook folder?
Thanks again..
Diane Poremsky says
This tells it to run on the selected items:
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection
Set olItem = obj
To work with all in the current folder:
Set objOL = Outlook.Application
Set objFolder = objOL.ActiveExplorer.CurrentFolder
Set objItems = objFolder.Items
For Each obj In objItems
With obj
(You'll need to make sure the references are correct. That snippet was copied from https://www.slipstick.com/developer/code-samples/working-items-folder-selected-items/)
Satej says
Hello Diane..
Thanks for the code...It's really useful. However, the formatting of the excel sheet is not good enough. Kindly suggest what could be done to achieve a good formatting.
Thanks in advance..
Diane Poremsky says
You can apply formatting using VBA or, if adding to an existing workbook, the formatting should carry over to each row.
Johan Brits says
hi, how do i add the subject line...?
Diane Poremsky says
Get the subject:
strColG = olItem.Subject
'write them in the excel sheet
xlSheet.Range("G" & rCount) = strColG
Søren says
Hi Diane
A
How to change the Outlook macro - [ Sub CopyToExcel() ] at the very top - to copy to Excel all and any email in the entire emailbox (root folder?) (any email in any folder or subfolder(s) - all together? With an extra Column in Excel indicating the folder / subfolder (s) where the email is stored in Outlook?
B
How to change the Sub CopyToExel with use of the PickFolder method (as an extra alternativ to A) to export all and any email in the folder picked with PickFolder - including subfolder(s) if any. With the option to choose a subfolder instead of a parent folder.
Cannot find such example at Slipstick.com
Hope you will guide. Thank you in advance.
Diane Poremsky says
Adding the folder name is as simple as adding another cell and adding the folderobject.name to it.
The sample macro in the article - https://www.slipstick.com/developer/saving-messages-to-the-hard-drive-using-vba/ - shows how to walk the folders and do something. In this case, it's saving each message. You'd replace that part of the code with the code on this page.
Søren says
Hi Diane
Thank you for the above.
I have downloaded the Excel file you mention at the top (An Excel version of this macro is available in a workbook template here).
I have put in Option Explicit at the very top of the macro (and before the Sub CopyMailtoExcel () I) . Then I have inserted Dim strSender As String which was missing.
Right after Public Sub CopyMailtoExcel () I have inserted On Error GoTo ErrorHandler and at the very bottom just before End Sub I have inserted Exit Sub ErrorHandler: and in next line Resume Next . This is needed otherwise the macro will not run.
I have comment (out) Range("C" & rCount) = strAttCount to Range("I" & rCount) = olItem.BCC both incl.
I have created an Searchfolder called All Mail (http://email.about.com/od/outlooktips/qt/et_all_mail.htm ).
In outlook I click at the All Mail Searchfolder so it is active before I run the Excel macro . Then I run the macro and everything in Data and Time come out in the Excel Sheet1 (Everything is here; any and all mail in the pst-file/Outlook). SUPER.
I now uncomment Range("C" & rCount) = strAttCount to Range("I" & rCount) = olItem.BCC (except ''' Range("D" & rCount) = olItem.Subject and ''' Range("E" & rCount) = strBody to save time) so they are active. I run the macro again and everything come out in the Excel Sheet1 – SUPER. I want the email address for Sender name AND the e-mail address for From (display name) AND To (display name) AND CC (display name) AND BCC (sent items only). I then add Range("J" & rCount) = olItem.SenderEmailAddress and run the macro. The from emailaddress come out in the Excel Sheet1 column J. (Everything is here; any and all mail in the pst-file/Outlook). SUPER . Now I want the email address for To and CC and BCC just like Range("J" & rCount) = olItem.SenderEmailAddress – therefore I have tried:
Range("K" & rCount) = olOriginator (I think this is the SenderEmailAddress so not needed)
Range("L" & rCount) = olTo 'Does not work
Range("M" & rCount) = olCC 'Does not work
Range("N" & rCount) = olBCC 'Does not work
But this does not work.
Please advice.
Sorry for the long text.
BR
Diane Poremsky says
you need to get the recipients collection and parse the names. This shows how to do it - https://www.slipstick.com/developer/recipient-email-address-sent-items/
Ashwin Dudhe says
Hi Diane, This as one of the most impressive and made easy code.
The current code exports the data from selected folder from he out look in box. I wonder if you could suggest the modification to fetch the data of not only inbox folders but also all the sub folder created inside it.
Thanks in advance
Diane Poremsky says
You can do that - i don't have a sample that does it, but this macro shows how to walk the folders: http://vboffice.net/en/developers/looping-recursively-through-folders-and-subfolders
Ashwin Dudhe says
Thanks for the reply Diane. I will try it and will get back you :)
And thank you for all the knowldege sharing :)
Ashwin Dudhe says
Hello Diane,
Thanks for guiding me to recursive loop method.
I really new to VBA could you please help me with making this recursive codes work with the sheet you have posted to Macro to Export Outlook Fields to Excel..
I am trying to get the macro which can pull data form the entire inbox (including subfolder).
I really appreciate your help and guidance..a big thanks from India
Diane Poremsky says
I will take a look at it - give me a few days and if i don't post back, remind me. :)
Ashwin Dudhe says
Hello Diane,
Sorry to bug you. This is a friendly reminder for the above request.
Once again thank you for your time and guidance. :)
Diane Poremsky says
try the attached text file.
i used the recursive macro at http://www.vboffice.net/en/developers/looping-recursively-through-folders-and-subfolders