Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-26-2014, 10:45 PM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows XP copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default copy content of a table inside email to excel sheet

Hi all,

I'm looking for a solution to copy the content of a table inside an email to excel sheet. The things I need to get there are per row.
The subject of the email is always the same, so a rule could fire a script when an email with that subject is received.
As the table has named fields, it should be easy to get them to the excel file.

How would I get let's say the following text into excel:

Name: Peter Postman


Department: finance
Reason: sick leave

All help is really appreciated.

Greetings.
Reply With Quote
  #2  
Old 09-27-2014, 04:04 AM
gmayor's Avatar
gmayor gmayor is offline copy content of a table inside email to excel sheet Windows 7 64bit copy content of a table inside email to excel sheet Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

This is covered in some depth on my web site at http://www.gmayor.com/extract_data_from_email.htm
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 09-27-2014, 09:05 AM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows XP copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi Gmayor,

BIG THANKS. It works really nice :-)
Any ideas how to write to an access database instead of the excel file?


Greetings.
Reply With Quote
  #4  
Old 09-27-2014, 12:58 PM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows XP copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi Gmayor,

I have some problems when I try it out with a table copied from word to the email for testing. I can't find what to change to get the get the customer name from the table.
Where in the code the change is needed?

Code:
'Check each line of text in the message body
For i = UBound(vText) To 0 Step -1
If InStr(1, vText(i), "Source:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("A" & rCount) = Trim(vItem(1))
End If

If InStr(1, vText(i), "Customer Name:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("B" & rCount) = Trim(vItem(1))
End If
After running the macro to test the lines, field name for customer name comes on line 0 and the customer name itself comes on line 2. I hope this helps.

Greetings.
Reply With Quote
  #5  
Old 09-27-2014, 09:40 PM
gmayor's Avatar
gmayor gmayor is offline copy content of a table inside email to excel sheet Windows 7 64bit copy content of a table inside email to excel sheet Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

If the fixed text is on line 0 and the required text is on line 2 then rather than split the line add the difference to the required line and use the whole line e.g.

If InStr(1, vText(i), "Customer Name:") > 0 Then
xlSheet.Range("B" & rCount) = Trim(vText(i + 2))
End If

For Access you will have to wait until I release an add-in that I have developed with my friend Greg Maxey that takes all the thinking out of the task for extracting the data from messages to Access, Excel or Word. The add-in is complete but we are still discussing how to present it to get some return from it, and thanks to on-going medical treatment I have not had much time to work with him recently.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #6  
Old 09-28-2014, 02:07 AM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows 7 32bit copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi Gmayor,

Again BIG thanks :-)
This was the solution I was looking for. Tomorrow at work I will check if I can use one of the two solutions depending how the email body is.

I will see if I can use ADO connection as I did from excel to excel.

I hope some one will be able to learn from this too and use it.

Greetings.
Reply With Quote
  #7  
Old 09-28-2014, 02:22 AM
gmayor's Avatar
gmayor gmayor is offline copy content of a table inside email to excel sheet Windows 7 64bit copy content of a table inside email to excel sheet Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

Yes, you will be able to do that.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #8  
Old 09-29-2014, 08:49 AM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows 7 32bit copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi Gmayor,

I tried today at work, but tiny thing happens. As I have 2 versions of excel installed (2003 and 2007) on my pc, it will always start excel 2003 and this one will give me errors as the workbook is a 2007 version.
How can I go around this?

greetings.

(at home I have office 2013, but at work I have office 2003 and excel 2007)
Reply With Quote
  #9  
Old 09-29-2014, 08:33 PM
gmayor's Avatar
gmayor gmayor is offline copy content of a table inside email to excel sheet Windows 7 64bit copy content of a table inside email to excel sheet Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

Two versions of Excel will conflict because of shared resources. See http://www.gmayor.com/Toolbars_in_word_2007.htm Although the page refers to Word, Excel has similar issues and the registry changes apply to Excel also. You then need to set 2007 as the default for xml data types.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 10-02-2014, 09:40 PM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows 7 32bit copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi Gmayor,

It seems that I have problems with outlook 2003. At home with 2013 works fine. I'm getting the run-time error 424 and I'm not able to get rid of the excel file opening in 2003. I need to ask the IT people to get it of the PC.

Greetings.
Reply With Quote
  #11  
Old 10-02-2014, 10:39 PM
gmayor's Avatar
gmayor gmayor is offline copy content of a table inside email to excel sheet Windows 7 64bit copy content of a table inside email to excel sheet Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

As I said, two versions of Excel will share resources and only one of them can have control of the operating system at a time. You need to make the registry changes to Excel to stop the two versions from reconfiguring and you need to associate the Excel files with the 2007 version, if you want that version to open them by default.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #12  
Old 10-02-2014, 10:49 PM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows 7 32bit copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi,

I have requested that they remove excel 2003 to avoid problems in future.
But the error 424 is that connected with excel 2007 and outlook 2003?

Greetings.
Reply With Quote
  #13  
Old 10-03-2014, 01:47 AM
gmayor's Avatar
gmayor gmayor is offline copy content of a table inside email to excel sheet Windows 7 64bit copy content of a table inside email to excel sheet Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 3,829
gmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to beholdgmayor is a splendid one to behold
Default

Not having seen the whole of your code it is not possible to say.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #14  
Old 10-03-2014, 02:46 AM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows 7 32bit copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi Gmayor,

I have now excel 2003 under control, it will not fire up anymore :-)

