Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default VBA to read save messages on an Excel sheet

Hi, I've been trying to write a scrip on VBA for outlook, I need that for each new email that contains a spceficid prhase in the subject, the email should be copied in Excel, the flieds that I want to copy are FROM, DATE, SUBJECT, BODY.
I tried to do this on Outlook but I couldn't. I don't know if you now how can I do this in excel. Thank you.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default VBA to read save messages on an Excel sheet

Hi,

Am Tue, 21 Jun 2016 06:00:35 -0700 (PDT) schrieb Juan López:

Hi, I've been trying to write a scrip on VBA for outlook, I need that for each new email that contains a spceficid prhase in the subject, the email should be copied in Excel, the flieds that I want to copy are FROM, DATE, SUBJECT, BODY.
I tried to do this on Outlook but I couldn't. I don't know if you now how can I do this in excel. Thank you.


try:

Sub OutlookMail()
Dim appOL As Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder
Dim objItems As Outlook.Items
Dim objItem As Object
Dim n As Long
Dim varOut() As Variant

Set appOL = CreateObject("outlook.Application")
Set objNameSpace = appOL.GetNamespace("MAPI")
Set objFolder = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objItems = objFolder.Items

For Each objItem In objItems
With objItem
If .Class = olMail Then
If InStr(.Subject, "Excel") 0 Then
ReDim Preserve varOut(2, n)
varOut(0, n) = .SenderName
varOut(1, n) = .ReceivedTime
varOut(2, n) = .Subject
n = n + 1
End If
End If
End With
Next
If n 0 Then
Range("A1").Resize(n, 3) = Application.Transpose(varOut)
Columns("A:C").AutoFit
End If
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default VBA to read save messages on an Excel sheet

Hi again,

Am Tue, 21 Jun 2016 16:39:46 +0200 schrieb Claus Busch:

Sub OutlookMail()


better try:

Sub OutlookMail()
Dim appOL As Outlook.Application
Dim objNameSpace As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder
Dim objItems As Outlook.Items
Dim objItem As Object
Dim n As Long

Set appOL = CreateObject("outlook.Application")
Set objNameSpace = appOL.GetNamespace("MAPI")
Set objFolder = objNameSpace.GetDefaultFolder(olFolderInbox)
Set objItems = objFolder.Items

For Each objItem In objItems
With objItem
If .Class = olMail Then
If InStr(.Subject, "Hallo") 0 Then
n = n + 1
Cells(n, 1) = .SenderName
Cells(n, 2) = .ReceivedTime
Cells(n, 3) = .Subject
Cells(n, 4) = .Body
End If
End If
End With
Next
Columns("A:D").AutoFit
Rows("1:" & n).AutoFit
End Sub


Regards
Claus B.
--
Windows10
Office 2016
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Save messages ledzepe Excel Discussion (Misc queries) 1 August 3rd 06 09:14 PM
excel says folder is read only and cannot save kylle200 Setting up and Configuration of Excel 0 May 17th 06 07:29 AM
How can I save an Excel file which says it's a Read only to a CD? Cindy Who Excel Discussion (Misc queries) 2 March 25th 06 12:19 AM
I get error messages in Excel cells once I save a file JeffJake Excel Worksheet Functions 1 November 29th 05 12:06 AM
Rules that act on messages after they are opened & read? Perotin Excel Discussion (Misc queries) 1 December 5th 04 01:31 AM


All times are GMT +1. The time now is 06:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"