Is this possible? Data extraction from an E-Mail

[brennaboy]

Hi,

I wondered if this is possible?

Everyday I get an E-Mail, which is effectively a report.

Currently I print the E-Mail which is quite large and go through it to see if there are any payments I have to make for today, e.g. any lines with today's date in the format 21-Sep-10.

I would like to write some sort of macro that can go through the E-Mail, find entries with today's date and extract them, either to another E-Mail or to Excel which ever is easiest.

Can this be done fairly easily?

Cheers,

BB

[Charlize]

A little start for your problem. One thing that could be a problem is the uppercase in the month's first letter. My system gives 23-sep-2010 in return for the current date. Just to let you know. This code will check every mail you have selected. No errorchecking when you're selection has something else then mailitems.

Sub check_active_mail()
Dim mymail As Outlook.MailItem
Dim currentmail As Long
Dim thedate As String
If Outlook.ActiveExplorer.Selection.Count < 1 Then
thedate = Format(Date, "dd-mmm-yyyy")
MsgBox "Select an email to check for " & thedate
Else
For currentmail = 1 To Outlook.ActiveExplorer.Selection.Count
Set mymail = Outlook.ActiveExplorer.Selection.Item(currentmail)
If InStr(1, mymail.Body, thedate) > 1 Then
MsgBox mymail.Subject & vbCrLf & "Contains " & thedate
End If
Next currentmail
End If
MsgBox "Selected mails are checked."
End SubCharlize

[brennaboy]

Hi,

Thank you for your reply.

Can you just talk me through the Macro.

I have run it and all it does is pop up with MsgBox "Selected mails are checked." at the end.

Cheers,

Bren.

[Charlize]

A variation of the original coding. First you create 3 new mails that you save in the drafts folder. So, new mail, fill in subject, body of message = 23-sep-2010 and click on cross and save message. Another one with the message 24-sep-2010 and last one with message 23-sep-2010.

You select all those mails in the drafts folder. When you're in the vba editor, go to this macro and hit F8 so you can see what this macro does.

I hope it's a bit clearer now.
Sub check_active_mail()
'the mailitem to check
Dim mymail As Outlook.MailItem
'mailitem in loop, count no of mails with date
Dim currentmail As Long, mailsfound As Long
'dateholder as string
Dim thedate As String
'format the date to dd-sep-2010
thedate = Format(Date, "dd-mmm-yyyy")
'put 0 in this one
mailsfound = 0
'count if there is a selection made of mails
'beware, I assume you select only mails, ie. no readreceipts etc ...
If Outlook.ActiveExplorer.Selection.Count < 1 Then
MsgBox "Select an email to check for " & thedate
Else
'loop through selected mails
For currentmail = 1 To Outlook.ActiveExplorer.Selection.Count
'put currectmail of the selection in mymail that we defined as a message item
Set mymail = Outlook.ActiveExplorer.Selection.Item(currentmail)
'compare the body of the message against the date. we put everything in caps
'to avoid the differences in sep,Sep,SEP
If InStr(1, UCase(mymail.Body), UCase(thedate)) > 1 Then
'show messagebox with subject and today date
MsgBox mymail.Subject & vbCrLf & "Contains " & thedate
'set counter of found mails
mailsfound = mailsfound + 1
End If
'process next one
Next currentmail
End If
'give result of this process
MsgBox "Selected mails (" & Outlook.ActiveExplorer.Selection.Count & _
") checked. Found : " & mailsfound
End SubCharlize

[shawnhet]

Since I can't see the actual report, I can't comment too specifically here - but I will suggest a pretty quick and dirty fix. Open the email, hit Ctrl A(Select All), Ctrl C(Copy), switch to an open Excel workbook and hit Ctrl V(Paste). Then do a Data >> Sort on the data and scroll down until you find the lines that begin with today's date and just print those. It is possible to use Excel to parse the data further if necessary, but without seeing the actual email in question I can't be of further help.

It is obviously possible to automate this process(or a similar one) using VBA, but I always like to know that I am at least in the ballpark of what you are looking for before I get more detailed.

Cheers, :)

[Charlize]

Since I can't see the actual report, I can't comment too specifically here - but I will suggest a pretty quick and dirty fix. Open the email, hit Ctrl A(Select All), Ctrl C(Copy), switch to an open Excel workbook and hit Ctrl V(Paste). Then do a Data >> Sort on the data and scroll down until you find the lines that begin with today's date and just print those. It is possible to use Excel to parse the data further if necessary, but without seeing the actual email in question I can't be of further help.

It is obviously possible to automate this process(or a similar one) using VBA, but I always like to know that I am at least in the ballpark of what you are looking for before I get more detailed.

Cheers, :)?

Change the line with If InStr(1, UCase(mymail.Body), UCase(thedate)) > 1 Then
by If InStr(1, UCase(mymail.Body), UCase(thedate)) > 0 ThenCharlize

[shawnhet]

Hi Charlize,

We both have different readings of what brennaboy is looking for. I think he is looking for a way to check a *particular* email for payments he must process without printing the whole email and manually scanning for it. You think he must check a bunch of emails to see if they contain the date in question. Obviously, the code you produce (if any) will depend on what you specifically are trying to do.

Cheers, :)

[Charlize]

Nope, I read the same as you. The code I wrote can be processed on a single mail to. Just thought that in the week-end he could get some mails to.

He wanted a way to check if the today date is included in the message. I gave the code to do that. Whatever he wants to do , extract, create task, ... can be done.

If the date isn't included, why bother to export ?


Obviously, the code you produce (if any)And what's the purpose of this sentence ?

Charlize

[shawnhet]

Ok, sorry, I guess I'm missing something in your code. To do what he asked for, wouldn't you need to output the lines with the date somewhere(so he would know the amount of the payment to enter)? I can't see where your code does this, but possibly I am just going blind.

Cheers, :)

[Charlize]

You did'nt even read the whole thread (look here http://www.vbaexpress.com/forum/showpost.php?p=225498&postcount=2). Otherwise you would have noticed I never stated that I've wrote the ultimate coding (but maybe I could (look at some of my postings if you question my ability to code something) if the poster would come back and give an example email) that would do what he wanted it to do.

Charlize

[shawnhet]

Ok, I'm sorry. I clearly didn't read some of your responses very well. It was not my intention to give offense, or question your competence(clearly you are much more competent than I am). However, from my POV, I was trying to answer brennaboy's issue, which was mainly about extracting data from a large email(I was not really focussing on what you wrote). What I suggested would work for brennaboy's problem, though it skips the step of checking the email first to see if the date is there first.

Cheers, :)

[Charlize]

No worries, I probably took the wrong leg out of my bed first. I just hate it when people don't answer (nearly 30 days after he asked some explanations).

:beerchug:

Charlize