Creating appointment from email

[bob_75]

Hi,

I own a restaurant which has an online reservation form. After a person fill out the form, an email is sent to me in a very specific format. Is there anyway I can automate the creation of calendar appointments from the dates that are included in the message body? this is the format:
-----------------------------------------------------------------------
From: {Name <PhoneNumber>}
Subject: [Online-Reservation]

Name: {Name <PhoneNumber>}
Number of people: {NumberOfPeople}

Date: {11/05/10}
Time: {6:30 PM}

--
This mail is sent via the reservation form on {WebsiteName}.
------------------------------------------------------------------------

Is there anyway to create an appointment from the Date and Time field and add the From to the appointment body?

Thanks a lot

Bob

[Sebastian H]

That no one has replied to your post so far may have to do with the fact that you're not just asking about a particular bit where you're stuck, but you need a whole solution, programmed from scratch. Have you thought of paying someone to do that? There are several people here (not me) who make an honest living doing this kind of work.

[bob_75]

oh, my bad. Don't need the whole entire solution. I just need to know if there is a way to read strings and compare them. I am not very familiar with vb although i could learn. the api is pretty crappy (not like java). I hacked up a code to create appointments but just don't know how to see what date and time i need to make that appointment for.

Thanks again for replying

[Charlize]

You can read the body of an e-mail and look for specific text in it. I would save the body to a txt file and read each line in. If you provide a sample message I could take a look at it (but don't promise a thing). I assume the subject is always the same. And how many reservations can be made on the same day and/or hour ? Or do you need to give a confirmation e-mail ...

Charlize

[bob_75]

the format on my first post is exactly what is sent. the things inside the curly braces is what is different as how the customer fills out the form.

if I understand correctly, there is no string object/class that can be filled with the entire email and then scanned? i have to create a text file first and then create the appointment from that?

danke
(isn't that a german flag?)
bob

[Charlize]

You can put the whole body in a variable. But you need to do a lot of coding to find the stuff you need. When using a textfile, you could just loop through each line of the textfile and see if it contains a keyword. If so, split by using the keyword and choose the correct element of the array you just created. Anyway, here a starter for your problem. Select a mail with a reservation. Run this code and you'll get a messagebox with the desired info. If that's correct, you could use the createitem stuff for an appointmentitem by using the variables.
[vba]Sub Read_Info()
'the mailmessage holder
Dim mymessage As Outlook.MailItem
'the text of the message
Dim thebody As String
'thename = name of person, nopeople is number of people
'mydate as string is the date in message, thedate as date = converted to real date
Dim thename As String, nopeople As String, mydate As String, thedate As Date
'thehour is hour and minute is minute
Dim thehour As String, theminute As String
'we are going to process the selected mailitem
'make sure it's a mailitem and nothing else (ie. read receipt or something else)
Set mymessage = ActiveExplorer.Selection.Item(1)
'store the messagebody to the variable
thebody = mymessage.Body
'display it
MsgBox thebody
'locate the start of the name in the message body and add 7 because
'we don't need the search string included
'then locate the end }, starting from the searchstring name and we are going to
'substract 7 + the number where we found } to determine the name
thename = Mid(thebody, InStr(1, thebody, "Name: {") + 7, _
InStr(InStr(1, thebody, "Name: {"), thebody, "}") - _
InStr(1, thebody, "Name: {") - 7)
'here it's 19 because the length of the searchstring is 19
nopeople = Mid(thebody, InStr(1, thebody, "Number of people: {") + 19, _
InStr(InStr(1, thebody, "Number of people: {"), thebody, "}") - _
InStr(1, thebody, "Number of people: {") - 19)
'Because you use mm/dd/yyyy, we need to separate the string later to a real date
'here it's 7 because searchstring is 7
mydate = Mid(thebody, InStr(1, thebody, "Date: {") + 7, _
InStr(InStr(1, thebody, "Date: {"), thebody, "}") - _
InStr(1, thebody, "Date: {") - 7)
'Here we separate the stringdate to a realdate using the split function
'split function only works on strings
thedate = DateSerial(Split(mydate, "/")(2), _
Split(mydate, "/")(0), _
Split(mydate, "/")(1))
'Display the result in a messagebox
MsgBox thename & " - no people : " & nopeople & _
vbCrLf & "Date : " & thedate
End Sub[/vba]Charlize

ps.: it's certain the Belgium flag. Germany is from top to bottom with the colors not from left to right.

[JP2112]

In addition to what Charlize suggested, you can also parse the email like this:

[VBA]Dim messageLines() As String
messageLines = Split(MailItem.Body, vbCrLf)[/VBA]

This would tokenize each line of the email into a separate array element, which you could then parse (using Instr, Mid, and so on) for the information you need to create the appointment.

[bob_75]

You guys are awesome, thank you soo much for pointing me in the right direction. Much much appreciated.

[jverbakel]

Hello,

can you make the code to put a selected Mail message in to a calendar appointment. In the the bode the message in both plain text and inserted a msg item.

I Hope so.