The code is the same as provided the last time, the only thing that changes is the path to the file.
The thing is now with outlook 2003.

Greetings.
Reply With Quote
  #15  
Old 10-06-2014, 12:36 AM
megatronixs megatronixs is offline copy content of a table inside email to excel sheet Windows 7 32bit copy content of a table inside email to excel sheet Office 2003
Advanced Beginner
copy content of a table inside email to excel sheet
 
Join Date: Aug 2012
Posts: 42
megatronixs is on a distinguished road
Default

Hi Gmayor,

I get the anoying error and have no clue what to do with it:

Run-time error '429' activeX component can't create object.

I got this one after IT linked all the excel files to excel 2007.

At home with office 2013 all works great, but I need this at work and not home :-(

Code:
Public Function FileExists(ByVal Filename As String) As Boolean
Dim nAttr As Long
On Error GoTo NoFile
nAttr = GetAttr(Filename)
If (nAttr And vbDirectory) <> vbDirectory Then
FileExists = True
End If
NoFile:
End Function
 
Sub ExtractData()
Dim oItem As MailItem
If Application.ActiveExplorer.Selection.Count = 0 Then
MsgBox "No Items selected!", vbCritical, "Error"
Exit Sub
End If
For Each oItem In ActiveExplorer.Selection
CopyToExcel oItem
Next oItem
Set oItem = Nothing
End Sub
 
 
Sub CopyToExcel(olItem As MailItem)

Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim vText As Variant
Dim sText As String
Dim sAddr As String
Dim vAddr As Variant
Dim vItem As Variant
Dim i As Long, j As Long
Dim rCount As Long
Dim bXStarted As Boolean
Const strWorkSheetName As String = "ALL Data"
Const strWorkBookName As String = "C:\Test1.xls" 'the path of the workbook
'Use FileExists function to determine the availability of the workbook
If Not FileExists(strWorkBookName) Then Exit Sub
'Get Excel if it is running, or open it if not
'On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
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(strWorkBookName)
Set xlSheet = xlWB.Sheets("ALL Data")
 
'Process the message
With olItem
sText = olItem.Body
vText = Split(sText, Chr(13))
'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(xlUp).Row + 1
 
'Check each line of text in the message body
For i = UBound(vText) To 0 Step -1
If InStr(1, vText(i), "Time Submitted:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("A" & rCount) = Trim(vText(i + 6))
End If
 
If InStr(1, vText(i), "Your name") > 0 Then
xlSheet.Range("B" & rCount) = Trim(vText(i + 15))
End If
 
If InStr(1, vText(i), "Customer Email") > 0 Then
'vItem = Split(vText(i), Chr(58))
xlSheet.Range("C" & rCount) = Trim(vText(i + 2))
End If
 
If InStr(1, vText(i), "Customer Phone:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("D" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Move Date") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("E" & rCount) = Trim(vText(i + 2))
End If
 
If InStr(1, vText(i), "Origin City") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("F" & rCount) = Trim(vText(i + 2))
End If
 
If InStr(1, vText(i), "Origin State:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("G" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Origin Zip:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("H" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Destination City:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("I" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Destination State:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("J" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Destination Zip:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("K" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Vehicle Type:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("L" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Vehicle Year:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("M" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Vehicle Make:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("N" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Vehicle Model:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("O" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Vehicle Condition:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("P" & rCount) = Trim(vItem(1))
End If
 
If InStr(1, vText(i), "Comments:") > 0 Then
vItem = Split(vText(i), Chr(58))
xlSheet.Range("Q" & rCount) = Trim(vItem(1))
End If
Next i
xlWB.Save
End With
xlWB.Close SaveChanges:=True
If bXStarted Then
xlApp.Quit
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub
 
 
 
Sub TestLines()
Dim olItem As Outlook.MailItem
Dim vText() As String
Dim sText As String
Dim i As Long
For Each olItem In Application.ActiveExplorer.Selection
sText = Replace(olItem.Body, Chr(160), Chr(32))
vText = Split(sText, Chr(13))
For i = 0 To UBound(vText)
sText = "Line " & i & vbCr & vText(i)
If i < UBound(vText) - 1 Then
sText = sText & vbCr & "Line " & i + 1 & vbCr & vText(i + 1)
End If
If i < UBound(vText) - 2 Then
sText = sText & vbCr & "Line " & i + 2 & vbCr & vText(i + 2)
End If
If MsgBox(sText, vbOKCancel) = vbCancel Then Exit Sub
Next i
Next olItem
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy all data from a website to excel sheet? Plz plz plz plz plz help...urgent Sam123 Excel 0 07-19-2014 02:12 AM
copy content of a table inside email to excel sheet Copy table content between documents mcb09 Word VBA 14 06-12-2014 09:33 PM
Copy content control entries to next table next page Mel_Herndon Word VBA 2 05-22-2014 05:07 PM
copy content of a table inside email to excel sheet Find Results in excel copy the rows to another sheet khalidfazeli Excel 2 02-06-2013 09:38 AM
How to Copy data from Outlook mail and Paste it in a Excel sheet? padhu1989 Outlook 0 09-11-2012 04:07 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:31 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft