Page 1 of 5 123 ... LastLast
Results 1 to 15 of 61
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Outlook Automation (2002)

    Just thought I'd see what I can stir up before going to bed <img src=/S/devil.gif border=0 alt=devil width=15 height=15>
    I have a cmdbtn with code that sends an e-mail with attachment to my carriers (truckers); (Thanks, and a tip of the hat to HansV). The carriers are then required to reply (not send a new e-mail) to confirm that they have received the order. The replies have their own folder in Outlook. The subject line has the Pro# for the order they were given, so it has always been a simple matter to find who has or has not replied. This worked just fine in my little one-horse carriage, but I'm growing a bit and it's harder and harder to track who has or has not replied (today a load was missed because the carrier claimed he never got the order). I have looked around the Lounge and MSKB and don't really see anything that guides me toward what I want to attempt (there's plenty on importing contacts, etc). I certainly wouldn't mind reading up on this if someone has some MSKB articles or posts that are even closely related (if they exist, I can't find them)

    What I would like to do is have Access check Outlook for replies, and then (somehow) show in the record associated with that reply (the Pro#) that the order has been confirmed. Assuming the worst, I suppose I would also have to handle multiple replies for the same Pro# (carrier responds twice, responds but later declines and another carrier replies, etc.) Is this just a pipe dream, or is something like this even possible?
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (2002)

    Processing the e-mails is quite possible, and we can help you with that, but you must work out for yourself what you want to do with multiple replies. Do you want to keep a history, or just store the latest reply?

    To keep a history, you would need to create a separate table, with the Pro# to link it to the main table, plus a code for the reply, and probably some other information, such as sender, date/time received etc. Otherwise, you could use one or more fields in the main table to store the latest reply; these fields would be overwritten each time a new reply for the same Pro# comes in.

    By the way, how can you determine (automatically) from the reply whether it is a confirmation or a refusal or whatever? Is this always obvious from the subject?

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (2002)

    Hans:

    Perhaps the best way to approach this (seeing as I don't even know what I really want at this point) is to describe the process. Because I'm only dealing with the carriers (truckers) at this point, we'll just assume I already have orders for shipments and will not address the customer side of the process.

    I send a broadcast e-mail to select carriers each morning with a list of available loads; the carriers (even before receiving my e-mail) may or may not call with a list of available trucks for that day. The number of loads and number of trucks changes dramatically throughout the day. When a carrier accepts a load (always by phone, never by e-mail) then I send a Load Confirm via e-mail. This msg has a subject line as displayed above, an attachment (report) containing all the load information, and the body msg of "Please REPLY to this msg, do NOT send a new e-mail" (all of which is done by your generous donation of some rather elegant code). These are not sent unless or until the carrier has agreed to do the load. The carrier then replies to the e-mail with "load confirmed" in the body of the message (although just replying is confirmation enough for me; no body text is required); the reasoning behind this is that any refusal of an order must be made by phone. By this I mean to say that the Load Confirm would never have been sent in the first place unless the carrier had already agreed to do it (by phone), but occasionally a truck wil break-down or something else that requires a carrier to back out of their commitment. The reply to the Load Confirm assures me that the carrier has received all the necessary info and, unless I receive a phone call telling me otherwise, it's a done deal. At the moment (due to increased volume) I have no way to know that a carrier has received the info, or if I should be calling someone to find out what the problem is (or even if there is a problem).

    I would like to track all this on the Load Board (continuous form) by conditionally changing the back color of the txtCarrierName field. As to the history question, I'm not certain I understand all the consequences of having, or not having, that recorded. I "presume" a history is not really necessary. The conditional formatting of the txtCarrierName field would probably have to be based on the Outlook info as well as the "status" of the load (ie. if order status is "dispatched" do formatting). All status' prior to, or following, that staus really have little to do with whether or not the order was confirmed by the carrier (ie. "new", "assigned", "in route", "billed", etc.). Hmm, I think I see a problem arising. If a carriers truck blows up and he can no longer do the load, the order status would be changed to "open" until a new carrier was "dispatched", but how would Access be able to determine the difference between the confirmation by the new carrier and the old confirmation (in the Outlook folder)? The plot thickens!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (2002)

    You could look at the received date/time of the e-mails and always use the most recent one, so old e-mails remaining in the Outlook folder would be ignored.

    What information would you like to be updated from the e-mails? I.e. what is the name of the table to be updated, and wich fields should be changed (field name, data type)?

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (2002)

    Hans:

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>. . .why do I always find myself apologizing for being a dolt?

    I'm not sure I understand what you're asking for, but here's my "guess".
    tblMaster
    ProNo (lngInt, req'd, no dups)
    txtCarrierName (text)
    txtOrderStatus (text)

    The Outlook folder is "Load Confirmations" if you need that. Am I on the right track?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (2002)

    OK, ProNo can be retrieved from the e-mail subject.
    Where do we get the Carrier Name from? We know the e-mail address of the sender.
    What should the Order Status be set to? The text "Confirmed" or a code or ...

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (2002)

    Hans:

    Not sure I follow (do we really need the carrier name? The ProNo already tells me who it's from (the carrier I sent it to))
    I'm not sure we want to automatically change the order status, but I could create a new txtbx in the table to use to record the confirm and use that to conditionally format the txtCarrierName in the continuous form (ie. [txtCarrierConfirm]="Confirmed"). Am I off base here?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (2002)

    You wrote that you wanted to update txtCarrierName and txtOrderStatus, so that is why I asked. But OK, so we won't update them. If the new Carrier Confirm field can only be blank or 'confirm', it would be better to make it a Yes/No field. Or do you foresee other values for this field?

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (2002)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>. . .you should know better than to think I know what I'm thinking/doing!
    As long as I can address Yes/No in the conditional formatting statement, I think that's best. In fact, why not just have a checkbox display the confirmation in the continuous form? I do not forsee other values.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (2002)

    Add the following fields to tblMaster:

    blnCarrierConfirm (type Yes/No)
    datReceived (type Date/Time)

    I have assumed that the Load Confirmations folder in Outlook is under Outlook Today. If not, post back.

    The ProcessMails procedure in the attached text file will loop through all mails in the Load Confirmations folder and set the blnCarrierConfirm field to Yes and the datReceived field to the received date/time of the e-mail if the datReceived field is blank or earlier than the received date.

    You may want to add code to move the mails to another folder once they have been processed.
    Attached Files Attached Files

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (2002)

    Hans:

    I must be doing something wrong. I presume the code just goes anywhere in the current project, and that by Outlook Today you mean Personal Folders (because I don't have a folder named Outlook Today in the folder list). Load Confirmations folder is in Personal Folders (along with InBox and everything else. Whatever it is, nothing's happening.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (2002)

    Outlook Today is usually the "root" of your Personal Folders, it's not a folder itself. But if Load Confirmations is at the same level as Inbox, you don't need to worry about it.

    The code I posted should go into a standard module (the kind you create in the "Modules" section of the database window by clicking New, or in the Visual Basic Editor by selecting Insert | Module.)

    Try the following (in a copy of your database, to be on the safe side):
    - Click in the line Sub ProcessMails().
    - Press F8 to start step-by-step execution; each F8 will progress one line.
    - See if the code loops through the mail items. You can view the value of variables by hovering the mouse over them.

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (2002)

    Hans:

    I'm not sure what I'm supposed to be looking for, but I don't recognize anything that looks like an item in Outlook. Every occurance of objOLNameSpace reveals the info shown in attachment. Like I said, I don't know exactly what to look for. Not that I have a clue, but tblMaster is a linked table (b.e.) - does that matter?
    Edit: Should "PRO#" be ProNo, or is that your own definition?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (2002)

    In the upper part of your screenshot, execution has paused at the line Set objOLNameSpace = ... At that point, the folder and the items have not been set yest, so it is to be expected that you see "Object variable ... not set". You will have to press F8 several more times to see what happens.

    According to the information you have provided, the subject of the e-mails contains PRO#, while the name of the field in tblMaster is ProNo. We're trying to read the e-mails here, so we search for "PRO#" in the subject. If the information was incorrect, you will have to change the code accordingly.

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (2002)

    Ah, I see (said the blind man). I'll keep looking. BTW, how does Access know to look for a new e-mail, or does Outlook tell access there is something new?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 5 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •