VBA for answering mail

[marc651]

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 : 6e until8e hour(
pulled out of the received mail)




Greatings, Marc

[gmayor]

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

[marc651]

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

[gmayor]

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.

[marc651]

This is how the mail comes in:

Form classroom application FEMDani 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).

[gmayor]

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.


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

[marc651]

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

[gmayor]

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.

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'

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"

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..

[marc651]

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

[marc651]

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

Greetings, Marc