Consulting

Results 1 to 10 of 10

Thread: VBA for answering mail

  1. #1

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




    Greatings, Marc

  2. #2
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

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

  4. #4
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    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).

  6. #6
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

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

  8. #8
    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..
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

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

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

    Greetings, Marc

Posting Permissions

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