email from exchange server into MySQL database 1

[DrDan1]

I need to have any emails sent to a particular email address to get placed into a table in a MySQL database. I know it's a simple matter to have this done using a web form, but this case requires email.

I've noticed something about this in the threads using the impa function but the emails are via an exchange server. Can the same process still be used, as I believe Exchange uses MAPI??

I'm using PHP5.2.8 on Apache 2.2 in a windows environment. Is anyone out there able to help or offer some guidance? I'm well and truly stuck.

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[ingresman]

Have a look at

http://msdn.microsoft.com/en-us/library/aa221877(office.11).aspx

which explains the object model for outlook.
To get you started try this:

set outlook = wscript.createobject("outlook.application")

set omapi = outlook.getnamespace("mapi")

set cal = omapi.getdefaultfolder(9)

quote = """"

set calItems = cal.items
wscript.echo "subject,start,end,attachments,size,location"
for each ent in calItems
    set oAtt = ent.attachments
    str = quote & ent.subject & quote  & "," & ent.start & "," & ent.end & ","  & oAtt.count & "," &   ent.size & "," & 

ent.location 
    wscript.echo str 
next

It's in vbscript for windows and just trawls through the defaukt calendar for all items. Chasnging the getdefaultdolder from 9 to 6 for the inbox (with changes it the fields of course)
Save it to a file called cal.vbs
and run it from a cmd prompt as cscript//nologo cal.vbs
There is a COM interface in PHP which you will have to convert to. I havn't tried but I might have a little look
good luck !

 

[lgarner]

If by "impa" you mean "IMAP", then yes, as long as the server is configured to allow IMAP connections. Same for POP3.

You might also be able to have Exchange auto-forward mail to a host which can capture it & process it using PHP or whatever language you like.

 

[DrDan1]

Typo. Yup, I meant imap. Looks like the server isn't configured for imap or pop3. Unfortunately I have no control over the server. Is there no way of doing this without the configuration? I'm hoping I've not bitten off more than I can chew as a relative novice.

Thanks ingresman. I'd rather avoid VBS if possible (I'm not too familiar with it) but it looks like it may come to that. :/

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[DrDan1]

I suppose I could have the mail forwarded to another email address accesable via imap or pop3, but that seems a bit messy and just gives more points of possible failure.

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[DrDan1]

More typo's. VBS = VBA :/

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[ingresman]

Yes it was just an example. PHP has a COM interface so you can access outlook from php code, no need to go near VBS.
If I get some time this afternoon I'll see if I csn get it to work.
Presume it's the sent items you need to enquire against ?

 

[ingresman]

try this

<?php
$sentItemsFolder = 5;
$realMessage = 43;

$outlook = new COM("outlook.application");

$mapi = $outlook->getnamespace("mapi");
$sentFolder = $mapi->getdefaultfolder($sentItemsFolder);
$items = $sentFolder->items;

foreach ($items as $ite)
{
  if ($ite->class == $realMessage)
    echo $ite->subject . " " . $ite->to . "\n";
}
?>

You might have to run the code under the same username as owns the mailbox. You can manipulate the login credentials (or profile as outlook calls it) by using the login method of the mapi object ($mapi in my code). The signature looks to be profile,password so to login to another user you would need something like:

$mapi = $outlook->getnamespace("mapi");
$mapi->logon("user","password");

Other point to note is the sent items has things like meeting accepts so you need to filter the mailitem type by looking at type 43.
Hope this helps, you'l need to sort out some database code but that should be easy enough.

 

[DrDan1]

Actually it's received emails that I'm trying to retrieve.

Thanks Won't be able to have a play with this till next week but I'm going to give it a shot! It certainly looks hopeful. Though what is the significance of or the values 5 and 43 for the first two variables?

From what you say it sounds like 43 is the value for real message type excluding things like meeting invites.

So does 5 then corrospond to the "sent items" folder? I presume then that the inbox folder will have a different value. I've tried google but not having much luck. How can I find out what value I need to use for inbox.

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[ingresman]

inbox is 6. the 43 is so you only get mails not any of the other stuff thst the sent items gets. If you use 6 and you get nothing back, remove the check and see what happens,
I agree docuentation for this is hard to find but you might find it on outlook itself by clioking tools->macro->viaual basic editor. When that fires up click help then microsoft visual basic hel and you shou;d see the help file, search for what you need, you might need to dig though

 

[jpadie]

ingresman -> does your code handle attachments ? i recall them being darned difficult to handle (and to distinguish between those annoying logo attachments and a file attachment.

 

[DrDan1]

You are a star. Thanks. Will let you know how it goes. As for attachments, well I'm only really interested in the text for now. Might consider that at a later date

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[ingresman]

Attachements are not too bad to handle. The mail item has a property which returns a collection (VB talk for an array) of objects which has all the attachments in. You can then loop through that collection and deal with the attachment as you like, e.g. get its size, extract it to a file, delete it, add a new one etc. If a get a quiet hour this afternoon I'll change the code to at least shoe some of the atachments handling capability.
Jpadie - what's a logo attachments.

 

[DrDan1]

I'm trying the above code but I keep getting timeouts (decided to just play with the sent items for now):
Fatal error: Maximum execution time of 30 seconds exceeded in C:\Server\Apache2.2\htdocs\tests\outlook.php on line 35

I've tried playing with the code a little but much of this is new to me. I'm not sure what the "->" does. It looks like it's a crossover method from PHP to VBA...?

I deleted all email in my sents except one as I had thousands. I even tried commenting out different parts to try and narrow down what the problem could be:

		$sentItemsFolder = 5;
		$realMessage = 43;

		$outlook = new COM("outlook.application");

		$mapi = $outlook->getnamespace("mapi");
		$mapi->logon("user","password");
		$sentFolder = $mapi->getdefaultfolder($sentItemsFolder);
		$items = $sentFolder->items;

		//foreach ($items as $ite)
		//{
		  //if ($ite->class == $realMessage)
		    echo $ite->subject . " " . $ite->to . "\n";
		//}

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[ingresman]

30 seconds might not be long enough if you have a load of messages in the sent items, but if you only have 1 it should be quick. Let's try some quick tracing.
Try running your script from the command to see what's going on (something like c:\php\php.exe yourscript.php. Doing this stops any web server issues confusing the matter.
As for the -> thats the PHP syntax to access something on a PHP object. It's nothing to do with VBA !
put this line in to see of you are connecting.

$items = $sentFolder->items;
echo "you have " . $items->count . " items";

I'm suprised you don't get an error as you are trying to use a non existant object on the echo of subject and to.

 

[ingresman]

Should have said, can you post your code ?

 

[DrDan1]

Here is my entire code:

  <?php
		$sentItemsFolder = 5;
		$realMessage = 43;

		$outlook = new COM("outlook.application");

		$mapi = $outlook->getnamespace("mapi");
		$mapi->logon("drobertson","2Dive49");
		$sentFolder = $mapi->getdefaultfolder($sentItemsFolder);
		$items = $sentFolder->items;

		foreach ($items as $ite)
		{
		  if ($ite->class == $realMessage)
		    echo $ite->subject . " " . $ite->to . "\n";
		  $items = $sentFolder->items;
			
		}
		echo "you have " . $items->count . " items";
  ?>

It times out when I run it from the browser, but when I do it from a command prompt like you suggested... IT WORKED!

It produced the following:

C:\Server\PHP>php.exe C:\Server\Apache2.2\htdocs\tests\outlook.php
<DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org[/URL]
/1999/xhtml1-transitional.dtd">
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
  <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
  <title> </title>
</head>


<body>
  test Robertson, Daniel
Ticket Request - PGS Ref: 748410 - Dead air on dialling number 'bt.irl.gbh@bt.co
m'
you have 2 items

But before it did, outlook asked me if I wanted to allow access to it from an external program. I had to select how many minutes to allow access for. I wonder if it's this security feature that is preventing me getting the data from apache.


----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[ingresman]

Take out the second $items = $sentFolder->items that might be causing you an issue.
It's an outlook security thing your up against when it asks you if you want to allow access to your address book. So I think when you are running behind apcahe it's just trying to get an answer from you but can never get one. You might have to have a chat with your exchange administrator to sort you out on this one. It's probabbly worth seeing it there is an oulook/exchange forum here you could perhaps ask. I don't know if the outlook web access software gets around tihs. Your at the edge of my knowledge here !
Running things from the command prompt often shows up things that the web server hides, it's a good debuging aid.

 

[DrDan1]

Thank you so much Ingresman. Even if I don't get this sorted, you've shown me a lot and given me much to think about. I'm currently reading about objects and classes.

I also found this link that may be of help:

http://www.prd-software.com.au/prd/support/kb/kbarticle.asp?id=115

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--So said the ever wise Bruce Lee
Memorize and live by it!

 

[ingresman]

Your welcome !