VBA for answering mail

Hello,

Is there a way to generate an email answer with data generated from the received mail.(with a button in the ribbon)
For example;

Dear Dani Jansen(name pulled out of the received mail),

Thank you for your classroomapplication.. We reserved for you:
Date : 02-01-2019(date

pulled out of the received mail)

Classroom :
Hours : 6^e until8^e hour(

pulled out of the received mail)

Greatings, Marc

Undoubtedly but not without seeing the 'received mail'. See https://www.gmayor.com/extract_data_from_email.htm or http://www.gmayor.com/extract_email_data_addin.htm

Dear Graham,

I don't mind seeing the received mail.
I looked at the code in the link you provided, but i am not able to translate this code t what i need.
My knowlidge of VBA is to limited for this.
So if anyone can help me with the code, that would be very nice.

Greetings, Marc

It is the forum members who need to see the message in order to determine how to assist ◔̯◔ The link essentially shows how to grab stuff from a message, albeit used there for a different purpose.

This is how the mail comes in:

Form classroom application FEM

Dani Jansen with e-mail .......... filled out the following:

Personal data:
Application date:	:	01-01-2019
Name:	:	Dani Jansen
E-mail:	:	...............
E-mail, check:	:	...............
I am:	:	student
Studentnumber:	:	586541
Institution	:	IBR

Reservation data:
Location for classroom:	:	Nijmegen
Date reservation classroom:	:	02-01 2019
Start time:	:	3e
End time:	:	7e
Classroom type:	:	Theorie
Max. persons:	:	3
Modulecode of activity:	:	jre1a-hc
Rostergroup:	:	HRN-M02
Remarks/Explanation:	:

I would like the name from 'Personal data', the date and start & end hours from 'Resrevation data'.
I am dutch by the way(in case some of the data seems weird).

Your e-mail text appears to contain two tables, so based on what you posted, the following macro will create the e-mail. You should be able to see how the macro works, should you require any further information from the tables.

The main macro can be run as a script as a script associated with a rule. Or select a suitable message asn run the first macro.

Code:

---

Sub TestCode()Dim olMsg As MailItem
    On Error Resume Next
    Set olMsg = ActiveExplorer.Selection.Item(1)
    ReplyToMail olMsg
lbl_Exit:
    Exit Sub
End Sub


Sub ReplyToMail(olItem As Object)
'Graham Mayor - https://www.gmayor.com - Last updated - 17 Sep 2019
Dim olOutMail As MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Dim oTable As Object
Dim oCell As Object
Dim strName As String, strDate As String
Dim strTime As String, sLocation As String
    If TypeName(olItem) = "MailItem" Then
        With olItem
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range
            Set oTable = oRng.Tables(1)
            Set oCell = oTable.Cell(3, 3).Range
            oCell.End = oCell.End - 1
            strName = oCell.Text


            Set oTable = oRng.Tables(2)
            Set oCell = oTable.Cell(3, 3).Range
            oCell.End = oCell.End - 1
            strDate = oCell.Text


            Set oCell = oTable.Cell(4, 3).Range
            oCell.End = oCell.End - 1
            strTime = oCell.Text & " until "


            Set oCell = oTable.Cell(5, 3).Range
            oCell.End = oCell.End - 1
            strTime = strTime & oCell.Text


            Set olOutMail = olItem.Reply
            With olOutMail
                Set olInsp = .GetInspector
                Set wdDoc = olInsp.WordEditor
                Set oRng = wdDoc.Range
                .Display
                oRng.collapse 1
                oRng.Text = "Dear " & strName & vbCr & vbCr & _
                            "Thank you for your classroom application. We reserved for you:" & vbCr & vbCr & _
                            "Date : " & strDate & vbCr & vbCr & _
                            "Classroom:" & vbCr & _
                            "Hours : " & strTime & vbCr & vbCr & _
                            "Greetings , Marc"
                '.Send 'restore after testing
            End With
        End With
    End If
lbl_Exit:
    Set olOutMail = Nothing
    Set olItem = Nothing
    Set olInsp = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

---

This is brilliant. I have 3 more questions;
I made a button in the ribbon to open the macro. Works like a charm. However;
- Is it posible to set it so that it doesn't open a new screen when activating the macro, but just pasts the answer above.
- Is it posible that the cursor sets itself after "Classroom:"
- When i want to put extra lines under "Greetings, Marc", how do i do that?
Thanks so far, it is VERY timesaving as it is.

Greetings, Marc

Quote:

---

Is it posible to set it so that it doesn't open a new screen when activating the macro, but just pasts the answer above.

---

I am not sure what you mean by this. The macro creates a reply and that has to be opened in order to write to the message.

Quote:

---

Is it posible that the cursor sets itself after "Classroom:"

---

Change the oRng.text = code to that below which will place the cursor after 'Classroom'

Code:

---

oRng.Text = "Dear " & strName & vbCr & vbCr & _                            "Thank you for your classroom application. We reserved for you:" & vbCr & vbCr & _
                            "Date : " & strDate & vbCr & vbCr & _
                            "Classroom: "
                oRng.collapse 0
                oRng.Select    'sets the cursor here
                oRng.InsertAfter vbCr & _
                                "Hours : " & strTime & vbCr & vbCr & _
                                "Greetings , Marc" & vbCr & vbCr & _
                                "this is the extra text after your name"

---

Quote:

---

When i want to put extra lines under "Greetings, Marc", how do i do that?

---

Just add to the text as shown above. vbCr is a paragraph break. Use it twice as shown to create space between paragraphs. Note that the reply will include the signature associated with the sending account..

Thanks,

The cursor code works.

However, i get fault messages when i want to ad a line after "Greetings, Marc".
It says; Expect name or instruction or instruction-end

Never mind my last question. I figured it out.
Thanks for all the help.
It works like a charm.

Greetings, Marc